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