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.
