Plugin to kill N+1 queries

Rails
1

Hi guys,

I wrote a plugin to detect the N+1 queries, it works very well and
maybe helpful to you. The site of the plugin is
GitHub - flyerhzm/bullet: help to kill N+1 queries and unused eager loading.
The README is good to explain how to use the plugin and the spec/
bullet_association_spec.rb show you what are N+1 queries and what are
not.

I will still give you an quick example to show how to use the plugin
step by step:

  1. setup test environment

$ rails test
$ cd test
$ script/generate scaffold post name:string
$ script/generate scaffold comment name:string post_id:integer
$ rake db:migrate

  1. change app/model/post.rb and app/model/comment.rb

class Post < ActiveRecord::Base
has_many :comments
end

class Comment < ActiveRecord::Base
belongs_to :post
end

  1. go to script/console and execute

post1 = Post.create(:name => ‘first’)
post2 = Post.create(:name => ‘second’)
post1.comments.create(:name => ‘first’)
post1.comments.create(:name => ‘second’)
post2.comments.create(:name => ‘third’)
post2.comments.create(:name => ‘fourth’)

  1. change the app/views/posts/index.html.erb to generate a N+1 query

Listing posts

<% @posts.each do |post| %>

<% end %>
Name
<%=h post.name %> <%= post.comments.collect(&:name) %> <%= link_to 'Show', post %> <%= link_to 'Edit', edit_post_path(post) %> <%= link_to 'Destroy', post, :confirm => 'Are you sure?', :method => :delete %>

<%= link_to ‘New post’, new_post_path %>

  1. add bullet plugin

$ script/plugin install git://github.com/flyerhzm/bullet.git

  1. enable the bullet plugin in development, add a line to config/
    environments/development.rb

Bullet.enable = true

  1. start server

$ script/server

  1. input http://localhost:3000/posts in browser, then you will see a
    popup alert box says

The request has N+1 queries as follows:
model: Post => associations: [comment]

which means there is a N+1 query from post object to comments
associations.

In the meanwhile, there’s a log appended into log/bullet.log file

2009-08-17 15:07:31[INFO] N+1 Query: PATH_INFO: /posts; model: Post
=> assocations: [comments]

The generated SQLs are

Post Load (1.0ms) SELECT * FROM “posts”
Comment Load (0.4ms) SELECT * FROM “comments” WHERE
(“comments”.post_id = 1)
Comment Load (0.3ms) SELECT * FROM “comments” WHERE
(“comments”.post_id = 2)

  1. fix the N+1 query, change app/controllers/posts_controller.rb file

def index
@posts = Post.find(:all, :include => :comments)

respond_to do |format|
  format.html # index.html.erb
  format.xml  { render :xml => @posts }
end

end

  1. refresh http://localhost:3000/posts page, no alert box and no log
    appended.

The generated SQLs are

Post Load (0.5ms) SELECT * FROM “posts”
Comment Load (0.5ms) SELECT “comments”.* FROM “comments” WHERE
(“comments”.post_id IN (1,2))

a N+1 query fixed. Cool!

Hope you like the plugin!

Thanks,
Richard