Skip site navigation (1) Skip section navigation (2)

Re: Any better plan for this query?..

From: Merlin Moncure <mmoncure(at)gmail(dot)com>
To: Dimitri <dimitrik(dot)fr(at)gmail(dot)com>
Cc: Dave Dutcher <dave(at)tridecap(dot)com>, PostgreSQL Performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Any better plan for this query?..
Date: 2009-05-19 21:48:49
Message-ID: b42b73150905191448y2fd4235cp6aa7460a041d1bc7@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Tue, May 19, 2009 at 3:15 PM, Dimitri <dimitrik(dot)fr(at)gmail(dot)com> wrote:
> On 5/19/09, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
>> On Tue, May 19, 2009 at 11:53 AM, Dimitri <dimitrik(dot)fr(at)gmail(dot)com> wrote:
>>> the query is *once* prepared via PQexec,
>>> then it's looping with "execute" via PQexec.
>>> Why PQexecPrepared will be better in my case?..
>>
>> It can be better or worse (usually better).  the parameters are
>> separated from the query string.  Regardless of performance, the
>> parametrized interfaces are superior for any queries taking arguments
>> and should be used when possible.
>
> you're probably right, but I don't like either when solution become so
> complicated - PG has a so elegant way to execute a prepared query!

It's not so bad.

PQexec:
sprintf(buf, query, char_arg1, my_arg2);
PQexec(conn, query);
sprintf(buf, query, char_arg1, my_arg2);
PQexec(conn, query);

PQexecParams:
char *vals[2];
int formats[2] ={0,0};
vals = {char_arg1, char_arg2};
PQexecPrepared(conn, stmt, 2, vals, NULL, formats, 0);
vals = {char_arg1, char_arg2};
PQexecPrepared(conn, stmt, 2, vals, NULL, formats, 0);

The setup is a little rough, and 'non strings' can be a pain vs.
printf, but the queries are safer (goodbye sql injection) and usually
faster.  Also the door is opened to binary formats which can be huge
performance win on some data types...especially bytea, date/time, and
geo.  There are some good quality libraries out there to help dealing
with execparams family of functions :D.

merlin

In response to

pgsql-performance by date

Next:From: Tom LaneDate: 2009-05-19 22:49:45
Subject: Re: Any better plan for this query?..
Previous:From: DimitriDate: 2009-05-19 19:15:13
Subject: Re: Any better plan for this query?..

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group