Front page | perl.dbi.users |
Postings from February 2012
I asked this on Stackoverflow and on Perlmonks, but hopefully I will get a more satisfactory and revealing insight straight from the DBI folks.
I have a Postgres table with more than 8 million rows. Given the following two ways of doing the same query, I get wildly different results.
$q .= '%';
## query 1
my $sql = qq{
SELECT a, b, c
FROM t
WHERE Lower( a ) LIKE '$q'
};
my $sth1 = $dbh->prepare($sql);
$sth1->execute();
## query 2
my $sth2 = $dbh->prepare(qq{
SELECT a, b, c
FROM t
WHERE Lower( a ) LIKE ?
});
$sth2->execute($q);
query 2 is at least an order of magnitude slower than query 1... seems like it is not using the indexes, while query 1 is using the index. And, yes, I have tried it with query 2 before query 1 to eliminate caching. There is a difference, but I don't know why, so would love hear an explanation.
Many tia,
--
Puneet Kishor
Thread Next