Sequel vs Active Record

sequel_vs_ar.rb

require "bundler/inline"

gemfile(true) do
  source "https://rubygems.org"
  gem "mongo"
  gem "sequel"
  gem "activerecord", require: "active_record"
  gem "activerecord-jdbcmysql-adapter", platform: :jruby
  gem "squeel", github: "activerecord-hackery/squeel"
  gem "mysql2", platform: :ruby
  gem "benchmark-ips", require: "benchmark/ips"
  gem "ruby-progressbar"
end

HOST = "localhost"
PORT = "3306"
USER = "root"
PASSWORD = ""
ADAPTER = "mysql2"
DB_NAME = "test"

logger = Logger.new(STDOUT)

if RUBY_PLATFORM == 'java'
  ActiveRecord::Base.establish_connection(adapter: "jdbcmysql", database: DB_NAME, host: HOST,
                                          username: USER, password: PASSWORD)

  DB = Sequel.connect("jdbc:mysql://#{HOST}:#{PORT}/#{DB_NAME}?user=#{USER}&PASSWORD=#{PASSWORD}")
else
  ActiveRecord::Base.establish_connection(adapter: "mysql2", database: DB_NAME, host: HOST,
                                          username: USER, password: PASSWORD)
  DB = Sequel.connect("mysql2://#{HOST}:#{PORT}/#{DB_NAME}?user=#{USER}&PASSWORD=#{PASSWORD}")
end

DB.drop_table?(:power_assignments)
DB.drop_table?(:powers)
DB.drop_table?(:users)

DB.create_table :powers do
  primary_key :id
  String :name
end

DB.create_table :users do
  primary_key :id
end

DB.create_table :power_assignments do
  primary_key :id
  datetime :valid_from
  datetime :valid_to
  foreign_key :user_id, :users
  foreign_key :power_id, :powers
end

POWERS = %w(
Nothingness Manipulation
Digital Form
Telekinetic Invulnerability
Nerve Manipulation
Temporal Duplication
Transmutation
Rubber Skin
Oxygen Generation
Dowsing
Freeze Breath
Miasma Breathing
Density Control
Cross-Dimensional Awareness
Control Sand
Rebirth
Sense People
Disintegrate Objects
Control Wind
Control The Dead
Enhanced Camouflage
)
POWERS.each do |name|
  DB[:powers].insert(name: name)
end

pb = ProgressBar.create(title: "Users insertions", starting_at: 0, total: 10000)
DB.transaction do
  10000.times do |i|
    pb.increment
    DB[:users].insert
  end
end

pb = ProgressBar.create(title: "Power assigment insertions", starting_at: 0, total: 40000)
DB.transaction do
  40000.times do |i|
    pb.increment
    DB[:power_assignments].insert(user_id: i / 4 + 1, power_id: rand(POWERS.size) + 1, valid_from: Time.now - 1000, valid_to: Time.now + 1000)
  end
end

class Power < ::ActiveRecord::Base
  has_many :power_assignments
end
class PowerAssignment < ::ActiveRecord::Base
  has_one :power
  has_one :user
end

GC.start
GC.disable

Benchmark.ips do |x|
  x.warmup = 20
  x.report("active record + squeel") do
    power_ids = PowerAssignment.where(user_id: rand(10000) + 1).where do
      (valid_to == nil) | (valid_from <= Time.now.utc) & (valid_to > Time.now.utc)
    end
    Power.where(id: power_ids).pluck(:name)
  end
  x.report("sequel") do
    DB[:powers]
      .join(:power_assignments, power_id: :id)
      .where(user_id: rand(10000) + 1)
      .where{ (valid_to =~ nil) | (valid_from <= Sequel.lit("NOW()")) & (valid_to >= Sequel.lit("NOW()")) }
      .select_map(:name)
  end
  x.report("active record + join + sql") do
    Power
      .joins(:power_assignments)
      .where("user_id = ?
              AND (valid_to IS NULL OR
                   valid_from <= NOW() AND valid_to >= NOW())",
             rand(10000) + 1
            )
      .pluck(:name)
  end
  x.compare!
end
__END__
Ruby 2.3.0:
Warming up --------------------------------------
active record + squeel
                        86.000  i/100ms
              sequel   230.000  i/100ms
active record + join + sql
                       149.000  i/100ms
Calculating -------------------------------------
active record + squeel
                        726.580  (± 8.7%) i/s -      3.612k in   5.020338s
              sequel      2.338k (±10.2%) i/s -     11.730k in   5.078747s
active record + join + sql
                          1.647k (± 8.4%) i/s -      8.195k in   5.021578s

Comparison:
              sequel:     2337.9 i/s
active record + join + sql:     1647.2 i/s - 1.42x slower
active record + squeel:      726.6 i/s - 3.22x slower

JRuby 9.1.0.0:

Warming up --------------------------------------
active record + squeel
                        18.000  i/100ms
              sequel   146.000  i/100ms
active record + join + sql
                        89.000  i/100ms
Calculating -------------------------------------
active record + squeel
                        309.575  (±13.6%) i/s -      1.512k in   4.998164s
              sequel      2.234k (± 9.1%) i/s -     11.096k in   5.022735s
active record + join + sql
                          1.151k (± 5.8%) i/s -      5.785k in   5.047078s

Comparison:
              sequel:     2234.0 i/s
active record + join + sql:     1150.5 i/s - 1.94x slower
active record + squeel:      309.6 i/s - 7.22x slower