On Feb 6, 2014, at 1:31 PM, Claudio Freire <klaussfre...@gmail.com> wrote:
> >> There are various performance concerns related to SELECT queries and solving >> them depends much on identifying where a particular performance issue >> resides. There's overhead on the side of Python/SQLAlchemy formulating the >> string statement, theres overhead on the side of passing it to the database >> to be parsed (which is by far the most infinitesimal part) > > That part is most definitely not always so infinitesimally small. I > recall one case (granted, one among so many) of a complex query that > was very fast to execute. The query had dozens of joins, so it took > considerable planning time on the database side. Planning took around > 300ms, where execution took only 25ms. So, having spotted the > bottleneck, I switched to using prepared statements (it was a query > that was run several times in the session, not millions of times, but > several) with different arguments. The benefit was considerable. ideally the database would know how to cache plans based on the string form of the statement. Oracle does this. It appears that Postrgresql does not unless you specifically use a prepared statement. > > DBAPI doesn't have direct support for prepared statements, but hacking > them isn't that difficult. You just have to make sure you send the > PREPARE, EXECUTE and the DEALLOCATE (never forget the DEALLOCATE) on > the same connection. Which is easy, just ask SQLAlchemy's session for > its connection and use it. psycopg2 is an extremely mature library and I find it curious that such a feature has not been added. They’ve posted their rationale here: http://initd.org/psycopg/articles/2012/10/01/prepared-statements-psycopg/ in any case, prepared statements aren’t part of DBAPI nor built in to psycopg2 yet so this is out of SQLAlchemy’s realm, thankfully. If you want to use the recipe at that link it would have to be hand-rolled.
signature.asc
Description: Message signed with OpenPGP using GPGMail
