FARTS - Faster ActiveRecord Through SQL

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

Transcript

1. FASTER ACTIVE RECORD THROUGH SQL FA R T S @kylebalderson
2. WHY SHOULD WE FARTS? THERE ARE DIFFERENT WAYS OF GETTING SIMILAR RESULTS
3. A PROBLEM IT’S TOO SLOW
4. 13,668 TOTAL ORDERS 46.29 SECONDS 43 RECENTLY SHIPPED HOW SLOW?
5. 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
6. THIS LOADS EVERY ORDER IN THE DATABASE INTO RUBY OBJECTS AND LOOPS THROUGH THEM TWICE
7. 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
8. 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’)
9. HOW MUCH FASTER? 46.29 SECONDS 0.02 SECONDS
10. WHAT ELSE CAN WE DO WITH SQL?
11. 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
12. OTHER MATH FUNCTIONS ▸ average ▸ count ▸ maximum ▸ minimum ▸ sum
13. WHEN TO AVOID COLLECT / MAP
14. 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
15. 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
16. 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
17. QUESTIONS?
18. THANK YOU @kylebalderson FASTER ACTIVERECORD THROUGH SQL (FARTS)