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! --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Frederick Cheung
2009-Mar-11 14:44 UTC
Re: Eager loading comments associated with user submissions.
On Mar 11, 12:15 pm, cdubd <cdub...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> 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 ?) Fred --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On Mar 11, 3:44 pm, Frederick Cheung <frederick.che...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> On Mar 11, 12:15 pm, cdubd <cdub...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> 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. 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] --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Frederick Cheung
2009-Mar-11 15:08 UTC
Re: Eager loading comments associated with user submissions.
On Mar 11, 3:05 pm, cdubd <cdub...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> On Mar 11, 3:44 pm, Frederick Cheung <frederick.che...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> > wrote:> On Mar 11, 12:15 pm, cdubd <cdub...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> 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> 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]--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
> 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 %> --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Frederick Cheung
2009-Mar-11 16:09 UTC
Re: Eager loading comments associated with user submissions.
On Mar 11, 3:31 pm, cdubd <cdub...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> > 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)%><divI don''t know that user_for does, but that obviously won''t use the comment.user object that has already been loaded.> <div id="vote_score_<%=comment.id%>" class="cvote_score"> > <%= comment.reload.total_votes %>Well this is why you get lots of queries loading the comment Fred --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
> > <%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. <div id="vote_score_<%=comment.id%>" class="cvote_score">> > <%= comment.reload.total_votes %> > > Well this is why you get lots of queries loading the commentThanks, 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. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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 --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Frederick Cheung
2009-Mar-11 16:33 UTC
Re: Eager loading comments associated with user submissions.
On Mar 11, 4:22 pm, cdubd <cdub...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> > > <%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?I''m not sure I follow. if comment belongs_to user, then comment.user will return the user with id comment.user_id. Fred --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Frederick Cheung
2009-Mar-11 18:19 UTC
Re: Eager loading comments associated with user submissions.
On Mar 11, 4:37 pm, cdubd <cdub...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> 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 --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---