[Rails-core] Re: slow eager loading ( & fix)

[Frederick Cheung]

On 7 Aug 2007, at 22:37, Trevor Squires wrote:

Hey,

2 comments from me:

1 - using :include one-level-deep when you are fetching *one*
toplevel object *and* you are not issuing :conditions on
the :included tables is *always* (well, I've never found an
exception) slower.



is slower than:

x = Foo.find(4678)
x.incoming_messages
x.outgoing_messages

If your reaction is to say "but it's always faster with eager
loading" then I urge you to *measure* it and get back to me if you
find that my measurements are wrong.

x = Foo.find(4678. :include => [:incoming_messages, :outgoing_messages])

Ah yes, you are right. It's not a huge difference but its definitely there.

2 - I've got a plugin that improves your situation where you are
fetching multiple toplevel objects *and* you don't have
any :conditions that relate to the associations you're pulling in.

foos = Foo.find(:all, :hydrate =>
[:incoming_messages, :outgoing_messages])

It will split that find() into 3 queries, wiring up the relation
targets.   I've chosen the explicit :hydrate option to give the user
more control over the strategy for pulling in associations and it
works just fine with Rick O's scope plugin too.

I've been sitting on the plugin since railsconf *last* year and never
released it because I had so many doubts about whether the strategy
was solid enough.  I've used it enough times that I'm confident it
works.  I'll be releasing it for public consumption in the next
couple of weeks.

Very interesting, I look forward to seeing it!

Fred

Trev

On 7-Aug-07, at 1:55 PM, Jeremy Evans wrote:

On 8/7/07, Frederick Cheung <[EMAIL PROTECTED]> wrote:

Executive Summary:
================

I've recently come across some performances problems with eager
loading multiple has_many relationships (and to a lesser extent a
single has_many with many objects in the collection) and had some
thoughts.

The case I came across involved some models like so:

class Question < ActiveRecord::Base
   has_many :incoming_messages
   has_many :outgoing_messages
end

class IncomingMessage < ActiveRecord::Base
   belongs_to :question
end

class OutgoingMessage < ActiveRecord::Base
   belongs_to :question
end

In various parts of the app we load a question (or multiple ones)
with :include => [:incoming_messages, :outgoing_messages]

Typically a question has  a small number of incoming and outgoing
messages (often only 1 or 2) and this all works absolutely fine.
However at some point we ended up with a question with many incoming

had a look under the hood.

The underlying thing is that in this case Question.find(1, :include

and so even fairly small things add up very quickly


some numbers

Numbers:
===========

In my benchmarks I've used 2 instances of Question: one with 150

goes up by 2.25)

and outgoing_messages. Our servers (quite literally) ground to a halt whenever loading that question with the aforementioned includes, so I => [:incoming_messages, :outgoing_messages]) returns quite a few rows I've put together some changes that improve the situation, along with incoming and 80 outgoing (big question) and one with 225 incoming and 120 outgoing (huge question) (ie 50% more of each, so total row count

The main issue you are running into is that Rails' SQL queries for
multiple included has_many associations return the cartesian product

to send two or three separate SQL statements.  You'd have one
statement for each association, and then combine them together.  The
most efficient way is probably n+1 queries where n is the number of
has_many associations, with one query to get the information on the
main object, and one query for each has_many association, that only
includes the association information and the main object's id (in
order to associate it).  That would shorten the number of rows

to 346.  It's more complex than the current implementation, but it
will preform much better.  I'm not volunteering to implement it,
though. :)

As a workaround, how about:

question = Question.find_by_id(big_question.id, :include
=> :incoming_messages)
question.instance_variable_set('@outgoing_messages',
Question.find_by_id(big_question.id, :include =>
:outgoing_messages).outgoing_messages)


lazy loading has_many associations (eager loading belongs_to
associations is fine).    Eager loading has_many associations should
only be done if you are getting multiple objects at once (i.e. find
:all).

Jeremy

of the has_many_associations. Ideally, the best way to handle this is returned for the queries you mention from 12,000 to 231 and from 27000 Also, note that for a single object, you are probably better off using

--~--~---------~--~----~------------~-------~--~----~

To post to this group, send email to rubyonrails-core@googlegroups.com

You received this message because you are subscribed to the Google Groups "Ruby on Rails: Core" group. To unsubscribe from this group, send email to rubyonrails-core- [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/ group/rubyonrails-core?hl=en

-~----------~----~----~----~------~----~------~--~---

smime.p7s
Description: S/MIME cryptographic signature