Re: dbi question

[FitTrend]

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.

[Tanktalus]

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.

[FitTrend]

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.

[shemp]

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
}
...