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 [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
