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

On Fri, Feb 17, 2012 at 9:18 AM, Puneet Kishor <[email protected]> wrote:

>
> On Feb 17, 2012, at 11:05 AM, Greg Sabino Mullane wrote:
>
> >
> > -----BEGIN PGP SIGNED MESSAGE-----
> > Hash: RIPEMD160
> >
> >
> >> query 2 is at least an order of magnitude slower than query 1
> >
> > To follow up on this, the current best practice for handling this
> > is to prepare two statements, and have your app use the correct one.
> > One could be prepared with pg_server_prepare=0, and one as normal.
>
>
> 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.
>

You can be reasonably safe using inline params like that if you're careful
to make sure $q contains only the sort of characters that make sense for
your app, and/or if $q is quoted properly. You can use a regex to test that
it is only alphanumeric for example. It's just easier to guarantee safety
if you use bind values... but that's not always practical as you have
discovered.

-- 
Check out my LEGO blog at http://www.brickpile.com
Follow/friend me: facebook.com/billward • flickr.com/photos/billward •
twitter.com/williamward