Beefy Boxes and Bandwidth Generously Provided by pair Networks
Syntactic Confectionery Delight
 
PerlMonks  

Re: dbi question

by FitTrend (Pilgrim)
on Mar 04, 2005 at 20:02 UTC ( #436726=note: print w/ replies, xml ) Need Help??


in reply to dbi question

The only comment I would add on this is that what you will do may slow your program down. I mean if you execute a query inside a loop that has say 100,000 records, you'll be executing a query 100,000 time inside that loop.

If the second query will be referencing the same index in a table, I would recommend creating a hash for the entire second dbi execute prior to executing the first query and performing the loop. This way, you're simply reference the hash for the value instead of performing all those queries. This may not work for what you need, but I thought it would be worth noting.

example:

## ASSUMED CONNECTION ESTABLISHED ## # CREATE HASH FOR REFERENCE $sth = $dbh->prepare("select report_id, name from reportindex"); $sth->execute(); while ($ref = $sth->fetchrow_hashref()) { $reportIndex{"R$ref->{'report_id'}"} = $ref->{'name'}; } $sth->finish() unless (! $sth); $sth = $dbh->prepare("select reportindex_id from favorite_reports"); $sth->execute(); while ($ref = $sth->fetchrow_hashref()) { # REFERENCE HASH INSTEAD OF CALLING SECOND QUERY print "I like the report: ". $reportIndex{"R$ref->{'reportindex_id +'}"}; } $sth->finish() unless (! $sth);

Hope this helps.


Comment on Re: dbi question
Download Code
Re^2: dbi question
by Tanktalus (Canon) on Mar 04, 2005 at 21:10 UTC

    Actually, and I'm no DBA, the OP looks more to me like a situation to use a JOIN of some sort. Your example may be a bit more simple than the general case, though.

    SELECT ri.name FROM reportindex AS ri, favorite_reports AS fr WHERE +ri.report_id = fr.reportindex_id
    Or something like that.

      This is true. I was being general. But you do bring up a good point that you should use SQL statements when possible to reduce the amount of perl code to manage.

Re^2: dbi question
by shemp (Deacon) on Mar 05, 2005 at 00:13 UTC
    Depending on what percentage of the rows in the table in the second query that you expect to hit, you could cache the results of the inner query, something like this:
    my $inner_query = $dbh->prepare([inner query]); # $inner_query will have a bind param my $outer_query = $dbh->prepare([outer_query]); my %inner_cache; while ( my $data_ref = $outer_query->fetchrow_hashref() ) { if ( ! exists $inner_cache{$data_ref->{some_field}} ) { $inner_query->execute($data_ref->{some_field}}; $inner_cache{$data_ref->{some_field}} = $inner_query->fetchrow_h +ashref(); $inner_query->finish(); } my $inner_result = $inner_cache{$data_ref->{some_field}}; # do whatever } ...

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://436726]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others pondering the Monastery: (7)
As of 2014-11-27 06:47 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My preferred Perl binaries come from:














    Results (180 votes), past polls