SQLAlchemy poor performance on iterating
<p>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)?</p>

<p>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.</p>

<pre><code>block = []
cnt = 0
for blah in dbsession.query(mytable):
    tic = time.time()
    for a in blah.component:
        cnt += 1
    block.append(time.time()-tic)
print "block: %s seconds    cnt: %s" % (sum(block), cnt)

# block: 20.78191 seconds    cnt: 3021
</code></pre>

<p>Using the suggestion from the selected best-answer, the for loop became:</p>

<pre><code>for blah in dbsession.query(mytable).options( joinedload(mytable.componentA)).options(mytable.componentB)).options(mytable.componentC)
</code></pre>

<p>which resulted in the inner loops of each component going from 20-25s each, to 0.25 0.59 and 0.11s respectively. The query itself now takes about 18s ... so my total saved time is about 55s.</p>

<p>Each time you access <code>.component</code> another SQL query is emitted.</p>

<p>You can read more at <a href="http://docs.sqlalchemy.org/en/latest/orm/loading_relationships.html" rel="nofollow">Relationship Loading Techniques</a>, but to load it all at once you can change your query to the following:</p>

<pre><code>from sqlalchemy.orm import joinedload

dbsession.query(mytable).options(joinedload('component'))
</code></pre>

python python-2.7 sqlalchemy