DBAPI Paramstyle

Tim Roberts wrote:
> In theory, using a paramstyle allows the query to be sent to the SQL> database backend and compiled like a program.  Then, successive uses of the> same query can be done by sending just the parameters, instead of sending> the entire query string to be parsed and compiled again and again.> This is commonly done with large production databases like SQL Server> and Oracle.  For a complicated query, it can be a significant time> savings.

Actually, the slow part is building a query execution plan (deciding
which indices to use, join orders, and such).  Identifying what is
being asked for is trivially fast (compared to any I/O).  The DB
optimizer works with estimates of running time for many of the possible
query plans, and chooses the cheapest of those -- that combinatorial
problem is how a DB can chew up CPU time (or save it in some cases).

> However, to the best of my knowledge, none of the Python dbabi> implementations actually do that.  
This cacheing need not occur anywhere before the DB.  There are database
systems (DB2 is one) that cache plans by the text of the query.  If your
query is an exact match with a previously optimized plan (and it hasn't
been chucked out of the cache), the plan is retrieved.

> So, the primary benefit of the paramstyle method is that the database > provider inserts whatever quoting is required; you don't have to> remember to put single quotes around the arguments, and protect> single quotes within the arguments by doubling them, and so on.

This _is_ a benefit.

Another benefit (greater in my estimation) ls that  you are separating
"code" and data to make a more readable (and malleable) query.  If
things get slow and database access is "at fault", you can carry
your SQL to a DB expert (sometimes one on site) who can check it to
help speed up your system.  The DB expert is an SQL dweeb, and will
appreciate seeing the SQL done in a straightforward manner.  She or he
may be able to rewrite the query to improve your access.  Perhaps
several related queries can be effectively combined (but again, you
are talking about an expert in SQL/DB -- they'll need to see all
the queries easily in order to help.  Perhaps the data access
pattern will suggest a change in DB indices (in which case the
database administrator can speed up your program without your
changing any of your code).

You could also be changing the format used to send the data to the
database (of that I'm not quite as sure).

--Scott David Daniels
Scott.Daniels at Acm.Org