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

I do exactly this.  The performance gain on such queries can be
enormous.  For fast, simple queries (select a,b from t where k), the
turnaround time is about half when using prepared statements.  The
overhead of caching them on the client is a small price to pay.  This
performance gain is on top of a roughly 10-15% gain by utilizing the
parameterized interfaces (ExecParams and ExecPrepared).

Are these enhancements to the libpq interface going to allow a faster
way to fire prepared statements?  In other words, is the proposal a
faster method than ExecPrepared?

Merlin

Fri, 15 Oct 2004 10:00:48 -0400
"Merlin Moncure" <merlin.moncure@rcsonline.com>
Re: libpq and prepared statements progress for 8.0