> -----Original Message-----
> From: pgsql-hackers-owner@postgresql.org 
> [mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of David Wheeler
> Sent: Tuesday, October 05, 2004 10:32 AM
> To: Abhijit Menon-Sen
> Cc: pgsql-hackers@postgresql.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

Tue, 5 Oct 2004 10:47:11 -0700
"Dann Corbit" <DCorbit@connx.com>
Re: libpq and prepared statements progress for 8.0
On Oct 5, 2004, at 10:47 AM, Dann Corbit wrote:

> 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'

Yes, this is how the Perl DBI works. And with Abhijit's patch, DBD::Pg 
(the DBI driver for PostgreSQL) will finally be able to take advantage 
of it.

Regards,

David


Tue, 5 Oct 2004 10:54:09 -0700
David Wheeler <david@kineticode.com>
Re: libpq and prepared statements progress for 8.0