The following block of code queries a table with ~2000 rows. The loop takes 20s to execute! From my limited knowledge, I don't think I'm doing 2000 queries, just the one, but perhaps I'm not understanding the '.' operator and what it's doing behind the scenes. How can I fix this loop to run more quickly? Is there a way to adjust the top level query s.t. the 2nd for loop is not making a total of 3000 queries (if that's in fact what's going on)?
Here is a test block of code I made to verify that it was in fact this inner loop that was causing the massive time consumption.
block = 
cnt = 0
for blah in dbsession.query(mytable):
tic = time.time()
for a in blah.component:
cnt += 1
print "block: %s seconds cnt: %s" % (sum(block), cnt)
# block: 20.78191 seconds cnt: 3021
for blah in dbsession.query(mytable).options( joinedload(mytable.componentA)).options(mytable.componentB)).options(mytable.componentC)
Each time you access
.component another SQL query is emitted.
You can read more at Relationship Loading Techniques, but to load it all at once you can change your query to the following:
from sqlalchemy.orm import joinedload dbsession.query(mytable).options(joinedload('component'))