Re: same query, two different approaches, vastly different performance

On Fri, 17 Feb 2012 11:18:39 -0600
Puneet Kishor <[email protected]> wrote:

> >> query 2 is at least an order of magnitude slower than query 1
> 
> Now that I know the reason behind this, thanks to all of you, I have
> decided to stick with inline params `LIKE $q` instead of bind values.
> I am not too worried about SQL attacks, and the above strategy works
> well without having to toggle `pg_server_prepare`. Also, I do expect
> the query to run often, but as the frontend is a web app, it will be
> called in separate sessions... so, think many reloads of the same
> page as opposed to one page request firing many instances of the same
> query.

Unless you're cleaning up the input in some other way, I would suggest
you use $dbh->quote() on $q before interpolating it into your query,
like so:

my $quoted = $dbh->quote( $q );
$dbh->do( "... LIKE $quoted" );

It's a Good Idea--regardless of whether you're "worried." :-)


-- 

C. Chad Wallace, B.Sc.
The Lodging Company
http://www.lodgingcompany.com/
OpenPGP Public Key ID: 0x262208A0