Need help porting limited eager loading optimization to Rails 3
46 views
Skip to first unread message
Victor Lin
Jul 6, 2011, 2:17:48 PM7/6/11
to rubyonra...@googlegroups.com
I'm fairly certain this 2.x feature never made it to rails 3. You can view the original ticket and patches here: http://web.archive.org/web/20081005125758/http://dev.rubyonrails.org/ticket/9560
The gist of it is, when you run something like this:
Article.includes(:comments, :author).where('authors.id = 1').limit(10)
You end up with two queries. The first is a "SELECT DISTINCT authors.id...", and the next will actually load the comments and authors associations. In Rails 2.x, AR was smart enough to only join against the tables that actually limited the resultset (e.g anything in the where or order clauses). Rails 3 will blindly join all the tables, which kills performance when you have several eager loaded associations.
I started working on a patch to apply_join_dependency but ran into a problem with table aliasing. The diff is here:
https://gist.github.com/1067917
The approach is basically to scan the order and where clauses for table names. Then scan the included associations for these table names, adding them (and any intermediate joins) to a list, and only joining those associations. The problem is when the arel object is built for clean_relation, it has fewer joins than the original. AR builds a JoinDependency object and JoinDependency#graft's all my joins to it. That object never actually sees any of the original joins, and so the alias tracker hands out the default table name instead of the aliased table name. I don't know how to deal with this without hacking up more of the source than I want to - anyone have any ideas about how to deal with this? Or maybe a completely different approach?
The gist of it is, when you run something like this:
Article.includes(:comments, :author).where('authors.id = 1').limit(10)
You end up with two queries. The first is a "SELECT DISTINCT authors.id...", and the next will actually load the comments and authors associations. In Rails 2.x, AR was smart enough to only join against the tables that actually limited the resultset (e.g anything in the where or order clauses). Rails 3 will blindly join all the tables, which kills performance when you have several eager loaded associations.
I started working on a patch to apply_join_dependency but ran into a problem with table aliasing. The diff is here:
https://gist.github.com/1067917
The approach is basically to scan the order and where clauses for table names. Then scan the included associations for these table names, adding them (and any intermediate joins) to a list, and only joining those associations. The problem is when the arel object is built for clean_relation, it has fewer joins than the original. AR builds a JoinDependency object and JoinDependency#graft's all my joins to it. That object never actually sees any of the original joins, and so the alias tracker hands out the default table name instead of the aliased table name. I don't know how to deal with this without hacking up more of the source than I want to - anyone have any ideas about how to deal with this? Or maybe a completely different approach?
Oriol Gual
Jul 6, 2011, 3:35:03 PM7/6/11
to rubyonra...@googlegroups.com
I'm not really sure, but maybe hacking directly Arel
(https://github.com/rails/arel) instead AR is easier to solve this
issue.
(https://github.com/rails/arel) instead AR is easier to solve this
issue.
> --
> You received this message because you are subscribed to the Google Groups
> "Ruby on Rails: Core" group.
> To view this discussion on the web visit
> https://groups.google.com/d/msg/rubyonrails-core/-/As1BiWORxRAJ.
> To post to this group, send email to rubyonra...@googlegroups.com.
> To unsubscribe from this group, send email to
> rubyonrails-co...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/rubyonrails-core?hl=en.
>
Ernie Miller
Jul 6, 2011, 4:26:14 PM7/6/11
to Ruby on Rails: Core
On Jul 6, 2:17 pm, Victor Lin <victorh...@gmail.com> wrote:
> I'm fairly certain this 2.x feature never made it to rails 3. You can view
> the original ticket and patches here:http://web.archive.org/web/20081005125758/http://dev.rubyonrails.org/...
> I'm fairly certain this 2.x feature never made it to rails 3. You can view
>
> The gist of it is, when you run something like this:
> Article.includes(:comments, :author).where('authors.id = 1').limit(10)
>
> You end up with two queries. The first is a "SELECT DISTINCT authors.id...",
> and the next will actually load the comments and authors associations. In
> Rails 2.x, AR was smart enough to only join against the tables that actually
> limited the resultset (e.g anything in the where or order clauses). Rails 3
> will blindly join all the tables, which kills performance when you have
> several eager loaded associations.
>
> I started working on a patch to apply_join_dependency but ran into a problem
> with table aliasing. The diff is here:https://gist.github.com/1067917
>
> The approach is basically to scan the order and where clauses for table
> names. Then scan the included associations for these table names, adding
> them (and any intermediate joins) to a list, and only joining those
> associations. The problem is when the arel object is built for
> clean_relation, it has fewer joins than the original. AR builds a
> JoinDependency object and JoinDependency#graft's all my joins to it. That
> object never actually sees any of the original joins, and so the alias
> tracker hands out the default table name instead of the aliased table name.
> I don't know how to deal with this without hacking up more of the source
> than I want to - anyone have any ideas about how to deal with this? Or maybe
> a completely different approach?
Victor,
> The gist of it is, when you run something like this:
> Article.includes(:comments, :author).where('authors.id = 1').limit(10)
>
> You end up with two queries. The first is a "SELECT DISTINCT authors.id...",
> and the next will actually load the comments and authors associations. In
> Rails 2.x, AR was smart enough to only join against the tables that actually
> limited the resultset (e.g anything in the where or order clauses). Rails 3
> will blindly join all the tables, which kills performance when you have
> several eager loaded associations.
>
> I started working on a patch to apply_join_dependency but ran into a problem
> with table aliasing. The diff is here:https://gist.github.com/1067917
>
> The approach is basically to scan the order and where clauses for table
> names. Then scan the included associations for these table names, adding
> them (and any intermediate joins) to a list, and only joining those
> associations. The problem is when the arel object is built for
> clean_relation, it has fewer joins than the original. AR builds a
> JoinDependency object and JoinDependency#graft's all my joins to it. That
> object never actually sees any of the original joins, and so the alias
> tracker hands out the default table name instead of the aliased table name.
> I don't know how to deal with this without hacking up more of the source
> than I want to - anyone have any ideas about how to deal with this? Or maybe
> a completely different approach?
I feel your pain, but in my opinion, moving further down the road of
scanning queries for strings to determine a table's inclusion takes
things in the wrong direction. The trend in core (and one that I hope
continues) has been toward using ARel objects representing the various
parts of a query. For instance, your order-scanning code is subject to
the same bug that this commit fixes:
https://github.com/rails/rails/commit/08f3f30994d37f6f44acfac801f82fc43127fc78
.
I don't think there's a quick (and also correct) way to fix this
behavior, without modifying more than this one method. Jon or Aaron
might tell me I'm wrong, though. :)
--
Ernie Miller
http://metautonomo.us
Victor Lin
Jul 6, 2011, 5:21:34 PM7/6/11
to rubyonra...@googlegroups.com
I totally agree - scanning sql strings is pretty crappy. Extracting the necessary tables from the ARel object should be more robust, this was just a quick hack to get things going.
I think there's a related performance issue here too, where the second query uses the LEFT OUTER JOIN method of preloading associations instead of issuing separate queries. We have the IDs already from the prequery, there's no need to use the join strategy anymore, right?
I think there's a related performance issue here too, where the second query uses the LEFT OUTER JOIN method of preloading associations instead of issuing separate queries. We have the IDs already from the prequery, there's no need to use the join strategy anymore, right?
Reply all
Reply to author
Forward
0 new messages