Hello, please allow me to advertise the progress with the "binary" cursor.
What is it?
Upstream branch of psycopg2 communicates Postgres using text-only SQL
commands. That is, it "mogrifies" all parameters passed to a query and builds a
single SQL string to send. In a similar way, it requests for ASCII-encoded
results from the db.
The "exec params" branch tries to use PQExecParams() , which does allow for a
binary mode of data transfer. That is, parameters to queries are sent in
binary (isolated) chunks, to the server.
In an earlier conversation, we have decided to implement that using a different
class of cursor and type adapter "ISQLParam".
In theory, this would speed up the DB protocol layer, while also protecting
from SQL injections (because data flows in truly separate channel).
So far, I've only implemented the send (aka. client->db) direction, only
adapted a few basic types (like int, varchar) to binary mode. The rest works
in "fallback" mode, still in text.
Initially, a fabricated query ( "SELECT $1 " + (int(42),) ) did show an
impressive performance boost over the text mode.
However, first tests, yesterday[1] have indicated a slight regression, lower
speed than text mode, in a real situation. I guess it's still too early to
judge[3].
Also, keep in mind that typecasting will be more strict with that cursor,
because PQExecParams() requires us to explicitly tell the types of each
parameter. For example, passing a date like a string won't work any more[2] ;
you have to specify like $1::DATE ( $1='2013-03-19' ) .
The (highly experimental) work is at:
https://github.com/xrg/psycopg/tree/mageia-exec
and I would really appreciate your feedback on it!
[1] first time OpenERP-F3 could run in "binary" mode.
[2] other solutions to this are welcome, of course
[3] "fallback" mode can be a bit more expensive, now. Also, the kind of
queries I'd been sending had too few parameters so far.
--
Say NO to spam and viruses. Stop using Microsoft Windows!