Re: contrib: hack for psycopg2 performance

On Mon, May 10, 2010 at 11:17 PM, P. Christeas <[email protected]> wrote:
> On Tuesday 11 May 2010, you wrote:
>> On Mon, May 10, 2010 at 6:45 PM, P. Christeas <[email protected]> wrote:

>> > One main idea is to effectively replace PQExec() with PQExecParams() a=
nd
>> > then avoid most of this quoting, casting =A0etc.
>>
>> PQexecParams has the shortcoming of being able to send only a query at
>> time, whereas PQexec allows to send any number in a single string,
>> separated by semicolons. Many people use this feature and dropping it
>> would be an incompatible change. How would you work around that?

> Perhaps, split the query?

It's tricky with semicolons that may be in comments or in strings: not
impossible, but the query had to be parsed with a good subset of
postgres' sql syntax - it's not the thing you manage to do well let's
say with a regex. Currently I don't think there is code in psycopg
coping with query parsing in any way.  Also keep in mind that now a
single roundtrip can be used to send statements to the server, whereas
using the "*Params" version it would take several roundtrip to have
the same effect, with has its cost.


> Or, at the worst case (async, multiple query, with params etc.), revert t=
o the
> old code..

If you found a good way to use PQexecParams instead of PQexec,
providing better performance or being superior in some other way, it
would be definitely a good thing. But keeping both the code paths and
selecting which one to use on a per-query basis seems complex, so the
advantage of the *Params should be *very* high to offset the burden of
keeping PQexec-based routines around as a fallback.

By the way I actually quite like how in PQexecParams it is possible to
specify for every param whether it is binary or not: this would
actually allow sending binary for basic data types, and falling back
to the textual representation for the less used one, allowing a
progressive and incremental transition. On the other hand the
performance increment comes at the cost of knowing the binary
representation of the different data types (it actually seems more an
interface leak than a feature...) and there is room for *a lot* of bug
in this area!

So I think potentially there are some improvements that can be done,
but you will have to fight against the fact that psycopg is a very
generic library (I think PQexec was chosen in first instance exactly
for being more generic than the *Param equivalent) and trying to
replicate the effect of a generic function by composition of less
generic ones is not an easy task.

-- Daniele