Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
#!/usr/bin/env ruby
# encoding: utf-8
# ======================================================
# <N+1 problem>
#
# Post.all.each do |p|
# puts p.category.name
# end
#
# select * from POSTS;
#
# select * from CATEGORYS where POST_ID=1;
# select * from CATEGORYS where POST_ID=2;
# select * from CATEGORYS where POST_ID=3;
# select * from CATEGORYS where POST_ID=4;
#
# --- solution ---
#
# Post.all.includes(:category).each do |p|
# puts p.category.name
# end
#
# select * from POSTS;
#
# select CATEGORY.* from CATEGORY
# where CATEGORY.POST_ID IN (1,2,3,4)
#
# ======================================================
require 'rubygems'
require 'faker'
require 'active_record'
require 'benchmark'
# This call creates a connection to our database.
ActiveRecord::Base.establish_connection(
:adapter => "mysql",
:host => "127.0.0.1",
:username => "wendi", # Note that while this is the default setting for MySQL,
:password => "wendi", # a properly secured system will have a different MySQL
# username and password, and if so, you'll need to
# change these settings.
:database => "test")
# First, set up our database...
class Category < ActiveRecord::Base
end
unless Category.table_exists?
ActiveRecord::Schema.define do
create_table :categories do |t|
t.column :name, :string
end
end
end
Category.create(:name=>'Sara Campbell\'s Stuff')
Category.create(:name=>'Jake Moran\'s Possessions')
Category.create(:name=>'Josh\'s Items')
number_of_categories = Category.count
class Item < ActiveRecord::Base
belongs_to :category
end
# If the table doesn't exist, we'll create it.
unless Item.table_exists?
ActiveRecord::Schema.define do
create_table :items do |t|
t.column :name, :string
t.column :category_id, :integer
end
end
end
puts "Loading data..."
item_count = Item.count
item_table_size = 10000
if item_count < item_table_size
(item_table_size - item_count).times do
Item.create!(:name=>Faker.name,
:category_id=>(1+rand(number_of_categories.to_i)))
end
end
puts "Running tests..."
Benchmark.bm do |x|
[100,1000,10000].each do |size|
x.report "size:#{size}, with n+1 problem" do
@items=Item.all.limit(size)
@items.each do |i|
i.category
end
end
x.report "size:#{size}, with :include" do
@items=Item.all.includes(:category).limit(size)
@items.each do |i|
i.category
end
end
end
end
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.