CODEWITHSUNDEEP
pythonmysqlsqlalchemyflask-sqlalchemy
zt50tz
zt50tz

Reputation: 155

Speed up updating items individually

I have 12000 items in a table and I want to update and commit each item individually.

items = db.session.query(User).all()

for item in items:
    item.active = 0
    # do stuff
    db.session.commit()

I need to commit every iteration so that changes goes to database immediately. If I select all rows in my first query the commit is slow. If I select smaller set of data (400 rows for example) it is fast.

What should I do for better performance without selecting a smaller set of data?

Upvotes: 0

Views: 167

Answers (1)

davidism
davidism

Reputation: 127210

SQLAlchemy caches all the queried items internally, but expires that cache when a commit is issued. So the instance accessed in the next iteration is in the "expired" state and SQLAlchemy re-queries the database. So you're effectively doing:

  1. a massive query at the beginning for 12000 items
  2. 12000 commits
  3. 11999 queries for individual items
  4. 24000 queries total

The difference you're seeing when you only select 400 the first time is merely an effect of scale: you only go through one third as many select/commit cycles.

There is no documented way to prevent the expiration mechanism from happening. You can either put up with making 24000 queries, or you can commit once at the end.

You will most likely be better off just committing after the loop in one large transaction.

Upvotes: 1

Related Questions