诗歌rails之 Plugin to kill N+1 queries
I wrote a plugin to kill the N+1 queries, it works very well and maybe helpful to you. The site of the plugin is 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
2. 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
3. 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')
4. change the app/views/posts/index.html.erb to generate a N+1 query
- <h1>Listing posts</h1>
- <table>
- <tr>
- <th>Name</th>
- </tr>
- <% @posts.each do |post| %>
- <tr>
- <td><%=h post.name %></td>
- <td><%= post.comments.collect(&:name) %></td>
- <td><%= link_to 'Show', post %></td>
- <td><%= link_to 'Edit', edit_post_path(post) %></td>
- <td><%= link_to 'Destroy', post, :confirm => 'Are you sure?', :method => :delete %></td>
- </tr>
- <% end %>
- </table>
- <br />
- <%= link_to 'New post', new_post_path %>
5. add bullet plugin
- $ script/plugin install git://github.com/flyerhzm/bullet.git
6. enable the bullet plugin in development, add a line to config/environments/development.rb
- Bullet.enable = true
7. start server
- $ script/server
8. 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)
9. 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
10. 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!
莫愁前路无知己,天下无人不识君。