On Nov 28, 2011, at 6:21 AM, Amir Sasson wrote: > Our Application is using a single session to access the DB. This > session is opened when the Application is started and closed when the > Application is terminated. The Application holds references to all the > DB objects via some kind of DB objects registry. The multithreading DB > access is protected at the application level using locking mechanism. > > > > My main problem is that a single commit takes ~200-250ms. After > performing some testing, I figured out that the cause for the long > commit duration is due to the SQLAlchemy expire attribute mechanism. > That is, the Application holds references to all the DB objects > (~20,000 objects) and as a result all the DB objects are expired > during the commit which takes most of the commit duration. > When I set expire_on_commit to False, it reduced the commit duration > to only 10ms. > > > > My question is whether I can set the expire_on_commit to False, in our > Application where we hold references to all DB objects, without > comprising the coherency of data while accessing the DB?
There's nothing magic about expire_on_commit - basically if you are confident that you either aren't using those 20K object references subsequent to the commit, *or* you're confident that subsequent changes to their rows are not a problem (i.e. you won't see them without an expiration), then the flag is safe to turn off. A bulk operation like 20K rows is generally not expected to be blazingly fast with the ORM, as there's a lot of bookkeeping overhead that you wouldn't get if you just did direct inserts (such as, the newly generated primary key values and such). Also the expire operation has been sped up in recent releases are you on 0.7 ? -- 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.
