On Thu, Feb 6, 2014 at 4:47 PM, Michael Bayer <mike...@zzzcomputing.com> wrote: > On Feb 6, 2014, at 2:43 PM, Claudio Freire <klaussfre...@gmail.com> wrote: > >> On Thu, Feb 6, 2014 at 4:38 PM, Michael Bayer <mike...@zzzcomputing.com> >> wrote: >>> 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. >> >> Well, it cannot. At least not simplistically, because postgres uses >> the literals in the statement (the ones likely to change) >> to >> specialize plan cost, and different values can produce different plans >> (depending on stats). For instance, a query with a where clause >> "deleted=false" that selects 99% of a table would do a sequential >> scan, whereas "deleted=true" might use an index (if there was such an >> index). >> >> They thus don't cache plans unless specifically asked for (with >> prepare), because those plans may very well be inferior (they don't >> use value-specific knowledge). > > right, but from an API POV, its more tedious since we have to track the > handle on the outside. > > This could be implemented as a cursor event even in conjunction with a custom > execution option and possibly using compiled_cache as well.
That would be tricky. You'd have to maintain a tally of which connection prepared which query (think the complications of multi-engine setups), and make sure you deallocate the handle before returning the connection to the pool. Not to mention coming up with unique handles. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
