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 ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match