Eager loading comments associated with user submissions.

Hey everyone,

I am working on an application that allows users to submit articles,
and also comment on those articles. Pretty much exactly like Digg.
Everything works fine except that when there are a lot of comments
there are a lot of database queries to both fetch the comments and the
users who posted those comments. I have used eager loading in other
parts of my application to reduce the amount of database queries, but
when I apply the same practices in this situation it doesn't seem to
work. Here is what I am working with:

I am using the "acts_as_commentable_with_threading" plugin that I
forked from elight on github
http://github.com/elight/acts_as_commentable_with_threading/tree/master

the association that is created is as follows

class Submission < ActiveRecord::Base
         has_many :comment_threads, :class_name => "Comment", :as
=> :commentable, :dependent => :destroy, :order => 'created_at ASC'
end

So Submission has many comment_threads.... using this association I
tried to implement eager loading like this:

def show
@submission = Submission.find(params[:id], :include =>
{:comment_threads => :user})
end

I tried it without the hash, and just the comment_threads as well

In the view, I am rendering the comment partials by calling

render :partial => @submission.comment_threads

but it still makes a separate database call for each comment.

Anything obvious I am doing wrong hear? I am getting a bit frustrated
because a simple page with 15 comments loads 2X more slowly than a
submissions page that has images, and many other things going on with
it.

Thanks for the help!

What database calls (ie what is the association that is getting loaded
1 at a time ?)

Fred

Here is the output to the log. You can see at the bottom, that when
the comment partials are rendered, there is a SELECT call to the
database for each comment.

Processing SubmissionsController#show (for 127.0.0.1 at 2009-03-11
12:50:20) [GET]
  Parameters: {"id"=>"4-university-offers-beatles-degree"}
  Submission Columns (2.2ms) SHOW FIELDS FROM `submissions`
  Submission Load (0.9ms) SELECT * FROM `submissions` WHERE
(`submissions`.`id` = 4)
  User Columns (3.2ms) SHOW FIELDS FROM `users`
  User Load (1.2ms) SELECT * FROM `users` WHERE (`users`.`id` = 1)
  Image Load (0.5ms) SELECT `images`.* FROM `images` WHERE
(`images`.submission_id = 4)
  Comment Load (1.5ms) SELECT `comments`.* FROM `comments` WHERE
(`comments`.`commentable_id` = 4 and `comments`.`commentable_type` =
'Submission') ORDER BY created_at ASC
  CACHE (0.0ms) SELECT * FROM `users` WHERE (`users`.`id` = 1)
Rendering template within layouts/application
Rendering submissions/show
Rendered submissions/_submission (3.4ms)
  Comment Load (1.3ms) SELECT * FROM `comments` WHERE
(`comments`.commentable_id = 4 AND `comments`.commentable_type =
'Submission' AND (`comments`.`parent_id` IS NULL)) ORDER BY created_at
ASC
  CACHE (0.0ms) SELECT * FROM `comments` WHERE
(`comments`.commentable_id = 4 AND `comments`.commentable_type =
'Submission' AND (`comments`.`parent_id` IS NULL)) ORDER BY created_at
ASC
  CACHE (0.0ms) SELECT * FROM `users` WHERE (`users`.`id` = 1)
  User Load (0.7ms) SELECT * FROM `users` WHERE (`users`.`id` = 3)
  Comment Load (0.9ms) SELECT * FROM `comments` WHERE
(`comments`.`id` = 7)
Rendered comments/_comment (62.6ms)
  CACHE (0.0ms) SELECT * FROM `users` WHERE (`users`.`id` = 1)
  Comment Load (0.8ms) SELECT * FROM `comments` WHERE
(`comments`.`id` = 8)
Rendered comments/_comment (33.0ms)
  CACHE (0.0ms) SELECT * FROM `users` WHERE (`users`.`id` = 1)
  Comment Load (0.8ms) SELECT * FROM `comments` WHERE
(`comments`.`id` = 9)
Rendered comments/_comment (46.4ms)
  CACHE (0.0ms) SELECT * FROM `users` WHERE (`users`.`id` = 1)
  Comment Load (0.9ms) SELECT * FROM `comments` WHERE
(`comments`.`id` = 10)
Rendered comments/_comment (33.2ms)
  CACHE (0.0ms) SELECT * FROM `users` WHERE (`users`.`id` = 1)
  Comment Load (0.9ms) SELECT * FROM `comments` WHERE
(`comments`.`id` = 11)
Rendered comments/_comment (32.2ms)
  CACHE (0.0ms) SELECT * FROM `users` WHERE (`users`.`id` = 1)
  Comment Load (0.8ms) SELECT * FROM `comments` WHERE
(`comments`.`id` = 12)
Rendered comments/_comment (33.9ms)
  CACHE (0.0ms) SELECT * FROM `users` WHERE (`users`.`id` = 1)
  Comment Load (0.8ms) SELECT * FROM `comments` WHERE
(`comments`.`id` = 13)
Rendered comments/_comment (33.5ms)
  CACHE (0.0ms) SELECT * FROM `users` WHERE (`users`.`id` = 1)
  Comment Load (0.8ms) SELECT * FROM `comments` WHERE
(`comments`.`id` = 14)
  CACHE (0.0ms) SELECT * FROM `comments` WHERE (`comments`.`id` =
14)
Rendered comments/_comment (32.8ms)
  CACHE (0.0ms) SELECT * FROM `users` WHERE (`users`.`id` = 1)
  Comment Load (0.8ms) SELECT * FROM `comments` WHERE
(`comments`.`id` = 17)
Rendered comments/_comment (32.8ms)
Completed in 592ms (View: 371, DB: 19) | 200 OK [http://localhost/
submissions/4-university-offers-beatles-degree]

On Mar 11, 3:44 pm, Frederick Cheung <frederick.che...@gmail.com>
wrote:> On Mar 11, 12:15 pm, cdubd <cdub...@gmail.com> wrote:> render :partial => @submission.comment_threads

> > but it still makes a separate database call for each comment.

> What database calls (ie what is the association that is getting loaded
> 1 at a time ?)

Here is the output to the log. You can see at the bottom, that when
the comment partials are rendered, there is a SELECT call to the
database for each comment.

What's in the _comment partial ?

Fred

What's in the _comment partial ?

get ready.... this is a pretty hairy partial... This was my first shot
at coming up with a javascript driven comment system. Any tips on
things to change would be appreciated.

<%div_for(comment) do %>
  <div id="comment_user"><%= user_for(comment.user_id)%><div
class="time"><%= time_ago(comment.created_at)%></div></div>
  <div id="message"><%= comment.body %></div>
  <div class="cvote_area">

    <%if !logged_in? %>
        <div class="ranked"><span id="up">ranked!</span><span
id="down">ranked!</span></div>
      <%else%>
        <div id="vote_form_<%=comment.id%>" class="cvote_form">
           <% form_remote_for [comment, Vote.new], :html => {:id =>
"new_vote_up"} do |f| %>
             <div id="up">
               <%= f.submit 'up', :id => 'up',:onmouseover
=>"this.className='hov_up'", :onmouseout =>"this.className=''" %>
             </div>
           <% end %>
          <% form_remote_for [comment, Vote.new], :html => {:id =>
"new_vote_down"} do |f| %>
             <div id="down">
               <%= f.submit 'down', :id => 'down',:onmouseover
=>"this.className='hov_down'", :onmouseout =>"this.className=''" %>
             </div>
           <% end %>
            <div class ="clear"></div>
        </div>
    <%end%>

    <div id="vote_score_<%=comment.id%>" class="cvote_score">
      <%= comment.reload.total_votes %>
    </div>

  </div>
  <div class="clear"></div>
  <div id="comment_footer_<%=comment.id%>" class="comment_footer">
    <%= comment_reply_tag(comment)%>
  </div>
  <div id="comment_replies_<%=comment.id%>" style="display:none">
    <div id="reply_form_<%=comment.id%>" class="reply_form" >
      <% form_remote_for [@submission, Comment.new], :html => {:id =>
"reply_field_#{comment.id}"} do |f| %>
        <%= hidden_field_tag "comment_id", "#{comment.id}"%>
        <%= hidden_field_tag "status", "reply"%>
        <%= f.text_area :body%>
        <%= f.submit "Submit"%>
      <% end %>

    </div>
    <div class="clear"></div>
    <div id="reply_left_<%=comment.id%>" >

      <%= link_to_function "reply", :id => "reply_link" do |page|
        page.show("reply_form_#{comment.id}")
      end%>
    </div>
  </div>
<%end %>

> What's in the _comment partial ?

get ready.... this is a pretty hairy partial... This was my first shot
at coming up with a javascript driven comment system. Any tips on
things to change would be appreciated.

<%div_for(comment) do %>
<div id="comment_user"><%= user_for(comment.user_id)%><div

I don't know that user_for does, but that obviously won't use the
comment.user object that has already been loaded.

            &lt;div id=&quot;vote\_score\_&lt;%=comment\.id%&gt;&quot; class=&quot;cvote\_score&quot;&gt;
                    &lt;%= comment\.reload\.total\_votes %&gt;

Well this is why you get lots of queries loading the comment

Fred

> <%div_for(comment) do %>
> <div id="comment_user"><%= user_for(comment.user_id)%><div

I don't know that user_for does, but that obviously won't use the
comment.user object that has already been loaded.

well, the User object has many comments, the comments belong to user.
As far as I can tell, calling comment.user will return nothing because
there is only a user_id field.
perhaps I could set up a Comment has_one :user relationship?

anyway the user_for method just returns the user name that belongs to
that particular user_id.

     &lt;div id=&quot;vote\_score\_&lt;%=comment\.id%&gt;&quot; class=&quot;cvote\_score&quot;&gt;

> <%= comment.reload.total_votes %>

Well this is why you get lots of queries loading the comment

Thanks, I can't remember why that reload was ever put in there, but it
certainly removed all the database calls when I got rid of it.

another quick update. For my submission model, which is a similar
setup to the comment model I have:

user has_many :submissions, submission belongs_to :user, and with this
relationship I am able to call:

submission.user.username

but this doesn't work for comments for some reason.... I cannot call
comment.user.username

I'm not sure I follow. if comment belongs_to user, then comment.user
will return the user with id comment.user_id.

Fred

ActionView::TemplateError (You have a nil object when you didn't
expect it!
You might have expected an instance of Array.
The error occurred while evaluating nil.include?) on line #2 of app/
views/comments/_comment.html.erb:
1: <%div_for(comment) do %>
2: <div id="comment_user"><%= comment.user.username %><div
class="time"><%= time_ago(comment.created_at)%></div></div>
3: <div id="message"><%= comment.body %></div>
4: <div class="cvote_area">

I have tested it in the console and you are right.... I can call
comment.user there. I am getting this error however when I try and do
it the view.

There's probably a comment with no user somewhere.

Fred

I am running edge rails so this could very well be the problem. I was
actually getting a similar error message before when I was writing the
code for the create method on the comments controller. I scratched my
head, but couldn't figure it out, then suddenly it stopped. I will
poke around with it, but the error is a tricky one.

Thanks for all your help fred, I think you are the only person that
responds to my requests on here.

just in case you are interested. I ran the debugger to check things
out, and the comment object does have a valid user_id, but when it
tries to call comment.user.username it tells me it is trying to
evaluate nill.include?

do you think this can be an issue with rails 2.3.1?

very mysterious indeed. I will be quite sad if it turns out to be
something silly.