Aaron Patterson's Variety Show!

“I will talk about a variety of things related to puns, Rails, and puns on Rails” —Aaron

https://railsconf.com/2020/video/aaron-patterson-aaron-patterson-s-variety-show

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 API
- But something is wrong with the benchmark
  - Back to basics
    - The #where method
      - Calls 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 query
      - Post.all.to_a no LIMIT
      - Post.all.inspect adds “LIMIT 11” to query
      - ActiveRecord 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 one
    - Objects can be reused
  - Convert Relation objects into SQL AST
    - Tree data structure: relation.arel.ast
    - Graphviz DOT format: relation.arel.to_dot
  - 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?

Updated March 2025