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
#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
Post.where(ActiveRecord::Base.sanitize_sql(["id IN (?)", ids]) is much faster than
Aaron benchmarked it.
sanitize_sqlwas 74% faster than the simpler API
But something is wrong with the benchmark
Back to basics
Calls are additive:
It builds up a SQL statement.
Something about automatic SQL generation is slower than doing it manually.
Post.all.inspectadds a LIMIT statement to the end of the query
Post.all.inspectadds “LIMIT 11” to query
ActiveRecord is lazy about generating the query. We need to proactively load the records for the SQL to execute (
Aaron fixed the benchmark so that each query executed to find 1000 ids.
sanitize_sqlstatement is still 60% faster.
Next step: profile the code
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?
--sort-totalto tell StackProf to sort by the total time spent in a method + any method it calls.
--sort-totalflag, 29% time spent compiling SQL in slow case.
Compiling doesn’t even register in fast case (it’s fast)
Out target is
SQL generation involves a few steps:
Creating Relation Objects
Each call to
where, etc. returns a new one
Objects can be reused
Use DOT command line tool to generate graph:
dot -Tpng -o out.png out.dot
SQL AST -> SQL Statement
Uses “Vistor” pattern to walk tree
Our target is
Something is different in the ASTs that are being generated
Generate DOT for fast and slow cases
Auto-sanitized (slow) version very different from
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
#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
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”
Not all code is equal
Similar behavior should have similar performance
Poor performance is a bug
#lol Poor performance? In THIS economy?