Aaron Patterson's Variety Show!

Speaker: Aaron Patterson

Source: RailsConf 2020

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

Tags: #Talks #Rails #ActiveRecord #Eileen M. Uchitelle

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 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 June 2021