Re: libpq and prepared statements progress for 8.0

> -----Original Message-----
> From: pgsql-hackers-owner(at)postgresql(dot)org
> [mailto:pgsql-hackers-owner(at)postgresql(dot)org] On Behalf Of David Wheeler
> Sent: Tuesday, October 05, 2004 10:32 AM
> To: Abhijit Menon-Sen
> Cc: pgsql-hackers(at)postgresql(dot)org
> Subject: Re: [HACKERS] libpq and prepared statements progress for 8.0
>
>
> On Oct 5, 2004, at 9:59 AM, Abhijit Menon-Sen wrote:
>
> > I decided against bundling the two operations together.
> Here's a patch
> > to add PQprepare() and PQsendPrepare() in a fairly
> self-contained way.
> > Also attached is a test program à la testlibpq3.c that I
> used to test
> > the change. This should be all that's needed for DBD::Pg to
> prepare a
> > statement without pre-specifying types.
>
> Ah, fantastic news, Abhijit! This is very exciting for DBD::Pg
> development, as well as other dynamic language libraries that plan to
> use prepare(), I expect. I very much look forward to Beta 4
> hoping that
> this patch makes it in.

It offers to all programmers who take advantage of it a valuable performance increase.
Especially in the case of programs that will reuse a statement many times with different parameter markers, it should be very valuable.
Most data entry jobs are like that.

You can make reuse of statements fairly transparent in some special cases.

Create a 64 bit hash (e.g. UMAC) of the prepared statement (removing hardwired parameters as needed so that "SELECT Col1, col2 FROM Some_Table where FOO = 'BAR'" becomes "SELECT COL1, COL2 FROM SOME_TABLE WHERE FOO = ?", form consistent capitalization of the statement by capitalizing all keywords and non-quoted column names and then form a hash. Create a hash table of skiplists that contain the prepared statement and the prepared statement handle (the hash modulo or bitmasked with some number is the index to which skiplist to store the data in). Then, when you get a query, if it is not already prepared, prepare it and store it in the list. If you find it in the list just reuse it. Of course, it only works with sticky cursors.

For something like TPC benchmarks, it can mean very large savings in time.

Any time you have a storm of small, similar queries, think 'prepared statement'

IMO-YMMV