[DB-SIG] New take on PostgreSQL bindings for Python

[Cristian Gafton]

On Wed, 6 Sep 2006, Andy Chambers wrote:

> could implement this themselves and instead of writing
>
> "select *
>  from table
> where param = $1"
>
> ..they write this
>
> "select *
>  from table
> where param = %s" % (aparam(),)
>
> Then if you change databases, you only need to redefine aparam().

That's not gonna help much for backends that like named bind parameters, 
like Oracle... But you are right, the current effort spend nowadays to 
escape the arguments and not use bind parameters could be spent rewriting 
the query string to the bind parameter format required by the backend.

> Have you seen how much this actually improves performance?  I myself tried
> writing a dbapi that made use of prepared queries but found that there
> was no improvement over psycopg.

For Postgres, using a prepared statement on a call like executemany() 
gives you roughly 2-2.5x times faster execution for simple 
queries. Probably not much, but for other backends it is more dramatic. In 
MySQL it comes down to ~20x; In Oracle that's even sweeter, because Oracle 
caches the execution plans of the prepared statements and looks them up 
whenever you "prepare" them again in its internal cache, with very 
dramatic effects on the execution speed.

> As I understand it, what you win from not parsing the query, you lose
> in sub-optimal execution path for many types of query.  This is because
> in postgres, the planner uses information in the query to decide which type
> of scan it should use in searching the respective tables.  By using
> PQPrepare, you make the plan without all possible information then keep using
> that sub-optimal plan

That might be true and it is a limitation of PostgreSQL; however, in my 
experience, most prepared statements tend to be quite simple inserts or 
straight join selects (probably with the exception of Oracle, where you 
will have a DBA jumping down your throat for not preparing everything and 
messing up his database's statement plan cache faster than you can say 
"oops"). I think what you are saying might be a reason not to use it in 
certain cases with PostgreSQL, not a reason for the DB API not to define 
it in reasonable way.

Cristian
-- 
Cristian Gafton
rPath, Inc.