where_exists_benchmark.rb

# Drop this file to activerecord/test
# Set adapter in 'config.yml' (e.g. "adapter: mysql2")
# bundle exec ruby -Itest test/where_exists_benchmark.rb

require 'cases/helper'
require 'benchmark/ips'

RECORDS = 1000
COHERENCE = 35

ActiveRecord::Schema.define do
  create_table :companies, force: true do |t|
    t.string :name
  end
  create_table :developers, force: true do |t|
    t.string :name
    t.integer :group_id
    t.index :group_id
  end
  create_table :groups, force: true do |t|
    t.string :name
  end
  create_table :contract, force: true do |t|
    t.integer :developer_id, null: false
    t.integer :company_id, null: false
    t.index :developer_id
    t.index :company_id
  end
end

class Company < ActiveRecord::Base
  has_many :contracts
  has_many :developers, through: :contracts
end

class Developer < ActiveRecord::Base
  belongs_to :group

  has_many :contracts
  has_many :companies, through: :contracts
end

class Group < ActiveRecord::Base
  has_many :developers
end

class Contract < ActiveRecord::Base
  belongs_to :company
  belongs_to :developer
end

RECORDS.times do |i|
  company = Company.create!(name: "Company #{i}")
  group = Group.create!(name: "Group #{i}")
  developer = Developer.create!(name: "Developer #{i}", group: group)
end

COHERENCE.times do |i|
  COHERENCE.times do |j|
    Contract.create!(
      developer_id: rand(Developer.last.id - Developer.first.id) + Developer.first.id,
      company_id: rand(Company.last.id - Company.first.id) + Company.first.id
    )
  end
end

RECORDS.times do |i|
  Developer.create!(name: "D #{i}")
  Company.create!(name: "C #{i}")
  Group.create!(name: "G #{i}")
end

# puts Developer.joins(contracts: :company).where.not("companies.id" => nil).explain
# puts; puts
# puts Developer.where_exists(:companies).explain

Benchmark.ips do |x|
  x.report("Exists on Through: Multiple queries, storing IDs in Ruby Array") do
    ActiveRecord::Base.uncached do
      Developer.where(id: Contract.where(company_id: Company.pluck(:id)).pluck(:developer_id)).pluck(:id)
    end
  end

  x.report("Exists on Through: Using joins") do
    ActiveRecord::Base.uncached do
      Developer.joins(contracts: :company).where.not("companies.id" => nil).pluck(:id)
    end
  end

  x.report("Exists on Through: Using where_exists") do
    ActiveRecord::Base.uncached do
      Developer.where_exists(:companies).pluck(:id)
    end
  end

  x.report("Not Exists on Through: Multiple queries, storing IDs in Ruby Array") do
    ActiveRecord::Base.uncached do
      Developer.where.not(id: Contract.where(company_id: Company.pluck(:id)).pluck(:developer_id)).pluck(:id)
    end
  end

  x.report("Not Exists on Through: Using joins") do
    ActiveRecord::Base.uncached do
      Developer.
         joins("LEFT JOIN contracts ON contracts.developer_id = developers.id").
         joins("LEFT JOIN companies ON companies.id = contracts.company_id").
         where("companies.id" => nil).pluck(:id)
    end
  end

  x.report("Not Exists on Through: Using where_not_exists") do
    ActiveRecord::Base.uncached do
      Developer.where_not_exists(:companies).pluck(:id)
    end
  end

  x.compare!
end

Benchmark.ips do |x|
  x.report("Exists: Multiple queries, storing IDs in Ruby Array") do
    ActiveRecord::Base.uncached do
      Group.where(id: Developer.pluck(:group_id)).pluck(:id)
    end
  end

  x.report("Exists: Using joins") do
    ActiveRecord::Base.uncached do
      Group.joins(:developers).where.not("developers.id" => nil).pluck(:id)
    end
  end

  x.report("Exists: Using where_exists") do
    ActiveRecord::Base.uncached do
      Group.where_exists(:developers).pluck(:id)
    end
  end

  x.report("Not Exists: Multiple queries, storing IDs in Ruby Array") do
    ActiveRecord::Base.uncached do
      Group.where.not(id: Developer.pluck(:group_id)).pluck(:id)
    end
  end

  x.report("Not Exists: Using joins") do
    ActiveRecord::Base.uncached do
      Group.
         joins("LEFT JOIN developers ON developers.group_id = groups.id").
         where("developers.id" => nil).pluck(:id)
    end
  end

  x.report("Not Exists: Using where_not_exists") do
    ActiveRecord::Base.uncached do
      Group.where_not_exists(:developers).pluck(:id)
    end
  end

  x.compare!
end