Plugin to kill N+1 queries

Hi guys,

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!

这个插件很有用，支持一下lz

Agile.Web.Development.with.Rails.

一书中就有介绍。算新手贴！

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

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

n+1没有什么性能问题，include造成的join查询才是性能杀手，不要误导大家。

@maojieming 这个插件是帮你找出项目中应该使用:include而没有使用的地方，以及无用的:include，这样你才可以改正来提高应用性能

@robbin 从Rails2.1开始，:include已经不再用join来查询了，而是通过两条SQL语句来读取的
SELECT * FROM "posts"
SELECT "comments".* FROM "comments" WHERE ("comments".post_id IN (1,2,3))
这个肯定比N+1查询性能好吧
SELECT * FROM "posts"
SELECT "comments".* FROM "comments" WHERE ("comments".post_id = 1)
SELECT "comments".* FROM "comments" WHERE ("comments".post_id = 2)
SELECT "comments".* FROM "comments" WHERE ("comments".post_id = 3)

另外说一下，最新版的flyerhzm-bullet还可以建议你哪里应该使用counter_cache的地方

不过实际上确实不会这么去用

因为所有都在缓存里面

先取得posts,当然某些posts也应该是缓存着的，取某一条post的时候的评论时候也是首先看看对应评论是否缓存

如果没有缓存，是上面的性能更好。如果有缓存的话，下面的性能更好。不过一般情况下，我也觉得你的办法更好，毕竟不是每个项目都能很好的用缓存的。

这个插件确实不错。很有实用价值，尤其对新手来说。
支持良好了。

bullet plugin/gem升级到1.7.0，现在已经可以同时支持rails 2.1, 2.2和2.3，修复了不少错误，增加了更多的测试，更少的误报。

项目地址：http://github.com/flyerhzm/bullet
gem已经迁移到gemcutter：http://gemcutter.org/gems/bullet

我的感觉是，如果N+1成了明显的问题，那IN查询同样成问题，因为你整个SQL会很长，解析就要花时间
所以到最后还是缓存和手写SQL…