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

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?