SQLAlchemy getting slow when do a lots of things in transaction

927 views
Skip to first unread message

Victor Lin

unread,
Aug 17, 2008, 8:50:13 AM8/17/08
to sqlalchemy
Hi,

I am using SQLAlchemy to write a application.
I add a losts of rows into database and commit.

Code like this:

for item in itemList:
# do some query
session.query(...)
# add a row
newItem = newItem(...)
session.commit()

At first few rows, it gos fast.
But after that, I found the loop getting slow when there is more and
more data in one transaction.
If I put commit inside loop, it runs very fast.

for item in itemList:
# do some query
session.query(...)
# add a row
newItem = newItem(...)
session.commit()

What makes my program getting slow? And how to solve that problem(all
stuff in transaction).
Thanks.

Victor Lin.

Michael Bayer

unread,
Aug 17, 2008, 10:16:20 AM8/17/08
to sqlal...@googlegroups.com


the size of the object list to be reconciled and sorted during flush
gets very large - so issue a flush() after every 100 or so entries
added to the session. they'll all execute within the context of the
enclosing transaction.

Victor Lin

unread,
Aug 18, 2008, 1:02:50 PM8/18/08
to sqlalchemy
I found that it seems not the transaction cause the performance
problem.
I think it maybe cause by use one session for a long time.
Performance getting slower with the program run longer time.
Once I stop the program and run it again, at first it works fast.

So I think it is the problem of using a session for too many query and
insert?
I think the identify map or something else getting bigger and bigger
with time.

Is that correct? How to solve this problem?
Thanks.

Victor Lin.

Michael Bayer

unread,
Aug 18, 2008, 1:16:08 PM8/18/08
to sqlal...@googlegroups.com

On Aug 18, 2008, at 1:02 PM, Victor Lin wrote:

>
> I found that it seems not the transaction cause the performance
> problem.
> I think it maybe cause by use one session for a long time.
> Performance getting slower with the program run longer time.
> Once I stop the program and run it again, at first it works fast.
>
> So I think it is the problem of using a session for too many query and
> insert?
> I think the identify map or something else getting bigger and bigger
> with time.
>
> Is that correct? How to solve this problem?

Assuming youre on at least version 0.4 with the default settings, the
Session only holds onto objects that are referenced elsewhere in your
application. If your app needs to hold many objects in memory but
you dont want them all being maintained within the Session, remove
them using Session.expunge(someobject) or everything using
Session.expunge_all() (which used to be called Session.clear()). The
flush() process in particular may be scanning the whole collection of
objects.

The 0.5 session has some enhancements such that a flush() usually does
not scan the entire Session's contents.

Victor Lin

unread,
Aug 18, 2008, 11:47:44 PM8/18/08
to sqlalchemy
Finally, I found the real reason of performance problem.
There is a pickle filed in my table. SQLAlchemy update all rows every
query. That's why it is so slow.
By following the guide of FAQ, I have solved that problem.

Thanks your help.
Victor Lin.
Reply all
Reply to author
Forward
0 new messages