Awesome, I'll work through these suggestions -- thanks for the speedy reply!
On May 2, 11:29 am, Michael Bayer <mike...@zzzcomputing.com> wrote: > 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 > > athttp://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.
