Rails recursive entity performance issue

Question

I have a simple rails app that displays a single page where one can post comments and reply to those comments and on and on.

The model for comments is quite simple:

class Comment < ActiveRecord::Base
  attr_accessible :text, :parent_id
  attr_accessor :level

  has_many :replies, :class_name => 'Comment', :foreign_key => 'parent_id'
  belongs_to :parent, :class_name => 'Comment'

  scope :get_replies, where(parent_id: to_param})

end

And, the controller, will pass to view only the root level comments:

def index
  @root_comments = Comment.where('parent_id IS NULL')
end

Finally the view will fetch the reply comments of the root comments and render everything:

<% @root_comments.each{ |c| c.level = 0} %>

<% while @root_comments.size > 0 %>
  <% comment = @root_comments[0] %>
  <% @root_comments.delete_at(0) %>
  <% replies = comment.get_replies %>
  <% replies.each{ |r| r.level = comment.level + 1} %>
  <% @root_comments = replies + @root_comments %>

  <div class="comment" style=<%= "margin-left:#{(comment.level * 50)}px;" %> >
    <%= comment.text %>
  </div>

<% end %>

So far so good... Until check the rails server output and...

  Comment Load (0.3ms)  SELECT "comments".* FROM "comments" WHERE (parent_id IS NULL)
  Comment Load (0.3ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_id" = 1
  Comment Load (0.3ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_id" = 4
  ...
  Comment Load (0.3ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_id" = 16
  Comment Load (0.3ms)  SELECT "comments".* FROM "comments" WHERE "comments"."parent_id" = 17

SQL queries everywhere...

I haven't found anything about a inbuilt rails server optimization to manage this kind of approach so.

1) Anyone knows if such optimization exists?

2) If it does not exist how can I fix this issue?

I have tried to eager loading the contacts in the controller but the server output shows the same number of queries.

@root_comments = Comment.includes(:replies).where('parent_id IS NULL')

Thanks!

score 2 · Answer 1 · answered Jan 03 '13 at 18:28

2

One thing you can do is store the root comment with each comment, so you could query all comments with two queries:
* where('parent_id IS NULL') # extract the ids of these into root_ids
* where('root_id IN ?', root_ids)

The downside is that you'll have to reconstruct the tree in code.

answered Jan 03 '13 at 18:28

zoli

469
6
17

that would work with two levels, right? I can have more than two levels. – Helio Santos Jan 03 '13 at 18:35
No, you would have to add the root ids to grandchildren, grand-grand-children, etc. – zoli Jan 03 '13 at 18:45
I see, it's a nice idea. But I rather keep my model clean. – Helio Santos Jan 03 '13 at 18:49
The answers [here](http://stackoverflow.com/questions/3362669/what-is-the-best-way-to-store-a-tree-structure-in-a-relational-db) have a few other options, but all of them would involve storing some extra information about the comment hierarchy in the Comment model. – zoli Jan 04 '13 at 16:20

score 1 · Answer 2 · answered Jan 03 '13 at 19:16

1

you should check out rails eager loading . Try

 def index
  @root_comments = Comment.includes(:comment).where('parent_id IS NULL')
 end

answered Jan 03 '13 at 19:16

Paritosh Piplewar

7,982
5
26
41

score 0 · Answer 3 · answered Jan 03 '13 at 01:32

0

I'm not sure if it will help your performance, but you may want to look at the acts_as_tree gem:

https://github.com/amerine/acts_as_tree

At the very least, you can likely avoid reinventing the wheel.

answered Jan 03 '13 at 01:32

Dave S.

6,349
31
33

Does acts_as_tree support several roots? – Helio Santos Jan 03 '13 at 01:36
I believe so - there is a class-level 'roots' method, which returns records with a null parent_id. – Dave S. Jan 03 '13 at 01:38
I've been browsing the source code and I believe the problem persists. One still have to do the querying. – Helio Santos Jan 03 '13 at 01:51

Rails recursive entity performance issue

3 Answers3