On Apr 13, 2011, at 9:07 PM, Michael Bayer wrote: > > On Apr 13, 2011, at 8:48 PM, Yang Zhang wrote: > >> We've been finding the SA ORM to be a large component in our >> application execution time. Our application makes frequent queries, >> and most of them simply query whole rows from a single table with some >> filters, but no joins (therefore, very basic queries). We've found >> that an alarming amount of CPU time is being spent on three parts: >> >> 1. Query compilation. Even though the compiled_cache execution option >> exists, the way Query objects are commonly constructed makes compiled >> statements non-cacheable. For example, the statements created often >> have the argument values baked in, and in any case, compiled_cache is >> keyed by instance-equality statements, so unless we pass in the exact >> same statement, we have to compile again. We hacked around this by >> manually managing statement instances for some subset of our most >> common queries so that they can be looked up by compiled_cache. This >> resulted in ~2x improvement. > > > query.filter(SomeClass.somerecord ==bindparam("somerecord"))
I've added a usage recipe for you which allows the compiled cache to be integrated with Query. By calling query.bake(), the statement is affixed to the query and forced to be pulled from the compiled cache. The techniques in this recipe should probably be made into features as it relies too heavily on reimplementing some of the Query object's internals. In particular from_session() is something I've wanted to add for a long time. The ability to get execution options per connection when using Query should probably be added as well, or otherwise Connection should honor the compiled_cache option on the given statement - its currently a bug that this doesn't work or raise an error and ticket #2131 is added for that. You still need to use bindparam() so that the statement is agnostic of its values. The recipe includes a usage example: http://www.sqlalchemy.org/trac/wiki/UsageRecipes/BakedQuery Still to be decided is if the usage of the "baked" query should be made more transparent - such as, perhaps calling query.bake("some name") will always return the Query previously keyed to "some name". If you want to play with the recipe and let me know what patterns work well for you that would be helpful in determining how the feature add proceeds. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.