advanced_queries_benchmark.rb

# frozen_string_literal: true

require "bundler/inline"

gemfile(true) do
  source "https://rubygems.org"

  git_source(:github) { |repo| "https://github.com/#{repo}.git" }

  gem "rails"
  gem "sqlite3"
  gem "memory_profiler"
  gem "table_print"
  gem "benchmark-ips"
end

require "active_record"
require "memory_profiler"
require "table_print"
require "tempfile"
require "benchmark/ips"
# require "logger"

ActiveRecord::Base.establish_connection(adapter: "sqlite3", database: ":memory:")
# ActiveRecord::Base.logger = Logger.new(STDOUT)

ActiveRecord::Schema.define do
  create_table :networks, force: true do |t|
    t.string :code
    t.string :name
    t.boolean :active
  end

  create_table :currencies, force: true do |t|
    t.string :code
    t.string :name
  end

  create_table :currency_networks, force: true do |t|
    t.references :currency, null: false, foreign: true, index: true
    t.references :network, null: false, foreign: true, index: true
  end

  create_table :pairs, force: true do |t|
    t.references :base_currency, null: false
    t.references :quote_currency, null: false
  end

  add_foreign_key :pairs, :currencies, column: :base_currency_id, primary_key: :id
  add_foreign_key :pairs, :currencies, column: :quote_currency_id, primary_key: :id

  create_table :tickers, force: true do |t|
    t.references :pair, null: false, index: true
    t.integer :trade_count, null: false
  end
end

class Network < ActiveRecord::Base
  has_many :currency_networks
  has_many :currencies, through: :currency_networks
end

class Currency < ActiveRecord::Base
  has_many :currency_networks
  has_many :networks, through: :currency_networks
  has_many :pairs_as_base, class_name: "Pair", foreign_key: :base_currency_id
  has_many :pairs_as_quote, class_name: "Pair", foreign_key: :quote_currency_id
end

class CurrencyNetwork < ActiveRecord::Base
  belongs_to :currency
  belongs_to :network
end

class Pair < ActiveRecord::Base
  has_many :tickers

  belongs_to :base_currency, class_name: "Currency", foreign_key: :base_currency_id
  belongs_to :quote_currency, class_name: "Currency", foreign_key: :quote_currency_id
end

class Ticker < ActiveRecord::Base
  belongs_to :pair

  validates_presence_of :trade_count
end

nbsc = Network.create!(code: "BSC", name: "Binance Smart Chain (BEP20)", active: true)
neth = Network.create!(code: "ETH", name: "Ethereum (ERC20)", active: false)
nbtc = Network.create!(code: "BTC", name: "Bitcoin", active: false)
ndot = Network.create!(code: "DOT", name: "Polkadot", active: true)

btc = Currency.create!(code: "BTC", name: "Bitcoin")
eth = Currency.create!(code: "ETH", name: "Ethereum")
dot = Currency.create!(code: "DOT", name: "Polkadot")
usdt = Currency.create!(code: "USDT", name: "Theter")

btc.networks << nbtc
eth.networks << [neth, nbsc]
dot.networks << [ndot, nbsc]
usdt.networks << [neth, nbsc]

eth_btc = Pair.create!(base_currency: eth, quote_currency: btc)
btc_usdt = Pair.create!(base_currency: btc, quote_currency: usdt)
dot_usdt = Pair.create!(base_currency: dot, quote_currency: usdt)

Ticker.create!(
  pair: eth_btc,
  trade_count: 103466
)

Ticker.create!(
  pair: btc_usdt,
  trade_count: 930518
)

Ticker.create!(
  pair: dot_usdt,
  trade_count: 342796
)

Ticker.create!(
  pair: dot_usdt,
  trade_count: 10000
)

Ticker.create!(
  pair: dot_usdt,
  trade_count: 342796
)

puts
puts "Table: networks"
tp Network.all
puts
puts "Table: currencies"
tp Currency.all
puts
puts "Table: currency_networks"
tp CurrencyNetwork.all
puts
puts "Table: pairs"
tp Pair.all
puts
puts "Table: tickers"
tp Ticker.all
puts

def run_memory_profiler(func, title)
  report = MemoryProfiler.report do
    func.call
  end
  
  file = Tempfile.new("results")
  
  report.pretty_print(to_file: file.path)
  
  puts title
  puts File.foreach(file.path).first(2)
  puts
  
  file.unlink
end

# belongs_to query

belongs_to_enumerable_solution = lambda { CurrencyNetwork.all.select { |cn| cn.network.active? } }
belongs_to_query_solution = lambda { CurrencyNetwork.joins(:network).where(networks: { active: true }) }

run_memory_profiler(belongs_to_enumerable_solution, "Belongs to query - Enumerable solution (memory profiler)")
run_memory_profiler(belongs_to_query_solution, "Belongs to query - ActiveRecord query solution (memory profiler)")

Benchmark.ips do |x|
  x.report("IPS - Belongs to query - Enumerable solution") { belongs_to_enumerable_solution.call }
  x.report("IPS - Belongs to query - ActiveRecord query solution") { belongs_to_query_solution.call }
  x.compare!
end

# has_many query

has_many_enumerable_solution = lambda { Currency.all.select { |currency| currency.networks.where(active: true).any? } }
has_many_query_solution = lambda { Currency.joins(currency_networks: :network).where(networks: { active: true }) }

run_memory_profiler(has_many_enumerable_solution, "Has many query - Enumerable solution (memory profiler)")
run_memory_profiler(has_many_query_solution, "Has many query - ActiveRecord query solution (memory profiler)")

Benchmark.ips do |x|
  x.report("IPS - Has many query - Enumerable solution") { has_many_enumerable_solution.call }
  x.report("IPS - Has many query - ActiveRecord query solution") { has_many_query_solution.call }
  x.compare!
end

# Aggregations

puts "Average trade count: #{Ticker.average(:trade_count)}"
puts
puts "Trade count by currency: #{Currency.joins(pairs_as_base: :tickers).group("currencies.name").sum(:trade_count)}"
puts

# Hits the database twice
# Ticker.where("trade_count > :avg", avg: Ticker.average(:trade_count)) 

# Hits the database only once
query = Ticker.where("trade_count > (:avg)", avg: Ticker.select("AVG(trade_count)"))
puts query.to_sql
tp query
puts

avg_trade_count_by_pairs_query_solution = lambda do
  avg_trade_count_by_pairs_sql = Ticker.select("pair_id, AVG(trade_count) as average").group(:pair_id).to_sql
  Ticker.joins("INNER JOIN (" + avg_trade_count_by_pairs_sql + ") trades ON tickers.pair_id = trades.pair_id").where("tickers.trade_count >= trades.average")
end

avg_trade_count_by_pairs_enumerable_solution = lambda do
  tickers = []
  Pair.all.each do |pair|
    avg = Ticker.where(pair: pair).average(:trade_count)
    selectable_tickers = Ticker.where(pair: pair).where("trade_count >= ?", avg)
    tickers.push(*selectable_tickers.to_a)
  end
end

run_memory_profiler(avg_trade_count_by_pairs_enumerable_solution, "Avg trade count by pairs - Enumerable solution (memory profiler)")
run_memory_profiler(avg_trade_count_by_pairs_query_solution, "Avg trade count by pairs - ActiveRecord query solution (memory profiler)")

Benchmark.ips do |x|
  x.report("IPS - Avg trade count by pairs - Enumerable solution") { avg_trade_count_by_pairs_enumerable_solution.call }
  x.report("IPS - Avg trade count by pairs - ActiveRecord query solution") { avg_trade_count_by_pairs_query_solution.call }
  x.compare!
end

puts