# Plugin to kill N+1 queries

**URL:** <https://www.ruby-forum.com/t/plugin-to-kill-n-1-queries/173871>\
**Category:** Rails\
**Created:** [August 17, 2009, 3:27pm UTC](https://www.ruby-forum.com/t/plugin-to-kill-n-1-queries/173871 "2009-08-17T15:27:12Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![flyerhzm](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/d26b3c/32.png) [@flyerhzm](https://www.ruby-forum.com/u/flyerhzm)\
**Post date:** [August 17, 2009, 3:27pm UTC](https://www.ruby-forum.com/t/plugin-to-kill-n-1-queries/173871/1 "2009-08-17T15:27:12Z")

</div>

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](http://github.com/flyerhzm/bullet).  
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](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](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