Flask-SqlAlchemy insert extremely slow after load large amount of data

user_2669704 0
Viewed 415

I have a table with about 200k records. I'm trying to insert abount 20 records in batch with Flask-SqlAlchemy. It takes about 20ms normally, but when I load all the records from the table before insertion, it takes about 1000ms(50x slower). Why?

from models import LocationMap, db
import random
import time
# the last line 'db.session.commit()' will be extremely slow when I uncomment this line.
#known_locations = LocationMap.query.all()
for i in range(10):
    loc = LocationMap()
    loc.longitude = 0 #location_batch[i + j][0]
    loc.latitude = random.random() #location_batch[i + j][1]
    loc.country = 'test'
    loc.province = 'test2'
    loc.city = 'test3'
    loc.district = 'test4'
    loc.township = 'test5'
    db.session.add(loc)

st = time.time()
db.session.commit()
print(time.time() - st)
flask sqlalchemy python performance
1 Answers

I think it has to do with tracking / state of instances, references, identity map etc. In other words - I think the reason is mainly how similar ORM's work (sqlalchemy is not tiny ORM). This is why these types of tools are inferior in performance when working with large datasets. But they have very powerful functionality.

I will try to explain with an example:

locations = LocationMap.query.all()
locations[0].country = 'new value1'
locations[1].country = 'new value2'
for i in range(10):
    # ... db.session.add(loc)

db.session.commit()

What's going to happen? Yes - we will have not only inserts. We will also have 2 updates(new value1, new value2). Because Session has a references to all objects. And now let's try to remove selected instances from a Session:

locations = LocationMap.query.all()
db.session.expunge_all()
# your code here... for i in range(10):

You'll see that operation takes ~20ms(normal time). Also you can remove specific object from a Session:

locations = LocationMap.query.all()
for l in locations:
    # if we don't need reference anymore
    db.session.expunge(l)

Otherwise, you can say no to ORM if performance is critical to you.

user_49296460
Related