On May 28, 2010, at 5:18 PM, Jason Baker wrote: > On Fri, May 28, 2010 at 3:48 PM, Michael Bayer <[email protected]> > wrote: > > This is the ORM affecting 124K statements so must be a very large data > persist (it seems like a persist heavy operation, i see 287K objects total > treated as part of units of work). > > It seems like you are calling commit() a very large number of times. So the > most obvious enhancement would be to call this a whole lot less - the commit > necessitates a flush, and also by default fully expires the session (unless > you turn off expire_on_commit), meaning all rows have to be fully reloaded, > which is probably making the number of statements executed much larger than > it needs to be. > > Unfortunately committing less isn't doable. :-( > > We need to have a transaction for each record we process otherwise, we'd have > to go back and clean up if something failed. I will try turning off > expire_on_commit and see if that helps though. > > A good deal of time is spent in compiling constructs into SQL strings here, > there is a feature whereby this can be cut down dramatically for similar > statements executed many times which is the "compiled_cache" execution > option. The ORM uses this a bit internally now though it might be a nice > feature for you to be able to switch it on for a given session, and have all > SQL statement compilation cached for the life of that session. This feature > can be approximated right now though I'd want to modify _save_obj to not > overwrite the cache with its own, which defeats the usage of a > session-spanning compilation cache. > > That would be a *huge* help. Would this involve a custom Query subclass?
you can use it right now like this (assuming usage of a scoped session):
connection = engine.connect()
compiled_cache = {}
session = MyScopedSession()
session.bind = connection.execution_options(compiled_cache=compiled_cache)
# do many things with the session. every SQL construct would store its
"compiled" form in that dictionary
MyScopedSession.remove()
so that will work now, *except* that most mapper._save_obj() calls are going to
replace the cache with its own that is local to the _save_obj() call for the
duration of its call, so it doesn't get to take advantage of that cache. The
adjustment would be that save_obj checks for a cache in place already. I'd
probably add an execution_options() method to Session. A patch is attached
which should achieve this (not tested):
session.execution_options(compiled_cache={})
# good to go
>
> --
> Jason Baker
> Developer
> ZeOmega
> 3010 Gaylord Parkway, Suite 210
> Frisco, TX 75034
> O: 214-618-9880 ext 8024
> [email protected]
> www.ZeOmega.com
> Proven. Progressive. Partner.
>
> --
> 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.
-- 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.
|
session_compiled_cache.patch
Description: Binary data
