Aaron Patterson's Variety Show!
Speaker: Aaron Patterson
Source: RailsConf 2020
Summary: I will talk about a variety of things related to puns, Rails, and puns on Rails
Notes
#lol “It was very Covid outside” 22:00
Technical talk starts at 23:58
This talk is about where clauses
How they work
Features you might not know about
Profiling/speeding up clauses
Not in your application
How to speed up Rails itself
Problem: Post.where(ActiveRecord::Base.sanitize_sql(["id IN (?)", ids])
is much faster than Post.where(id: ids)
Why?
Aaron benchmarked it.
sanitize_sql
was 74% faster than the simpler APIBut something is wrong with the benchmark
Back to basics
The
#where
methodCalls are additive:
Post.where(..)
,Post.where(..).where()
It builds up a SQL statement.
Something about automatic SQL generation is slower than doing it manually.
Post.all.inspect
adds a LIMIT statement to the end of the queryPost.all.to_a
no LIMITPost.all.inspect
adds “LIMIT 11” to queryActiveRecord is lazy about generating the query. We need to proactively load the records for the SQL to execute (
Post.where(...).to_a
).Aaron fixed the benchmark so that each query executed to find 1000 ids.
New benchmark:
sanitize_sql
statement is still 60% faster.
Next step: profile the code
Using StackProf
Generate two profiles–one for each case.
Slow case (no_sanitize): 14% querying the database.
Fast case (sanitize): basically same (20%).
Speeding up the query method won’t help in this case.
These profiles tells us how much time we spend in any one particular function call.
Self vs Self + Children
What about all of the functions it calls outside of the current method?
Use
--sort-total
to tell StackProf to sort by the total time spent in a method + any method it calls.
With
--sort-total
flag, 29% time spent compiling SQL in slow case.Compiling doesn’t even register in fast case (it’s fast)
Out target is
Arel::Visitors::ToSql#compile
ActiveRecord Design
SQL generation involves a few steps:
Creating Relation Objects
Each call to
where
, etc. returns a new oneObjects can be reused
Use DOT command line tool to generate graph:
dot -Tpng -o out.png out.dot
SQL Generation
SQL AST -> SQL Statement
Uses “Vistor” pattern to walk tree
Our target is Arel::Visitors::ToSql#compile
Something is different in the ASTs that are being generated
Generate DOT for fast and slow cases
Auto-sanitized (slow) version very different from
sanitize_sql
(fast) version
StackProf comparison shows similar results for generating SQL: 28.9% vs. 0.6%
These number correlate with the ASTs
How to fix?
We want to make both methods to work equally
Reduce the work that the slow case is doing
Make where
call sanitize_sql
#lol 3 refactoring questions:
Can I refactor this code?
Should I refactor this code?
Do I want my name on the blame?
Aaron contacted Eileen
There are a number of issues with just calling sanitize_sql
to generate the query. Non-integer IDS, sanitization, large numbers, etc.
The solution was to create pre-compiled nodes (a new type of node)
Nodes can be compiled at the where
callsite, where we know we have a list of IDs. Instead of creating a list of IDs, create a pre-compiled node.
Aaron and Eileen implemented this
Performance difference
Their branch is 30% faster than master
Compare w/ original self-sanitization benchmarks
Still 1.18x slower (but much better)
Aaron thinks they can still optimize further to make them equal.
They call this “Eager Compilation”
Takeaways:
Not all code is equal
Similar behavior should have similar performance
Poor performance is a bug
#lol Poor performance? In THIS economy?