DBI execute is slow?

mlorenz has asked for the wisdom of the Perl Monks concerning the following question:

Hello fellow penitents,

I've been having a problem with a particular Postgres SQL query running slowly when executed from my Perl script. Here it is in its Perl 5 context:

        my $sql = "SELECT o.objectid, 
                       <other fields>
            FROM       object o
            WHERE       o.accountid = " . $accountid . 
            ORDER BY o.objectname
            LIMIT 20 OFFSET 0";

        my $object_info_ref = undef;
        eval {
            my $sth=$dbh->prepare($sql);
            $sth->execute();
            $object_info_ref = $sth->fetchall_arrayref();
            $sth->finish();
        };
[download]

That's the gist of the query, and I've used logging statements to track the slowness to the $sth->execute() command:
- When the given account ID is for an account with a lot of data, $sth->execute() takes around 12 seconds to come back.
- For an account with little data, it's very fast (less than a second).

That initially seems to make sense, if you assume the query is running differently because of the data. However, the strange thing is that the exact same query, executed from pgAdmin or psql (windowed and command-line tools for Postgres) takes about 2ms to run, regardless of which account is chosen.

So my question is, is there some kind of weird interaction between DBI and Postgres to account for this slowness? Seems to me like it can't be the database, because the database tools by themselves return the results immediately.

I've tried this with the database handle ($dbh) using autocommit on and autocommit off, and seen no difference.

Thanks!

Comment on DBI execute is slow? Download Code

Replies are listed 'Best First'.
Re: DBI execute is slow? by peter (Sexton) on Feb 27, 2008 at 20:41 UTC
First: even if your accountid doesn't change it is better to use prepared statements. `my $sql = "SELECT o.objectid, <other fields> FROM object o WHERE o.accountid = ? ORDER BY o.objectname LIMIT 20 OFFSET 0"; .... my $sth=$dbh->prepare($sql); $sth->execute($accountid);` [download] This will give you (maybe) a small speed increase in this example. Second: `fetchall_arrayref` returns all data in one structure. This involves a lot of copying. Probably you should take a look at `fetchrow_arrayref`. This will get one row at a time (so you can process one row at a time). Peter Stuifzand	[reply] [d/l] [select]
Re: DBI execute is slow? by erix (Prior) on Feb 27, 2008 at 20:50 UTC
What driver are you using? DBD::Pg is the one to use. Do you have an index on `object.accountid` ? Have you run vacuum / autovacuum ? Have you run `analyze object`? (do this after the create index) How much is a "lot of data"? versions, please?	[reply] [d/l] [select]
Re: DBI execute is slow? by nedals (Deacon) on Feb 28, 2008 at 04:16 UTC
Index both the accountid and objectname columns, if not already indexed. What is that 'eval' doing there? That has to be detriment to performance.	[reply]
Re^2: DBI execute is slow? by runrig (Abbot) on Feb 28, 2008 at 07:15 UTC
What is that 'eval' doing there? That has to be detriment to performance. No, the {BLOCK} form of eval does not contribute to any sort of detriment to performance here. Certainly not the 12 seconds that the OP is claiming (update: even eval "STRING" wouldn't explain that kind of perfomance hit). That said, I wonder what the problem is. The OP says the delay is at execute time...I wonder if under the hood Postgres fetches the data at execute time. But that doesn't explain why the query allegedly completes quickly from the command line tool. I might also think that maybe it just doesn't finish quickly, and in the command line it just appears to start returning rows more quickly, but in the example, only 20 rows are being fetched, so it ought to be obvious whether it finishes quickly or not. I might also wonder if Postgres supports permissions on indexes, and that maybe they're not the same between how the OP is executing in the command line vs the perl program. Anyway, it's all just random speculation on my part, as I don't have the OP's system in front of me, so maybe someone else can take a better guess from the information provided, or maybe the OP can provide more information.	[reply]