On May 2, 2011, at 11:14 AM, Adrian wrote:
I'm facing some interesting speed issues with my database that only
seem to crop up within sqlalchemy. I have 2 tables in a one-to-one
relationship, with about ~1 million rows each. From Python, I grab an
object from one table (table A):
rowA = session.query(A).limit(1).one()
And then access a row in table B:
blah = rowA.b.someRow
I noticed that for ~500 of these, it started taking a long time - so I
wrapped it in time() calls:
time1 = time.time()
blah = rowA.b.someRow
print time.time() - time1
What I find is that when I run this in Python, it takes ~0.01 seconds
per object, but when I execute it directly in the database:
EXPLAIN ANALYZE SELECT *
FROM a
WHERE a.b_pk = 20;
It only takes ~0.01 milliseconds! Is there some optimization I can do
from within the Model Class definitions, or do I really need to
execute raw sql from my code to get raw speed?
a. using EXPLAIN ANALYZE does not take time spent fetching rows, network
overhead, or the communication between the DBAPI and the database into account
. A more apt comparison would be against raw DBAPI access using
connection.cursor() where all rows are fetched.
b. turn on echo=True, see what SQL is being emitted.
c. assuming every rowA.b.someRow is a lazyload of the related row, you are
executing 2x as many queries. Use eager loading:
http://www.sqlalchemy.org/docs/orm/loading.html
d. it is faster to load individual columns, that is sess.query(cls.id,
cls.name), rather than the whole object, i.e. sess.query(cls). The latter has
a lot more bookkeeping to perform.
e. Emitting hundreds of single object queries is by far the least efficient
way to get data back, it would be much better if you could emit fewer queries
to load records in batches of 1000 or so.
f. ORMs add lots of overhead as the cost of automation and state management,
not unlike Python itself does at the cost of high level, easy to use objects
instead of writing raw C code. Some perspective on this specific to
SQLAlchemy is described here:
http://techspot.zzzeek.org/2010/12/12/a-tale-of-three-profiles/
Thanks,
Adrian
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
