Update this issue is resolved in edge rails, if you are using a version number higher than 2.0.2 you should be safe

ActiveRecord eager loading is a force to be reckoned with. Recently, I had to write some reports that span many models and thought I would save myself some database round trips by eager loading the data.

What is eager loading you ask?

The ActiveRecord “documentation”:Peak Obsession says: “Eager loading is a way to find objects of a certain class and a number of named associations along with it in a single SQL call. This is one of the easiest ways of to prevent the dreaded 1+N problem in which fetching 100 posts that each need to display their author triggers 101 database queries. Through the use of eager loading, the 101 queries can be reduced to 1.”

Wow, that sounds fantastic, if I have a post with 100 comments I can load it all up in one round trip to the database. But there is a catch here, the current implementation of eager loading leaves a lot to be desired.

Imagine if you have a post with 100 comments and 100 images. If you eager load your data the SQL produced may surprise you:

Post.find(:all, :include => [:comments, :images])

ActiveRecord will generate a single query that left joins comments post.id and left joins images on post.id .

The problem is that the database will give you back 10,000 rows for a single post. That is seriously flawed.

So, imho a patch is needed here, this feature is just too dangerous to have in rails.

The solution

Well the code needs to be a little more sophisticated. The most efficient way of retrieving all the posts with all the images and comments is.

  • Get all the posts
  • Get all the comments
  • Get all the images
  • update all the post models with comments and images loaded in 2/3

So, when eager loading, I think, the best way is executing a query per model when dealing with has_many associations. For has_one and belongs_to the current implementation is alright.

You should be really careful with eager loading in the current ActiveRecord, it can take out your “production servers”:http://toolmantim.com/article/2006/9/29/web_connections_eager_loading_headaches and cause huge memory spikes.


Tim_Lucas about 16 years ago

I don’t think there’s anything inherently flawed about the current implementation, its just that it doesn’t stop you from being stupid. Some alternate eager loading strategies could be useful, like for the example you gave. Sounds like a useful plugin and area to look into

Sam Saffron about 16 years ago
Sam Saffron

Actually, it looks like its now fixed in edge rails!


comments powered by Discourse