Re: libpq and prepared statements progress for 8.0 - Mailing list pgsql-hackers

From David Wheeler
Subject Re: libpq and prepared statements progress for 8.0
Date
Msg-id 8ECCE6C1-16F7-11D9-BC00-000A95B9602E@kineticode.com
Whole thread Raw
In response to Re: libpq and prepared statements progress for 8.0  ("Dann Corbit" <DCorbit@connx.com>)
List pgsql-hackers
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



pgsql-hackers by date:

Previous
From: "Dann Corbit"
Date:
Subject: Re: libpq and prepared statements progress for 8.0
Next
From: Bruce Momjian
Date:
Subject: Re: win32 tablespace handing