Upgrade to Pro
— share decks privately, control downloads, hide ads and more …
Speaker Deck
Features
Speaker Deck
PRO
Sign in
Sign up for free
Search
Search
FARTS - Faster ActiveRecord Through SQL
Search
Kyle Balderson
February 05, 2016
Programming
0
66
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
Share
Other Decks in Programming
See All in Programming
IntelliJ IDEA を知らなかった 自分に教えたい小ネタ集 / IntelliJ IDEA Hints for My Past Self
mackey0225
3
160
incrementalモデルの理解を深める
ikkimiyazaki
2
640
htmx is fun!
codehex
2
190
Deno に Web 標準 API を実装する / Implementing Web Standard API to Deno
petamoriken
0
350
PHPerKaigi 2024〜10年以上動いているレガシーなバッチシステムを Kubernetes(Amazon EKS) に移行する取り組み〜
tshinowpub
1
220
Creating Retro-Style Photos Using Swift
ski
1
340
AppDeveloperCon 2024 EU: Building polyglot developer experiences in 2024
salaboy
0
380
脱・初心者!脱・マネコン!AWS CDKを使ってみませんか!?
har1101
0
100
PHP 8.3で追加されたjson_validate()を徹底的に深掘りしてみよう
mashirou1234
1
720
DDDはなぜ難しいのか / 良いコードの定義と設計能力の壁
pospome
24
7.3k
Understanding Ast By Looking
inouehi
0
120
生成 AI の中身を覗いてみよう〜基礎から医療現場での応用まで〜
soh9834
2
760
Featured
See All Featured
Into the Great Unknown - MozCon
thekraken
10
830
What the flash - Photography Introduction
edds
64
11k
Code Review Best Practice
trishagee
54
15k
Six Lessons from altMBA
skipperchong
19
2.9k
Designing Dashboards & Data Visualisations in Web Apps
destraynor
225
51k
Agile that works and the tools we love
rasmusluckow
323
20k
Building an army of robots
kneath
300
41k
A better future with KSS
kneath
230
16k
The Cost Of JavaScript in 2023
addyosmani
13
3.7k
How to Create Impact in a Changing Tech Landscape [PerfNow 2023]
tammyeverts
12
1.4k
Designing the Hi-DPI Web
ddemaree
275
33k
Embracing the Ebb and Flow
colly
78
4.1k
Transcript
FASTER ACTIVE RECORD THROUGH SQL FA R T S @kylebalderson
WHY SHOULD WE FARTS? THERE ARE DIFFERENT WAYS OF GETTING
SIMILAR RESULTS
A PROBLEM IT’S TOO SLOW
13,668 TOTAL ORDERS 46.29 SECONDS 43 RECENTLY SHIPPED HOW SLOW?
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
THIS LOADS EVERY ORDER IN THE DATABASE INTO RUBY OBJECTS
AND LOOPS THROUGH THEM TWICE
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
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’)
HOW MUCH FASTER? 46.29 SECONDS 0.02 SECONDS
WHAT ELSE CAN WE DO WITH SQL?
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
OTHER MATH FUNCTIONS ▸ average ▸ count ▸ maximum ▸
minimum ▸ sum
WHEN TO AVOID COLLECT / MAP
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
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
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
QUESTIONS?
THANK YOU @kylebalderson FASTER ACTIVERECORD THROUGH SQL (FARTS)