A benchmarking script for ActiveRecord with the SQLite3 adapter

activerecord-benchmark.rb

require 'benchmark'
require 'active_record'
require 'pg'

# -----------------------------------------------------------------------------

INPUT_COLUMNS = {
  name:       "Name of benchmark",
  iters:      "Number of iterations the block is run",
  usr_time:   "Amount of user CPU time",
  sys_time:   "Amount of system CPU time",
  ttl_time:   "Amount of total CPU time",
  clk_time:   "Amount of actual/real/wallclock time",
  txn:        "Whether a transaction was used",
}
PRAGMAS = %w[
  analysis_limit
  application_id
  auto_vacuum
  automatic_index
  busy_timeout
  cache_size
  cache_spill
  case_sensitive_like
  cell_size_check
  checkpoint_fullfsync
  data_version
  defer_foreign_keys
  encoding
  foreign_keys
  freelist_count
  fullfsync
  hard_heap_limit
  ignore_check_constraints
  integrity_check
  journal_mode
  journal_size_limit
  legacy_alter_table
  locking_mode
  max_page_count
  mmap_size
  page_count
  page_size
  query_only
  quick_check
  read_uncommitted
  recursive_triggers
  reverse_unordered_selects
  secure_delete
  soft_heap_limit
  synchronous
  temp_store
  threads
  trusted_schema
  user_version
  wal_autocheckpoint
]

Result = Struct.new(*INPUT_COLUMNS.keys)

module Workbench
  def measure(iterations, in_transaction = false, &block)
    GC.start
    
    label = caller_locations(1,1)[0].label.gsub("bench_", "")
    measurement = Benchmark.measure(nil) do
      if in_transaction
        ActiveRecord::Base.transaction(&block)
      else
        block.call
      end
    end
    output = measurement
              .format('%u,%y,%t,%r')
              .gsub(/[()]/, '')
              .split(',')
              .map { |s| s.strip.to_f.round(4) }
    
    @results << Result.new(*[label, iterations, *output, in_transaction])
  end
end

module Runner
  def run!(log = true)
    instance = new
    instance.instance_variable_set(:@results, [])
    instance_methods(_include_ancestors = false)
      .select { |m| m.to_s.start_with?("bench_") }
      .each do |m|
        instance.method(m).call
        TestRecord
          .descendants
          .each { |c| c.delete_all }
      end
    results = instance.instance_variable_get(:@results)
      .sort_by { |result| result.clk_time }
      .reverse
    
    if log
      $stdout << results
        .map do |result|
          sub = [
            result.txn.to_s.ljust(6, ' '),
            result.iters.to_s.ljust(5, ' '),
            result.usr_time.to_s.ljust(7, ' '),
            result.sys_time.to_s.ljust(7, ' '),
            result.ttl_time.to_s.ljust(7, ' '),
          ].join(" ")
          [
            result.name.ljust(60, ' '),
            result.clk_time.to_s.ljust(7, ' '),
            "(#{sub})"
          ].join(" ")
        end
        .join("\n")
      $stdout << "\n"
    end
    
    results
  end
end

# Based off of: https://github.com/jeremyevans/simple_orm_benchmark
class BenchmarkTest
  include Workbench
  extend Runner
  BASELINE = 64
  
  def bench_model_object_creation
    measure(BASELINE*50) do
      Party.create(theme: "Halloween")
    end
    
    measure(BASELINE*50, in_transaction = true) do
      Party.create(theme: "Halloween")
    end
  end
  
  def bench_model_object_select
    Party.insert_all((0..BASELINE*100).map { |n| {theme: "Theme: #{n}"} })
    
    measure(BASELINE) do
      Party.all
    end
    
    measure(BASELINE, in_transaction = true) do
      Party.all
    end
  end
  
  def bench_model_object_select_by_pk
    Party.insert_all((0..BASELINE*10).map { |n| {theme: "Theme: #{n}"} })
    party_ids = Party.pluck(:id)
    
    measure(BASELINE) do
      party_ids.each do |party_id|
        Party.find(party_id)
      end
    end
    
    measure(BASELINE, in_transaction = true) do
      party_ids.each do |party_id|
        Party.find(party_id)
      end
    end
  end
  
  def bench_model_object_select_by_attr
    Party.insert_all((0..BASELINE*10).map { |n| {theme: "Theme: #{n}"} })
    party_themes = Party.pluck(:theme)
    
    measure(BASELINE) do
      party_themes.each do |party_theme|
        Party.find_by(theme: party_theme)
      end
    end
    
    measure(BASELINE, in_transaction = true) do
      party_themes.each do |party_theme|
        Party.find_by(theme: party_theme)
      end
    end
  end
  
  def bench_model_object_select_and_save
    Party.insert_all((0..BASELINE*50).map { |n| {theme: "Theme: #{n}"} })
    
    measure(BASELINE) do
      Party.all.each do |party|
        party.theme += '.1'
        party.save
      end
    end
    
    measure(BASELINE, in_transaction = true) do
      Party.all.each do |party|
        party.theme += '.1'
        party.save
      end
    end
  end
  
  def bench_model_object_destruction
    Party.insert_all((0..BASELINE*100).map { |n| {theme: "Theme: #{n}"} })
    
    measure(BASELINE) do
      Party.all.each do |party|
        party.destroy
      end
    end
    
    measure(BASELINE, in_transaction = true) do
      Party.all.each do |party|
        party.destroy
      end
    end
  end
  
  def bench_model_object_and_associated_object_creation
    measure(BASELINE*20) do
      party = Party.create(theme: "X-mas")
      Person.create(party: party, name: "Test_#{party.id}")
    end
    
    measure(BASELINE*20, in_transaction = true) do
      party = Party.create(theme: "X-mas")
      Person.create(party: party, name: "Test_#{party.id}")
    end
  end
  
  def bench_model_object_and_associated_object_creation
    Party.insert_all((0..BASELINE*25).map { |n| {theme: "Theme: #{n}"} })
    party_ids = Party.pluck(:id)
    Person.insert_all(party_ids.map { |party_id| {name: "Party: #{party_id}", party_id: party_id} })
    
    measure(BASELINE) do
      Party.all.each do |party|
        party.people.each { |person| person.destroy }
        party.destroy
      end
    end
    
    measure(BASELINE, in_transaction = true) do
      Party.all.each do |party|
        party.people.each { |person| person.destroy }
        party.destroy
      end
    end
  end
  
  def bench_eager_loading_query_per_association_with_1_to_1_records
    (BASELINE*20).times do |n|
      party = Party.create(theme: "Theme: #{n}")
      Person.insert({name: "Name: #{n}", party_id: party.id})
    end
    
    measure(BASELINE*5/7) do
      Party.preload(:people).to_a.each do |party|
        party.people.map { |p| p.id }
      end
    end
    
    measure(BASELINE*5/7, in_transaction = true) do
      Party.preload(:people).to_a.each do |party|
        party.people.map { |p| p.id }
      end
    end
  end
  
  def bench_eager_loading_single_query_with_1_to_1_records
    (BASELINE*20).times do |n|
      party = Party.create(theme: "Theme: #{n}")
      Person.insert({name: "Name: #{n}", party_id: party.id})
    end
    
    measure(BASELINE*5/7) do
      Party.eager_load(:people).to_a.each do |party|
        party.people.each{|p| p.id}
      end
    end
    
    measure(BASELINE*5/7, in_transaction = true) do
      Party.eager_load(:people).to_a.each do |party|
        party.people.each{|p| p.id}
      end
    end
  end
  
  def bench_eager_loading_query_per_association_with_1_to_n_records
    BASELINE.times do |n|
      party = Party.create(theme: "Theme: #{n}")
      BASELINE.times do |nn|
        Person.insert({name: "Name: #{nn}", party_id: party.id})
      end
    end
    
    measure(BASELINE*5/7) do
      Party.preload(:people).to_a.each do |party|
        party.people.map { |p| p.id }
      end
    end
    
    measure(BASELINE*5/7, in_transaction = true) do
      Party.preload(:people).to_a.each do |party|
        party.people.map { |p| p.id }
      end
    end
  end
  
  def bench_eager_loading_single_query_with_1_to_n_records
    BASELINE.times do |n|
      party = Party.create(theme: "Theme: #{n}")
      BASELINE.times do |nn|
        Person.insert({name: "Name: #{nn}", party_id: party.id})
      end
    end
    
    measure(BASELINE*5/7) do
      Party.eager_load(:people).to_a.each do |party|
        party.people.map { |p| p.id }
      end
    end
    
    measure(BASELINE*5/7, in_transaction = true) do
      Party.eager_load(:people).to_a.each do |party|
        party.people.map { |p| p.id }
      end
    end
  end
  
  def bench_eager_loading_query_per_association_with_1_to_n_to_n_records
    BASELINE.times do |n|
      party = Party.create(theme: "Theme: #{n}")
      BASELINE.times do |nn|
        Person.insert({name: "Name: #{nn}", party_id: party.id})
        Person.insert({name: "Name: #{nn}", other_party_id: party.id})
      end
    end
    
    measure(BASELINE*2/7) do
      Party.preload(:people, :other_people).to_a.each do |party|
        party.people.map { |p| p.id }
        party.other_people.map { |p| p.id }
      end
    end
    
    measure(BASELINE*2/7, in_transaction = true) do
      Party.preload(:people, :other_people).to_a.each do |party|
        party.people.map { |p| p.id }
        party.other_people.map { |p| p.id }
      end
    end
  end
  
  def bench_eager_loading_single_query_with_1_to_n_to_n_records
    BASELINE.times do |n|
      party = Party.create(theme: "Theme: #{n}")
      BASELINE.times do |nn|
        Person.insert({name: "Name: #{nn}", party_id: party.id})
        Person.insert({name: "Name: #{nn}", other_party_id: party.id})
      end
    end
    
    measure(BASELINE*2/7) do
      Party.eager_load(:people, :other_people).to_a.each do |party|
        party.people.map { |p| p.id }
        party.other_people.map { |p| p.id }
      end
    end
    
    measure(BASELINE*2/7, in_transaction = true) do
      Party.eager_load(:people, :other_people).to_a.each do |party|
        party.people.map { |p| p.id }
        party.other_people.map { |p| p.id }
      end
    end
  end
  
  def bench_lazy_loading_with_1_to_1_records
    (BASELINE*20).times do |n|
      party = Party.create(theme: "Theme: #{n}")
      Person.insert({name: "Name: #{n}", party_id: party.id})
    end
    
    measure(1) do
      Party.all.each do |party|
        party.people.map { |p| p.id }
      end
    end
    
    measure(1, in_transaction = true) do
      Party.all.each do |party|
        party.people.map { |p| p.id }
      end
    end
  end
  
  def bench_lazy_loading_with_1_to_n_records
    BASELINE.times do |n|
      party = Party.create(theme: "Theme: #{n}")
      BASELINE.times do |nn|
        Person.insert({name: "Name: #{nn}", party_id: party.id})
      end
    end
    
    measure(BASELINE/2) do
      Party.all.each do |party|
        party.people.map { |p| p.id }
      end
    end
    
    measure(BASELINE/2, in_transaction = true) do
      Party.all.each do |party|
        party.people.map { |p| p.id }
      end
    end
  end
  
  def bench_light_threading
    measure(1) do
      threads = []
      Math.sqrt(BASELINE).round.times do
        threads << Thread.new do
          Party.insert_all((0..BASELINE).map { |n| {theme: "Theme: #{n}"} })
          BASELINE.times do
            party = Party.first
            party.update(theme: "Christmas")
            party.destroy
          end
          ActiveRecord::Base.clear_active_connections!
        end
        threads.each { |t| t.join }
      end
      Party.create(theme: "Halloween")
    end
  end
  
  def bench_heavy_threading
    measure(1) do
      threads = []
      BASELINE.times do
        threads << Thread.new do
          Party.insert_all((0..BASELINE).map { |n| {theme: "Theme: #{n}"} })
          BASELINE.times do
            party = Party.first
            party.update(theme: "Christmas")
            party.destroy
          end
          ActiveRecord::Base.clear_active_connections!
        end
        threads.each { |t| t.join }
      end
      Party.create(theme: "Halloween")
    end
  end
  
  def bench_model_object_select_json_nested
    JsonParty.insert_all((0..BASELINE*10).map { |n| { stuff: { n: n, theme: "Theme" } } })
    party_ids = JsonParty.pluck(:id)
    
    measure(BASELINE) do
      party_ids.each do |party_id|
        JsonParty.find_by("stuff->>'$.theme' = ?", "Theme")
      end
    end
    
    measure(BASELINE, in_transaction = true) do
      party_ids.each do |party_id|
        JsonParty.find_by("stuff->>'$.theme' = ?", "Theme")
      end
    end
  end
  
  def bench_model_object_update_json_nested
    JsonParty.insert_all((0..BASELINE*10).map { |n| { stuff: { n: n, theme: "Theme" } } })
    party_ids = JsonParty.pluck(:id)
    
    measure(BASELINE) do
      party_ids.each do |party_id|
        JsonParty.where(id: party_id).update_all(["stuff = jsonb_set(stuff::jsonb, '{theme}', to_json(?::text)::jsonb)", 'New Theme'])
      end
    end
    
    measure(BASELINE, in_transaction = true) do
      party_ids.each do |party_id|
        JsonParty.where(id: party_id).update_all(["stuff = jsonb_set(stuff::jsonb, '{theme}', to_json(?::text)::jsonb)", 'New Theme'])
      end
    end
  end
  
  def bench_model_object_update_json
    JsonParty.insert_all((0..BASELINE*10).map { |n| { stuff: { n: n, theme: "Theme" } } })
    party_ids = JsonParty.pluck(:id)
    
    measure(BASELINE) do
      party_ids.each do |party_id|
        JsonParty.where(id: party_id).update(:stuff=>{:pumpkin=>2, :candy=>1})
      end
    end
    
    measure(BASELINE, in_transaction = true) do
      party_ids.each do |party_id|
        JsonParty.where(id: party_id).update(:stuff=>{:pumpkin=>2, :candy=>1})
      end
    end
  end
end

# -----------------------------------------------------------------------------

class TestRecord < ActiveRecord::Base
  primary_abstract_class
end

class Party < TestRecord
  has_many :people
  has_many :other_people, class_name: 'Person', foreign_key: :other_party_id
end

class Person < TestRecord
  belongs_to :party
  belongs_to :other_party, class_name: 'Party', foreign_key: 'other_party_id'
end

class JsonParty < TestRecord
end

def run_benchmark!(enhance: false, log: true)
  # conn = PG.connect(dbname: 'postgres')
  # conn.exec("CREATE DATABASE ar_test IF NOT EXISTS;")
  ActiveRecord::Base.establish_connection(adapter: "postgresql", database: "ar_test")
  
  ActiveRecord::Schema.define do
    create_table :parties, force: true do |t|
      t.string :theme
    end
    
    create_table :people, force: true do |t|
      t.integer :party_id
      t.integer :other_party_id
      t.string :name
      t.string :address
    end
    
    create_table :json_parties, force: true do |t|
      t.json :stuff
    end
  end
  
  if log
    $stdout << {}.tap do |memo|
      memo['pg-ruby version'] = PG::VERSION
      memo['pg version'] = ActiveRecord::Base.connection.raw_connection.server_version
    end
    
    $stdout << "\n"
  end
  
  runs = []
  10.times { runs << BenchmarkTest.run!(log = false) }
  benches = runs.first.map { |result| [result.name, result.txn] }
  
  results_per_bench = benches.map do |bench, txn|
    [
      bench + (txn ? " (txn)" : ""),
      runs.sum do |results|
        results.find { |result| result.name == bench && result.txn == txn }
               .clk_time
      end.fdiv(runs.size).round(4)
    ]
  end.sort_by(&:last).reverse
  
  avg_run_time = runs.sum do |results|
    results.sum { |result| result.clk_time }
  end.fdiv(runs.size).round(4)
  
  [avg_run_time, results_per_bench]
end

sqlite-activerecord-benchmark.rb

require 'benchmark'
require 'active_record'
require 'sqlite3'

# -----------------------------------------------------------------------------

INPUT_COLUMNS = {
  name:       "Name of benchmark",
  iters:      "Number of iterations the block is run",
  usr_time:   "Amount of user CPU time",
  sys_time:   "Amount of system CPU time",
  ttl_time:   "Amount of total CPU time",
  clk_time:   "Amount of actual/real/wallclock time",
  txn:        "Whether a transaction was used",
}
PRAGMAS = %w[
  analysis_limit
  application_id
  auto_vacuum
  automatic_index
  busy_timeout
  cache_size
  cache_spill
  case_sensitive_like
  cell_size_check
  checkpoint_fullfsync
  data_version
  defer_foreign_keys
  encoding
  foreign_keys
  freelist_count
  fullfsync
  hard_heap_limit
  ignore_check_constraints
  integrity_check
  journal_mode
  journal_size_limit
  legacy_alter_table
  locking_mode
  max_page_count
  mmap_size
  page_count
  page_size
  query_only
  quick_check
  read_uncommitted
  recursive_triggers
  reverse_unordered_selects
  secure_delete
  soft_heap_limit
  synchronous
  temp_store
  threads
  trusted_schema
  user_version
  wal_autocheckpoint
]

Result = Struct.new(*INPUT_COLUMNS.keys)

module Workbench
  def measure(iterations, in_transaction = false, &block)
    GC.start
    
    label = caller_locations(1,1)[0].label.gsub("bench_", "")
    measurement = Benchmark.measure(nil) do
      if in_transaction
        ActiveRecord::Base.transaction(&block)
      else
        block.call
      end
    end
    output = measurement
              .format('%u,%y,%t,%r')
              .gsub(/[()]/, '')
              .split(',')
              .map { |s| s.strip.to_f.round(4) }
    
    @results << Result.new(*[label, iterations, *output, in_transaction])
  end
end

module Runner
  def run!(log = true)
    instance = new
    instance.instance_variable_set(:@results, [])
    instance_methods(_include_ancestors = false)
      .select { |m| m.to_s.start_with?("bench_") }
      .each do |m|
        instance.method(m).call
        TestRecord
          .descendants
          .each { |c| c.delete_all }
      end
    results = instance.instance_variable_get(:@results)
      .sort_by { |result| result.clk_time }
      .reverse
    
    if log
      $stdout << results
        .map do |result|
          sub = [
            result.txn.to_s.ljust(6, ' '),
            result.iters.to_s.ljust(5, ' '),
            result.usr_time.to_s.ljust(7, ' '),
            result.sys_time.to_s.ljust(7, ' '),
            result.ttl_time.to_s.ljust(7, ' '),
          ].join(" ")
          [
            result.name.ljust(60, ' '),
            result.clk_time.to_s.ljust(7, ' '),
            "(#{sub})"
          ].join(" ")
        end
        .join("\n")
      $stdout << "\n"
    end
    
    results
  end
end

# Based off of: https://github.com/jeremyevans/simple_orm_benchmark
class BenchmarkTest
  include Workbench
  extend Runner
  BASELINE = 64
  
  def bench_model_object_creation
    measure(BASELINE*50) do
      Party.create(theme: "Halloween")
    end
    
    measure(BASELINE*50, in_transaction = true) do
      Party.create(theme: "Halloween")
    end
  end
  
  def bench_model_object_select
    Party.insert_all((0..BASELINE*100).map { |n| {theme: "Theme: #{n}"} })
    
    measure(BASELINE) do
      Party.all.entries
    end
    
    measure(BASELINE, in_transaction = true) do
      Party.all.entries
    end
  end
  
  def bench_model_object_select_by_pk
    Party.insert_all((0..BASELINE*10).map { |n| {theme: "Theme: #{n}"} })
    party_ids = Party.pluck(:id)
    
    measure(BASELINE) do
      party_ids.each do |party_id|
        Party.find(party_id)
      end
    end
    
    measure(BASELINE, in_transaction = true) do
      party_ids.each do |party_id|
        Party.find(party_id)
      end
    end
  end
  
  def bench_model_object_select_by_attr
    Party.insert_all((0..BASELINE*10).map { |n| {theme: "Theme: #{n}"} })
    party_themes = Party.pluck(:theme)
    
    measure(BASELINE) do
      party_themes.each do |party_theme|
        Party.find_by(theme: party_theme)
      end
    end
    
    measure(BASELINE, in_transaction = true) do
      party_themes.each do |party_theme|
        Party.find_by(theme: party_theme)
      end
    end
  end
  
  def bench_model_object_select_and_save
    Party.insert_all((0..BASELINE*50).map { |n| {theme: "Theme: #{n}"} })
    
    measure(BASELINE) do
      Party.all.each do |party|
        party.theme += '.1'
        party.save
      end
    end
    
    measure(BASELINE, in_transaction = true) do
      Party.all.each do |party|
        party.theme += '.1'
        party.save
      end
    end
  end
  
  def bench_model_object_destruction
    Party.insert_all((0..BASELINE*100).map { |n| {theme: "Theme: #{n}"} })
    
    measure(BASELINE) do
      Party.all.each do |party|
        party.destroy
      end
    end
    
    measure(BASELINE, in_transaction = true) do
      Party.all.each do |party|
        party.destroy
      end
    end
  end
  
  def bench_model_object_and_associated_object_creation
    measure(BASELINE*20) do
      party = Party.create(theme: "X-mas")
      Person.create(party: party, name: "Test_#{party.id}")
    end
    
    measure(BASELINE*20, in_transaction = true) do
      party = Party.create(theme: "X-mas")
      Person.create(party: party, name: "Test_#{party.id}")
    end
  end
  
  def bench_model_object_and_associated_object_creation
    Party.insert_all((0..BASELINE*25).map { |n| {theme: "Theme: #{n}"} })
    party_ids = Party.pluck(:id)
    Person.insert_all(party_ids.map { |party_id| {name: "Party: #{party_id}", party_id: party_id} })
    
    measure(BASELINE) do
      Party.all.each do |party|
        party.people.each { |person| person.destroy }
        party.destroy
      end
    end
    
    measure(BASELINE, in_transaction = true) do
      Party.all.each do |party|
        party.people.each { |person| person.destroy }
        party.destroy
      end
    end
  end
  
  def bench_eager_loading_query_per_association_with_1_to_1_records
    (BASELINE*20).times do |n|
      party = Party.create(theme: "Theme: #{n}")
      Person.insert({name: "Name: #{n}", party_id: party.id})
    end
    
    measure(BASELINE*5/7) do
      Party.preload(:people).to_a.each do |party|
        party.people.map { |p| p.id }
      end
    end
    
    measure(BASELINE*5/7, in_transaction = true) do
      Party.preload(:people).to_a.each do |party|
        party.people.map { |p| p.id }
      end
    end
  end
  
  def bench_eager_loading_single_query_with_1_to_1_records
    (BASELINE*20).times do |n|
      party = Party.create(theme: "Theme: #{n}")
      Person.insert({name: "Name: #{n}", party_id: party.id})
    end
    
    measure(BASELINE*5/7) do
      Party.eager_load(:people).to_a.each do |party|
        party.people.each{|p| p.id}
      end
    end
    
    measure(BASELINE*5/7, in_transaction = true) do
      Party.eager_load(:people).to_a.each do |party|
        party.people.each{|p| p.id}
      end
    end
  end
  
  def bench_eager_loading_query_per_association_with_1_to_n_records
    BASELINE.times do |n|
      party = Party.create(theme: "Theme: #{n}")
      BASELINE.times do |nn|
        Person.insert({name: "Name: #{nn}", party_id: party.id})
      end
    end
    
    measure(BASELINE*5/7) do
      Party.preload(:people).to_a.each do |party|
        party.people.map { |p| p.id }
      end
    end
    
    measure(BASELINE*5/7, in_transaction = true) do
      Party.preload(:people).to_a.each do |party|
        party.people.map { |p| p.id }
      end
    end
  end
  
  def bench_eager_loading_single_query_with_1_to_n_records
    BASELINE.times do |n|
      party = Party.create(theme: "Theme: #{n}")
      BASELINE.times do |nn|
        Person.insert({name: "Name: #{nn}", party_id: party.id})
      end
    end
    
    measure(BASELINE*5/7) do
      Party.eager_load(:people).to_a.each do |party|
        party.people.map { |p| p.id }
      end
    end
    
    measure(BASELINE*5/7, in_transaction = true) do
      Party.eager_load(:people).to_a.each do |party|
        party.people.map { |p| p.id }
      end
    end
  end
  
  def bench_eager_loading_query_per_association_with_1_to_n_to_n_records
    BASELINE.times do |n|
      party = Party.create(theme: "Theme: #{n}")
      BASELINE.times do |nn|
        Person.insert({name: "Name: #{nn}", party_id: party.id})
        Person.insert({name: "Name: #{nn}", other_party_id: party.id})
      end
    end
    
    measure(BASELINE*2/7) do
      Party.preload(:people, :other_people).to_a.each do |party|
        party.people.map { |p| p.id }
        party.other_people.map { |p| p.id }
      end
    end
    
    measure(BASELINE*2/7, in_transaction = true) do
      Party.preload(:people, :other_people).to_a.each do |party|
        party.people.map { |p| p.id }
        party.other_people.map { |p| p.id }
      end
    end
  end
  
  def bench_eager_loading_single_query_with_1_to_n_to_n_records
    BASELINE.times do |n|
      party = Party.create(theme: "Theme: #{n}")
      BASELINE.times do |nn|
        Person.insert({name: "Name: #{nn}", party_id: party.id})
        Person.insert({name: "Name: #{nn}", other_party_id: party.id})
      end
    end
    
    measure(BASELINE*2/7) do
      Party.eager_load(:people, :other_people).to_a.each do |party|
        party.people.map { |p| p.id }
        party.other_people.map { |p| p.id }
      end
    end
    
    measure(BASELINE*2/7, in_transaction = true) do
      Party.eager_load(:people, :other_people).to_a.each do |party|
        party.people.map { |p| p.id }
        party.other_people.map { |p| p.id }
      end
    end
  end
  
  def bench_lazy_loading_with_1_to_1_records
    (BASELINE*20).times do |n|
      party = Party.create(theme: "Theme: #{n}")
      Person.insert({name: "Name: #{n}", party_id: party.id})
    end
    
    measure(1) do
      Party.all.each do |party|
        party.people.map { |p| p.id }
      end
    end
    
    measure(1, in_transaction = true) do
      Party.all.each do |party|
        party.people.map { |p| p.id }
      end
    end
  end
  
  def bench_lazy_loading_with_1_to_n_records
    BASELINE.times do |n|
      party = Party.create(theme: "Theme: #{n}")
      BASELINE.times do |nn|
        Person.insert({name: "Name: #{nn}", party_id: party.id})
      end
    end
    
    measure(BASELINE/2) do
      Party.all.each do |party|
        party.people.map { |p| p.id }
      end
    end
    
    measure(BASELINE/2, in_transaction = true) do
      Party.all.each do |party|
        party.people.map { |p| p.id }
      end
    end
  end
  
  def bench_light_threading
    measure(1) do
      threads = []
      Math.sqrt(BASELINE).round.times do
        threads << Thread.new do
          Party.insert_all((0..BASELINE).map { |n| {theme: "Theme: #{n}"} })
          BASELINE.times do
            party = Party.first
            party.update(theme: "Christmas")
            party.destroy
          end
          ActiveRecord::Base.clear_active_connections!
        end
        threads.each { |t| t.join }
      end
      Party.create(theme: "Halloween")
    end
  end
  
  def bench_heavy_threading
    measure(1) do
      threads = []
      BASELINE.times do
        threads << Thread.new do
          Party.insert_all((0..BASELINE).map { |n| {theme: "Theme: #{n}"} })
          BASELINE.times do
            party = Party.first
            party.update(theme: "Christmas")
            party.destroy
          end
          ActiveRecord::Base.clear_active_connections!
        end
        threads.each { |t| t.join }
      end
      Party.create(theme: "Halloween")
    end
  end
  
  def bench_model_object_select_json_nested
    JsonParty.insert_all((0..BASELINE*10).map { |n| { stuff: { n: n, theme: "Theme" } } })
    party_ids = JsonParty.pluck(:id)
    
    measure(BASELINE) do
      party_ids.each do |party_id|
        JsonParty.find_by("json_extract(stuff, '$.theme') = ?", "Theme")
      end
    end
    
    measure(BASELINE, in_transaction = true) do
      party_ids.each do |party_id|
        JsonParty.find_by("json_extract(stuff, '$.theme') = ?", "Theme")
      end
    end
  end
  
  def bench_model_object_update_json_nested
    JsonParty.insert_all((0..BASELINE*10).map { |n| { stuff: { n: n, theme: "Theme" } } })
    party_ids = JsonParty.pluck(:id)
    
    measure(BASELINE) do
      party_ids.each do |party_id|
        JsonParty.where(id: party_id).update_all(["stuff = JSON_SET(stuff, '$.theme', ?)", 'New Theme'])
      end
    end
    
    measure(BASELINE, in_transaction = true) do
      party_ids.each do |party_id|
        JsonParty.where(id: party_id).update_all(["stuff = JSON_SET(stuff, '$.theme', ?)", 'New Theme'])
      end
    end
  end
  
  def bench_model_object_update_json
    JsonParty.insert_all((0..BASELINE*10).map { |n| { stuff: { n: n, theme: "Theme" } } })
    party_ids = JsonParty.pluck(:id)
    
    measure(BASELINE) do
      party_ids.each do |party_id|
        JsonParty.where(id: party_id).update(:stuff=>{:pumpkin=>2, :candy=>1})
      end
    end
    
    measure(BASELINE, in_transaction = true) do
      party_ids.each do |party_id|
        JsonParty.where(id: party_id).update(:stuff=>{:pumpkin=>2, :candy=>1})
      end
    end
  end
end

# -----------------------------------------------------------------------------

module RailsExt
  module SQLite3Adapter
    PRAGMAS = {
      # level of database durability, 2 = "FULL" (sync on every write), other values include 1 = "NORMAL" (sync every 1000 written pages) and 0 = "NONE"
      # https://www.sqlite.org/pragma.html#pragma_synchronous
      synchronous: "NORMAL",
      # Journal mode WAL allows for greater concurrency (many readers + one writer)
      # https://www.sqlite.org/pragma.html#pragma_journal_mode
      journal_mode: "WAL",
      # impose a limit on the WAL file to prevent unlimited growth (with a negative impact on read performance as well)
      # https://www.sqlite.org/pragma.html#pragma_journal_size_limit
      journal_size_limit: 67108864, # 64.megabytes
      # set the global memory map so all processes can share data
      # https://www.sqlite.org/pragma.html#pragma_mmap_size
      # https://www.sqlite.org/mmap.html
      mmap_size: 134217728, # 128.megabytes
      # increase the local connection cache to 2000 pages
      # https://www.sqlite.org/pragma.html#pragma_cache_size
      cache_size: 2000,
    }
    # Perform any necessary initialization upon the newly-established
    # @raw_connection -- this is the place to modify the adapter's
    # connection settings, run queries to configure any application-global
    # "session" variables, etc.
    #
    # Implementations may assume this method will only be called while
    # holding @lock (or from #initialize).
    # https://github.com/rails/rails/blob/main/activerecord/lib/active_record/connection_adapters/sqlite3_adapter.rb#L691
    def configure_connection
      if @config[:timeout] && @config[:retries]
        raise ArgumentError, "Cannot specify both timeout and retries arguments"
      elsif @config[:retries]
        raw_connection.busy_handler do |count|
          count <= @config[:retries]
        end
      end
      
      super
      
      PRAGMAS.each do |key, value|
        execute("PRAGMA #{key} = #{value}", "SCHEMA")
      end
    end
  end
end

class TestRecord < ActiveRecord::Base
  primary_abstract_class
end

class Party < TestRecord
  has_many :people
  has_many :other_people, class_name: 'Person', foreign_key: :other_party_id
end

class Person < TestRecord
  belongs_to :party
  belongs_to :other_party, class_name: 'Party', foreign_key: 'other_party_id'
end

class JsonParty < TestRecord
end

def run_benchmark!(enhance: false, log: true)
  ActiveRecord::Base.establish_connection(adapter: "sqlite3", database: "ar_test.sqlite3")
  
  # Enhance the SQLite3 ActiveRecord adapter with optimized defaults and extensions
  ActiveSupport.on_load(:active_record_sqlite3adapter) do
    # self refers to `SQLite3Adapter` here,
    # so we can call .prepend
    prepend(RailsExt::SQLite3Adapter) if enhance
  end
  
  ActiveRecord::Schema.define do
    create_table :parties, force: true do |t|
      t.string :theme
    end
    
    create_table :people, force: true do |t|
      t.integer :party_id
      t.integer :other_party_id
      t.string :name
      t.string :address
    end
    
    create_table :json_parties, force: true do |t|
      t.json :stuff
    end
  end
  
  if log
    $stdout << {}.tap do |memo|
      memo['sqlite3-ruby version'] = SQLite3::VERSION
      memo['sqlite3 version'] = SQLite3::SQLITE_VERSION
      memo['sqlcipher?'] = SQLite3.sqlcipher?
      memo['threadsafe?'] = SQLite3.threadsafe?
      
      compile_options = {
        "compile_options" => ActiveRecord::Base.connection
                                               .execute("PRAGMA compile_options;")
                                               .map { _1["compile_options"] }
      }
      memo.merge!(compile_options)
      pragmas = memo['pragmas'] = {}
      PRAGMAS.each do |key, _value|
        result = ActiveRecord::Base.connection.execute("PRAGMA #{key};").first
        pragmas.merge!(result || {})
      end
    end
    
    $stdout << "\n"
  end
  
  runs = []
  10.times { runs << BenchmarkTest.run!(log = false) }
  benches = runs.first.map { |result| [result.name, result.txn] }
  
  results_per_bench = benches.map do |bench, txn|
    [
      bench + (txn ? " (txn)" : ""),
      runs.sum do |results|
        results.find { |result| result.name == bench && result.txn == txn }
               .clk_time
      end.fdiv(runs.size).round(4)
    ]
  end.sort_by(&:last).reverse
  
  avg_run_time = runs.sum do |results|
    results.sum { |result| result.clk_time }
  end.fdiv(runs.size).round(4)
  
  [avg_run_time, results_per_bench]
end