DB Query Performance demonstration webapp

db_performance.rake

# frozen_string_literal: true

namespace :db_performance do
  desc 'Tests how badly written db calculations perform against ruby'
  task(test: :environment) do
    DBQueryPerformance.test
  end
end

db_query_performance.rb

# frozen_string_literal: true

require 'benchmark'

class DBQueryPerformance
  include Benchmark

  SET_SIZE = 1000

  def self.test
    Bullet.start_request

    puts(benchmarks.map do |benchmark|
      format(
        '%<label>30s: %<total>5.2f total, %<real>5.2f real, %<user>5.2f user',
        label: benchmark.label,
        total: benchmark.total,
        real: benchmark.real,
        user: benchmark.utime
      )
    end.join("\n"))
    Bullet.perform_out_of_channel_notifications if Bullet.notification?
    Bullet.end_request
  end

  def self.benchmarks
    Benchmark.bm do |x|
      puts "\n\n\n### Running #ruby_calculation_sum ###\n\n\n"
      sleep 1
      x.report('ruby_calculation_sum') { ruby_calculation_sum }

      puts "\n\n\n### Running #db_calculation_sum ###\n\n\n"
      sleep 1
      x.report('db_calculation_sum') { db_calculation_sum }

      puts "\n\n\n### Running #partial_with_select ###\n\n\n"
      sleep 1
      x.report('partial_with_select') { partial_with_select }

      puts "\n\n\n### Running #partial_with_where ###\n\n\n"
      sleep 1
      x.report('partial_with_where') { partial_with_where }

      puts "\n\n\n### Running #realistic_partial_with_select ###\n\n\n"
      sleep 1
      x.report('realistic_partial_with_select') { realistic_partial_with_select }

      puts "\n\n\n### Running #realistic_partial_with_where ###\n\n\n"
      sleep 1
      x.report('realistic_partial_with_where') { realistic_partial_with_where }

      puts "\n\n\n### Running #instance_calculations_ruby ###\n\n\n"
      sleep 1
      x.report('instance_calculations_ruby') { instance_calculations_ruby }

      puts "\n\n\n### Running #instance_calculations_db ###\n\n\n"
      sleep 1
      x.report('instance_calculations_db') { instance_calculations_db }
    end
  end

  def self.db_calculation_sum
    Order.limit(SET_SIZE).each do |order|
      order.line_items.sum(:price_eur)
    end
  end

  def self.ruby_calculation_sum
    Order.limit(SET_SIZE).includes(:line_items).each do |order|
      order.line_items.sum(&:price_eur)
    end
  end

  def self.partial_with_where
    Order
      .limit(SET_SIZE)
      .includes(line_items: :calculation)
      .joins(line_items: :calculation)
      .each do |order|
        # Couldn't get it to work without includes(:calculation) here
        order.line_items.includes(:calculation).where(calculations: {calculator_type: 'FlightCalculator'}).to_a
      end
  end

  def self.partial_with_select
    Order
      .limit(SET_SIZE)
      .joins(line_items: :calculation)
      .includes(line_items: :calculation)
      .each do |order|
        order.line_items.select { |line_item| line_item.calculation.calculator_type == 'FlightCalculator' }
      end
  end

  def self.realistic_partial_with_where
    Order
      .limit(SET_SIZE)
      .includes(line_items: :calculation) # Bullet: Remove whole line
      .joins(line_items: :calculation)
      .each do |order|
        # Couldn't get it to work without includes(:calculation) here
        order.line_items.sum(:price_eur)
        order.line_items.includes(:calculation).where(calculations: {calculator_type: 'CarCalculator'}).to_a
        order.line_items.includes(:calculation).where(calculations: {calculator_type: 'FlightCalculator'}).to_a
        order.line_items.includes(:calculation).where(calculations: {calculator_type: 'EventCalculator'}).to_a
        order.line_items.includes(:calculation).where(calculations: {calculator_type: 'CarCalculator'}).sum(:price_eur)
        order.line_items.includes(:calculation).where(calculations: {calculator_type: 'FlightCalculator'}).sum(:price_eur)
        order.line_items.includes(:calculation).where(calculations: {calculator_type: 'EventCalculator'}).sum(:price_eur)
      end
  end

  def self.realistic_partial_with_select
    Order
      .limit(SET_SIZE)
      .joins(line_items: :calculation)
      .includes(line_items: :calculation)
      .each do |order|
        order.line_items.sum(&:price_eur)
        order.line_items.select { |line_item| line_item.calculation.calculator_type == 'CarCalculator' }
        order.line_items.select { |line_item| line_item.calculation.calculator_type == 'FlightCalculator' }
        order.line_items.select { |line_item| line_item.calculation.calculator_type == 'EventCalculator' }
        order.line_items.select { |line_item| line_item.calculation.calculator_type == 'CarCalculator' }.sum(&:price_eur)
        order.line_items.select { |line_item| line_item.calculation.calculator_type == 'FlightCalculator' }.sum(&:price_eur)
        order.line_items.select { |line_item| line_item.calculation.calculator_type == 'EventCalculator' }.sum(&:price_eur)
      end
  end

  def self.instance_calculations_db
    Order
      .limit(SET_SIZE)
      .joins(line_items: :calculation)
      .includes(line_items: :calculation)
      .sum(&:compensated_co2_not_from_common_calculators_db)
  end

  def self.instance_calculations_ruby
    Order
      .limit(SET_SIZE)
      .joins(line_items: :calculation)
      .includes(line_items: :calculation)
      .sum(&:compensated_co2_not_from_common_calculators_ruby)
  end
end

class Order < OrderContainer
  def compensated_co2_not_from_common_calculators_db
    line_items.sum(:metric_tons) - compensated_co2_from_common_calculators_db
  end

  def compensated_co2_from_common_calculators_db
    compensated_co2_from_car_calculators_db + compensated_co2_from_flight_calculators_db
  end

  def compensated_co2_from_car_calculators_db
    line_items
      .includes(:calculation)
      .where(calculations: {calculator_type: 'CarCalculator'})
      .sum(:metric_tons)
  end

  def compensated_co2_from_flight_calculators_db
    line_items
      .includes(:calculation)
      .where(calculations: {calculator_type: 'FlightCalculator'})
      .sum(:metric_tons)
  end

  def compensated_co2_not_from_common_calculators_ruby
    line_items.sum(&:metric_tons) - compensated_co2_from_common_calculators_ruby
  end

  def compensated_co2_from_common_calculators_ruby
    compensated_co2_from_car_calculators_ruby + compensated_co2_from_flight_calculators_ruby
  end

  def compensated_co2_from_car_calculators_ruby
    line_items
      .select { |line_item| line_item.calculation.calculator_type == 'CarCalculator' }
      .sum(&:metric_tons)
  end

  def compensated_co2_from_flight_calculators_ruby
    line_items
      .select { |line_item| line_item.calculation.calculator_type == 'FlightCalculator' }
      .sum(&:metric_tons)
  end
end