On Jul 21, 2008, at 5:19 AM, Valentin Bogdanov wrote:
> Hi,
>
> I have ran quite a few tests comparing how long a query takes to
> execute from Perl/DBI as compared to psql/pqlib. No matter how many
> times I run the test the results were always the same.
>
> I run a SELECT all on a fairly big table and enabled the
> log_min_duration_statement option. With psql postgres consistently
> logs half a second while the exact same query executed with Perl/DBI
> takes again consistently 2 seconds.
>
> If I were timing the applications I would have been too much
> surprised by these results, obviously, processing with Perl would be
> slower than a native application. But it's the postmaster that gives
> these results. Could it be because the DBI module is slower at
> assimilating the data?
Hi Val,
Yes, DBI can be slower then the native C interface. The speed depends
on how the data is being returned inside of Perl. Returning hashes is
a slower method then returning arrays from what I've found due to the
overhead in the creation of the objects in Perl.
So:
my $r = $dbh->selectall_arrayref("select * from table", { Columns =>
{}});
Is slower then:
my $r = $dbh->selectall_arrayref("select * from table", undef);
Secondarily, if you're returning a lot of rows you may want to look
into using a cursor, so that you can fetch the rows a 1000 at a time
in a tight loop then discard them once you are done with them. This
will hopefully prevent the system from having continually allocate
memory for all of your rows. For each field in each row Perl
allocates memory to store the value from Postgres, so if you have many
fields on your table this can be a large number of allocations
depending on the number of rows. Any userland profile tool should
help you debug what's going on here.
Cheers,
Rusty
--
Rusty Conover
InfoGears Inc.
http://www.infogears.com - http://www.gearbuyer.com - http://www.footwearbuyer.com
