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 October 2020