Joshua Wood Web Developer

Aaron Patterson's Variety Show!

Speaker: Aaron Patterson

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

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

    • 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”


  • Not all code is equal

  • Similar behavior should have similar performance

  • Poor performance is a bug

#lol Poor performance? In THIS economy?