Dr. Dobb's Journal - December 2008 - (Page 26)

d12weis_p4db 10/10/08 9:30 AM Page 26 Core Technology PERFORMANCE ON RAILS continued from page 24 def show @post = Post.find params[:id], :include => { :comments => :user } end Post Load (0.000537) SELECT * FROM "posts" WHERE ("posts"."id"=1) Comment Load (0.001683) SELECT "comments".* FROM "comments" WHERE ("comments".post_id IN (1)) User Load (0.001375) SELECT * FROM "users" WHERE ("users".id IN ('6','1','2','3','4','5')) Either approach is a big improvement over the original n+1 problem. Solution 2: Eager Fetching with JOIN There are times that the :include option alone isn’t sufficient. What happens if a post is wildly popular and has comments by hundreds, even thousands, of users? The IN clause in the users query will have a lot of IDs, and performance will begin to suffer— the query may even fail if the list of IDs is too long. The solution is to fetch even more eagerly using a JOIN in your query. Instead of writing a query for find_by_sql, encourage Active Record to use a JOIN for you: @post.comments.all :include => :user, :conditions => 'users.id is not null' In versions of Rails prior to 2.1, this statement would fetch the post, its comments, and the users with a single complicated query composed of SQL JOINs. The current version (2.1 at this writing) does not use JOINs, instead favoring one query per model that you specify in your :include parameter: In this example, one query fetches the post, a second query fetches the comments for that post, and a third query fetches the users that wrote the comments. This approach makes for a couple of extra queries, but JOINs can be expensive and become a bottleneck in their own right. When you include a filter on users.id in the :conditions option, Active Record smartly fetches the users with a JOIN (specifically a LEFT OUTER JOIN) to satisfy the dependency you’ve introduced in the WHERE clause on the users table. It’s probably a good idea to comment code like this to document the intent of the :conditions option. Solution 3: has_many :through Posts have a transitive dependency on users: A post has comments, and each comment has a user; therefore, a post has commenters. Use Active Record’s has_many :through to declare this dependency: # post model has_many :commenters, :through => : comments, :source => :user Now each post provides a #commenters method, which returns the list of users who have commented on the post by executing a SQL query like this: User Load (0.001009) SELECT "users".* FROM "users" INNER JOIN comments ON users.id = comments.user_id WHERE (("comments".post_id = 1)) To get the user from that list for a particular comment, we could use some simple Ruby in the view: # this is not optimal! @post.commenters.detect { |u| u.id == comment.user_id } 26 Dr. Dobb’s Journal l www.ddj.com l December 2008 http://www.programmingresearch.com http://www.programmingresearch.com http://www.ddj.com

Table of Contents Feed for the Digital Edition of Dr. Dobb's Journal - December 2008

Dr. Dobb's Journal - December 2008 Contents Friday Night Fish Fry Alia Vox Developer Diaries Conversations The Man Who Sold the Sky Performance on Rails LINQ-to-SQL and T-SQL A Remote Java RMI Registry Beyond B-Trees File Descriptors and Multithreaded Programs Effective Concurrency The Agile Edge Swaine's Flames

Dr. Dobb's Journal - December 2008

For optimal viewing of this digital publication, please enable JavaScript and then refresh the page. If you would like to try to load the digital publication without using Flash Player detection, please click here.