Joshua Wood

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?
  0 likes and retweets
  • As I missed his talk, I want to say thank you so much for the valuable information.

    If you have free time, let me hear your opinion on it. Should we replace all #where with this?

    Thank you

 Want to opt out?