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:
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.
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.
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.
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.
> <%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.
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.
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.