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
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
