Re: Any better plan for this query?..

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