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