Re: Performance issue

You generate a big number of open statement handles, each costing some memory. Put the SQL statements (*NOT* the statement handles) into a hash, like this:

use DBI;

my %statements=(
'find_users' => 'select foo,bar from users where baz=?',
'find_documents' => 'select * from documents where id=? and type=?',
'increment_counter' => 'update counter set n=n+',
);

## some lines later

my $sth=$dbh->prepare($statements{'find_users'});
$sth->execute(42);
while (my $record=$sth->fetchrow_arrayref()) {
# ...
}
$sth->finish();

# somewhere else

$dbh->do($statements{'increment_counter'});


Personally, I don't like this style very much, as I would always have to lookup somewhere what actually happens.

See also http://www.perl.com/pub/a/2002/10/22/phrasebook.html


Alexander


Richard J McWaters wrote:

I was trying to improve the performance of a Perl DBI query. In the process I was also trying to improve the look and maintenance of my code. I am doing a batch job where I am doing multiple queries based on an input file. The old code had the prepare statement inside the "read file" loop. A sample would look like:

use DBI qw(:sql_types);


while ($name = <INFILE>>)
{
$sth = $dbh->prepare("
SELECT TAB_NAME FROM TABLE1 WHERE TAB_IN = ?
" );

$sth->bind_param( 1, $name);

$sth->execute();

while ( @out1 = $sth->fetchrow_array )
{
print "$out1[1]\n";
}

}

I can't send a sample of the actual code, but this is a "summary" of what I am trying to do.

I thought it would be nice to collect all the prepare statments together and put them in a "documented block" at the start of the program just after the use DBI line. So the above code would now look like:

use DBI qw(:sql_types);

$sth = $dbh->prepare("
SELECT TAB_NAME FROM TABLE1 WHERE TAB_IN = ?
" );

$sth->bind_param( 1, $name);


while ($name = <INFILE>>)
{
$sth->execute();

while ( @out1 = $sth->fetchrow_array )
{
print "$out1[1]\n";
}

}


Not only would this allow me to have an easier to maintain group of prepare statements, I also would be doing the prepare only once in the program instead of for each line of input file. To my surprise, this slowed down my program instead of speeding it up. Does anyone know what is going on?



--
Alexander Foken
mailto:alexander@xxxxxxxx http://www.foken.de/alexander/

.

Relevant Pages

  • Performance issue
    ... I was trying to improve the performance of a Perl DBI query. ... process I was also trying to improve the look and maintenance of my code. ... SELECT TAB_NAME FROM TABLE1 WHERE TAB_IN =? ... program instead of for each line of input file. ...
    (perl.dbi.users)
  • Re: how to set a DEFAULT value !!
    ... Hash: SHA1 ... in plain old DBI your only real option is to create a separate statement ... I've proposed adding something simlilar to DBI itself, ...
    (perl.dbi.users)
  • Re: adding key to DB object
    ... It may look like a hash, but when you set or query ... T>> j> the DBD and by DBI, ...
    (perl.dbi.users)
  • Re: how do i tie a reference to a hash
    ... > I'm trying to tie a hash that is returned from a DBI ... One is to pass the reference as a parameter to TIEHASH, ...
    (comp.lang.perl)
  • inserting new date in datatbase when different from system directory
    ... use DBI; ... my $lookup_access=$dbh1->prepare(qq{select id,name, update FROM table1 ... chdir $Top; ... Do you Yahoo!? ...
    (perl.dbi.users)