Wednesday, 11 January 2012

Don't loop around active record !!!

Hello everyone, it's been a long time since I have posted here.

Today, I am going to show you a way to optimize the performance of your active record queries.

Lets say we have model definitions like this:

class User < ActiveRecord::Base
  has_many :blogs
end

class Blog < ActiveRecord::Base
  has_many :comments
  belongs_to :user
end

class Comment < ActiveRecord::Base
  belongs_to :blog
end

Now,let's say we want to display the content of the comments  in the view regarding a particular user.

WRONG WAY

@user = User.where("email like ?","example@foobar.com")

@user.first.blogs.each do |blog|
   blog.comments.each do |comments|
       puts comments.content
   end
end

The Rails logfile will look something like this:

 User Load (1.1ms)  SELECT `users`.* FROM `users` WHERE (email like 'example@foobar.com') LIMIT 1
  Blog Load (0.7ms)  SELECT `blogs`.* FROM `blogs` WHERE `blogs`.`user_id` = 2
  Comment Load (0.5ms)  SELECT `comments`.* FROM `comments` WHERE `comments`.`blog_id` = 3
  Comment Load (0.7ms)  SELECT `comments`.* FROM `comments` WHERE `comments`.`blog_id` = 4
  Comment Load (0.7ms)  SELECT `comments`.* FROM `comments` WHERE `comments`.`blog_id` = 8
  Comment Load (0.6ms)  SELECT `comments`.* FROM `comments` WHERE `comments`.`blog_id` = 9
  Comment Load (41.4ms)  SELECT `comments`.* FROM `comments` WHERE `comments`.`blog_id` = 11
  Comment Load (0.8ms)  SELECT `comments`.* FROM `comments` WHERE `comments`.`blog_id` = 12

What happened? We started with a single user object, then issued a separate SQL command to retrieve the blogs for that user.
Additional SQL commands were required to retrieve the comments in each of those blogs.
In this case there were only a few blogs, but 6 queries were generated. A user with more blogs, more comments, or further nesting could easily result in dozens of SQL queries and a very slow
response.

RIGHT WAY

Fortunately, Rails 3.1 provides us with an easy way to deal with this problem as Active Record lets you specify in advance all the associations that are going to be loaded. This is possible by specifying the includes method of the Model.where / Model.find calls. With includes, Active Record ensures that all of the specified associations are loaded using the minimum possible number of queries.
Here's the way to do this:

@user =User.where("email like ?","example@foobar.com").includes(:blogs => :comments)

Looking into the log again and we see these 3 simple queries:

User Load (1.0ms)  SELECT `users`.* FROM `users` WHERE (email like 'example@foobar.com')
Blog Load (0.4ms)  SELECT `blogs`.* FROM `blogs` WHERE `blogs`.`user_id` IN (2)

Comment Load (0.5ms)  SELECT `comments`.* FROM `comments` WHERE `comments`.`blog_id` IN (3, 4, 8, 9, 11, 12)


And when we run the above loop again:


@user.first.blogs.each do |blog|
   blog.comments.each do |comments|
       puts comments.content
   end
end

In log what do we see??
NOTHING !!

Yes, it's really possible to minimize the database load to a huge extent.

This wraps up my post, hope it will help you write better code.














1 comment: