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. _______________________________________________ DB-SIG maillist - DB-SIG@python.org http://mail.python.org/mailman/listinfo/db-sig
