[DB-SIG] Improved support for prepared SQL statements

Michael Bayer mike_mp at zzzcomputing.com
Thu Dec 18 16:39:00 CET 2014

> On Dec 18, 2014, at 2:10 AM, SF Markus Elfring <elfring at users.sourceforge.net> wrote:
>>>> I am looking for higher service levels without following the software
>>>> design directions from object-relational managers like SQLAlchemy
>>>> and SQLObject.
>> If you would like a very mature and well proven SQL abstraction layer
>> that does not provide any design directions whatsoever (not to mention
>> much better performance than the ORM), please consider SQLAlchemy Core 
> Thanks for your suggestion.
> Where and how does this application programming interface deal with prepared SQL statements?
> http://docs.sqlalchemy.org/en/rel_0_9/core/dml.html

because the DBAPI has no prepared statement API (as of yet), the only access we are given to that functionality is if the DBAPI in use already does this internally.   The most common way to take advantage of prepared statements, if they in fact provide a performance improvement on the target backend, is to use the DBAPI executemany() method; if the backing DBAPI uses prepared statements in order to achieve a performance gain here, then you’ll get access to that.   executemany() performs dramatically better than execute() in any case because it’s Python function call overhead that really is the source of performance issues.   SQLAlchemy offers the executemany() API just by passing a list of parameters to the execute() method: http://docs.sqlalchemy.org/en/rel_0_9/core/tutorial.html#executing-multiple-statements, but this only applies to CRUD (insert, update, delete) statements.   

In my experience, caching of prepared statements in modern use is not a major source of performance gain unless your application overall is already blindingly fast, more than Python typically provides.   Consider that a site like reddit.com serves six billion page views a month from Postgresql databases with no use of prepared statements.

More information about the DB-SIG mailing list