Upgrade to Pro — share decks privately, control downloads, hide ads and more …

FARTS - Faster ActiveRecord Through SQL

FARTS - Faster ActiveRecord Through SQL

Exploring ways of filtering, calculating, and sorting using SQL instead of Iterating over records

Kyle Balderson

February 05, 2016
Tweet

Other Decks in Programming

Transcript

  1. WHAT’S THE CAUSE? def recently_shipped_orders @shipped_orders = Order.all.select do |o|

    o.current_state == “shipped” end @recently_shipped_orders = @shipped_orders.select do |o| o.updated_at <= 3.days.ago end end
  2. EVERY SINGLE ORDER def recently_shipped_orders @shipped_orders = Order.all.select do |o|

    o.current_state == “shipped” end @recently_shipped_orders = @shipped_orders.select do |o| o.updated_at <= 3.days.ago end end
  3. A FASTER WAY def recently_shipped_orders @shipped_orders = Order.where(current_state: "shipped") @recently_shipped_orders

    = @shipped_orders.where(“updated_at > ?", 3.days.ago) end SELECT “orders”.* FROM “orders” WHERE “orders”.“current_state” = ‘shipped’ AND (updated_at > ‘2016-02-01’)
  4. TOTALING ATTRIBUTES ON MODELS LineItem .where("created_at > ?", 1.month.ago) .reduce(0){

    |total, line_item| total + line_item.quantity } LineItem .where("created_at > ?", 1.month.ago) .sum(:quantity) 26.56 SECONDS 0.061 SECONDS
  5. GET AN ARRAY OF ATTRIBUTES LineItem.all. collect do |line_item| line_item.item_color

    end LineItem .where("created_at > ?", 1.month.ago) .pluck(:item_color) 1.14 SECONDS 0.08 SECONDS
  6. RELATED RECORDS Order .where("created_at > ?", 6.months.ago) .joins(:line_items) Order .where("created_at

    > ?", 6.months.ago) .collect do |order| order.line_items.any? end 7.70 SECONDS 0.01 SECONDS
  7. NO RELATED RECORDS Order .where("created_at > ?", 6.months.ago) .joins(" LEFT

    JOIN line_items ON line_items.order_id = orders.id” ) .group("orders.id") .having("COUNT(line_items.*) = ?", 0) Order .where("created_at > ?", 6.months.ago) .collect do |order| order.line_items.empty? end 7.79 SECONDS 0.06 SECONDS