Cristian Gafton wrote: > 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. >
Actually, in Oracle (via cx_Oracle) you can use the same parameter name for each placeholder and the values you provide will be assigned in order. e.g. you can use; >>> stmt = "SELECT * FROM table WHERE col1 = :param AND col2 = :param" >>> cursor.execute(stmt, '1', '2') And its quite valid. >> 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. > [snip] > > Cristian Err, actually all you save in Oracle with bind parameters is the parse component. This can be of the order of several CPU cycles but doesn't materially affect the execution speed of the fetch part of the statement which is usually much larger. What it *does* do is stop the SGA filling up with multiple variations of the same explain plan (as you indicate). That is what causes DBAs to get militant. I think we can all agree that using bind parameters is a good thing. However, I disagree with your conclusions about how they are implemented by certain DB-API modules. The DB-API doesn't need changing (apart from the removal of the 'format' paramstyle) but some of the modules that implement it do. Regards, Andy -- -------------------------------------------------------------------------------- From the desk of Andrew J Todd esq - http://www.halfcooked.com/ _______________________________________________ DB-SIG maillist - DB-SIG@python.org http://mail.python.org/mailman/listinfo/db-sig
