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
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')
@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.
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:
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.
