[DB-SIG] New take on PostgreSQL bindings for Python
Cristian Gafton
gafton at rpath.com
Wed Sep 6 18:09:20 CEST 2006
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.
More information about the DB-SIG
mailing list