#22 Eager Loading

Great stuff as usual.

I'd like to have seen the before and after results of including comment and user counts on the task list and how the database queries were affected when you added them to the find include.

Just to echo the praise above, thank you! I too look forward to seeing an update in my RSS reader; keep up the great work.

Thanks everyone for the encouraging comments.

@Ted: I think you'll only see a boost in performance if you're displaying the comment and user name for each task in the list. Each task will need an additional query to fetch the comments, and each comment would need another query to fetch the user name. If you're displaying a significant number of tasks with comments then this can get out of control. Eager loading will reduce this all to one big query with lots of joins.

That said, if you're just displaying the number of comments and not the entire comment content, you won't see much of a boost in performance with eager loading because it will be fetching more information from the database (the content of the comment) and not just the number of comments. I cover this problem in the next episode.

Nice screencast.

Do you ever get name clashes with eager loading? IE, since a project and a task both have the column 'name', when using eager loading you might get a Invalid SQL exception.

Hey Ryan,

  Thank you for putting out great screencasts. Great work. Great quality. Awesome contribution to the community.

@Hugh,

Good point, I should have mentioned this in the screencast. You can avoid name clashes by specifying the table name in front of any mentioned column name. For example:

Topic.find(:all, :include => :project, :order => 'topics.name')

You can do the same in the conditions parameter and anywhere else.

Note to self: add some code formatting in comments.

I love Railscasts, it's a simple no fuzz approach to learning rails. I do wish I could watch the podcast on my Apple TV however. Just a thought. Keep up the good work!!!

Railscasts are coming soon to an AppleTV or iPod near you.

Love the screencasts and the recent additions of the site.

Have you thought about doing screenshots or having sample code up next to each video?

The reason I ask -- I'm trying to remember something you did with a User model and the =|| operator, but I can't remember which video it was in!

Anyway -- thanks for sharing the knowledge and putting out a great site!

@Rebort,

Great ideas, thanks. I'm trying not to add to the amount of work for each episode, but I'll give it some serious consideration.

The ||= operator is described in the very first episode on caching with instance variables.

Congratulations ;)
Ryan can you explain the link_to xxx, xxx_path(xxx)? I tried it and it didn't work.

@Pepe, the "_path" method is called a named route. This is generated automatically when you are doing a RESTful design. I'll try to cover it in a future episode.

Great idea, thanks.

Have you ever considered getting more coders on board to contribute railcasts? I ask purely out of greed, I just can't get enough or RoR screencasts :)

I enjoy the screencasts. Some of them I find to be a little too basic, but I often get something worthwhile out of them. I do, however, like the path you're taking--it seems like the topics are getting more and more in-depth.

As a future request, I would love to see a screencast or two on modules and mixins. I'm starting to get into meta-programming, but I find it really, really confusing in the beginning.

Keep up the good work!

Clear, concise, and very useful. Thanks for your excellent Railscasts.

What are you using to get the pretty-printed live display of your development.log in the Terminal?

What are you using to get command-key strokes displayed in that little popup near the top right?

Thanks!

@Jack, maybe someday I'll get more contributors to Railscasts, but at the moment I like to keep things simple and controllable.

@Ryan, an episode on modules and mixins is a great idea. I'll add it to my list. :)

@Grant, the live display of the development.log file is just coming from when I run "script/server", I guess mongrel is automatically set up to display this. If it's not displaying it for you, you can run this command (probably won't work on Windows)

tail -f log/development.log

The keystrokes are being displayed using KeyCastr.

http://stephendeken.net/software/keycastr/

Thanks for the encouraging comments folks!

Thanks for the answers. KeyCastr is a very good idea for screencasts.

I’ve been using Locomotive on Mac OS X for my Rails development, so I hadn’t been calling script/server directly. I had also just been opening my logs in Textmate or TextWrangler, so hadn’t realized there was pretty-printing that would pick up in tail.

Thanks again!

I have code in helper:
  def page_methods(page)
    str = " "
    str += ( link_to blabla ) unless page.first?
    str += ( link_to blublu ) unless page.last?
    str
end

I found that first? and last? makes new query to database. How can i tell to use already made page query where i fetch all data.

@InMan,

Are you using some form of pagination here? I can't tell why it is querying with just that code. I suggest starting a thread on http://railsforum.com instead of discussing it here. That way you can post more code. I'll try to respond there if I know the answer.

I forgot to say that i am using plugins:
acts_as_tree
acts_as_list :scope => :parent_id

Just wanted to say that your Railscasts have been helping me tremendously while trying to learn rails. I've worked my way out of several tough spots to grasp using them, thanks!

I had one question about this episode though. I am working on a gallery type page for my portfolio, and I am using eager loading to call up categories, projects, and images all at once. I am wondering if there is a way to limit just one of the models returned? For example I only want to retrieve 5 projects per page. Where would I put the limit condition? This is what I have in my category model right now:

def self.portfolio
  find_all_by_parent_id(2, :include => { :projects => :images }, :conditions => 'images.default = true' )
end

Thanks!

Very good question. AFAIK the limit clause in SQL is very limiting, and I don't think it's possible to do this with an SQL LIMIT (find :limit parameter). See if fetching all of the projects gives you decent performance. If not you could try not fetching the projects and doing a separate SQL query for each category, fetching the limited projects. This is a good way to go if you don't have many category models.

If you want to do this all in one query, I'm sorry I don't know how. I'm sure it's possible with a custom SQL query, but I'm not proficient enough with SQL to know.

now that rails edge has query caching, eager loading may not always be the optimal solution, esp. with :select not working, one has to pull every column of all the tables :include'd.

see technoweenies "trick":
http://activereload.net/2007/5/23/spend-less-time-in-the-database-and-more-time-outdoors
http://pastie.caboo.se/63741

me thinks it would be really cool to have a follow-up on this episode. :)

in context with my previous post, here I have another nice link concerning the problems with eager loading:

http://darwinweb.net/article/Free_Form_Manual_Eager_Loading

Great episode! For anyone using eager loading on large databases, to get a huge performance boost you should set the column to index on the database of the attribute you're including, e.g. setting the task_id column in the projects table to be an index column using the sql command
ALTER TABLE `projects` ADD INDEX ( `task_id` )

Excellent tutorial :-)

Is it possible to get one piece of related data with out actually making a model for it? For example, I have Places and Countries, but I don't need/want a Country model, so I did Place.find(:all, :joins => 'LEFT JOIN countries ON places.country_id = countries.id'). The query executes, but I can't figure out how to access the resulting country name. Does anyone know?

Thanks!

You can do this on the fly in Lore (lore.rubyforge.org), and by configuration, even.

On the fly:

  User.all.join(Project).using(:project_id).entities

Or by configuration (eager load every time)

  model User < Lore::Model
    table :user, :public
    aggregates Project, :project_id
  end

User.all.entities # (now automatically joins referencesd project)

I was wondering if scoping is possible with eager loading. Without using scope things work fine, but when scoping is introduced new queries get fired.
Problem in detail : here

Thank you very much Ryan ! As usual a fantastic tutorial

This episode has been updated for Rails 5 as a blog post. Eager Loading in Rails 5