Re: Perl/DBI vs Native

Valentin Bogdanov wrote:
> 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.

The problem may be that your two tests are not equivalent.  When Perl executes a statement, it copies the *entire*
resultset back to the client before it returns the first row.  The following program might appear to just be fetching
thefirst row: 

  $sth = $dbh->prepare("select item from mytable");
  $sth->execute();
  $item = $sth->fetchrow_array();

But in fact, before Perl returns from the $sth->execute() statement, it has already run the query and copied all of the
rowsinto a hidden, client-side cache.  Each $sth->fetchrow_array() merely copies the data from the hidden cache into
yourlocal variable. 

By contrast, psql executes the query, and starts returning the data a page at a time.  So it may appear to be much
faster.

This also means that Perl has trouble with very large tables.  If the "mytable" in the above example is very large, say
ahundred billion rows, you simply can't execute this statement in Perl.  It will try to copy 100 billion rows into
memorybefore returning the first answer. 

The reason for Perl's behind-the-scenes caching is because it allows multiple connections to a single database, and
multiplestatements on each database handle.  By executing each statement completely, it gives the appearance that
multipleconcurrent queries are supported.  The downside is that it can be a huge memory hog. 

Craig