well the thing with eagerloading is that its using outer joins, and if
you are dealing with a joined table inheritance structure its
potentially going to outerjoin to subqueries which themselves contain
joins. Or if you're selecting a LIMIT/OFFSET result, SQLAlchemy will
apply the LIMIT/OFFSET inside of a subquery and attach the eager load
outer joins to the outside of the subquery.
If you're on MySQL, MySQL's optimizer completely chokes on outerjoins
to subqueries, and doesn't perform as well as it should for any kind
of subquery operation. So this might be something to look at. So
in that regard, eager loading should be applied judiciously if you're
on a platform with suspicious optimizer behavior (as should joined
table inheritance in general which I would say is not worth it on
MySQL).
Another caveat about eager loading is if one of your parent objects
contains 10000 children, the eager load result set will be huge, even
if you never access that parent object's children. If the parent or
child objects contain very large columns like BLOBs or such, that adds
a lot of overhead to the wire as well - these can be optimized in
SQLAlchemy by marking them as "deferred".
What steps to take next depend on the specific causes of slowness.
Analzying the SELECT statement SQLalchemy produces as well as how many/
what size of rows/columns it's producing would indicate what the
source of the issue is.
On Feb 18, 2009, at 11:16 AM, Bryan wrote:
>
> When I eagerload an object's 3 nested collections, the SqlAlchemy
> debug output is about 30 lines long. When I don't eagerload, the
> debug output is about 10,700 lines long. So the eagerload is
> definitely using less queries.
>
> However, the eagerload strategy takes many times longer than the
> lazyload. My client connection actually times out before the
> eagerload can return results, while lazyload takes about 17 seconds.
>
> In general, is this to be expected in certain cases, or should
> eagerload always be faster and I am just doing something wrong?
>
> Bryan
>
> >
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
