Skip to content
  • eileencodes's avatar
    72fd0bae
    Perf: Improve performance of where when using an array of values · 72fd0bae
    eileencodes authored
    
    
    A coworker at GitHub found a few months back that if we used
    `santitize_sql` over `where` when we knew the values going into `where`
    it was a lot faster than `where`.
    
    This PR adds a new Arel node type called `HomogenousIn` that will be
    used when Rails knows the values are all homogenous and can therefore
    pick a faster codepath. This new codepath skips some of the required
    processing by `where` to make `wheres` with homogenous arrays faster
    without requiring the application author to know when to use which query
    type.
    
    Using our benchmark code:
    
    ```ruby
    ids = (1..1000).each.map do |n|
      Post.create!.id
    end
    
    Benchmark.ips do |x|
      x.report("where with ids") do
        Post.where(id: ids).to_a
      end
    
      x.report("where with sanitize") do
        Post.where(ActiveRecord::Base.sanitize_sql(["id IN (?)", ids])).to_a
      end
    
      x.compare!
    end
    ```
    
    Before this PR comparing where with a list of IDs to santitize sql:
    
    ```
    Warming up --------------------------------------
          where with ids    11.000  i/100ms
     where with sanitize    17.000  i/100ms
    
    Calculating -------------------------------------
          where with ids    115.733  (± 4.3%) i/s -    583.000  in   5.045828s
     where with sanitize    174.231  (± 4.0%) i/s -    884.000  in   5.081495s
    
    Comparison:
     where with sanitize:      174.2 i/s
          where with ids:      115.7 i/s - 1.51x  slower
    ```
    
    After this PR comparing where with a list of IDs to santitize sql:
    
    ```
    Warming up --------------------------------------
          where with ids    16.000  i/100ms
     where with sanitize    19.000  i/100ms
    
    Calculating -------------------------------------
          where with ids    158.293  (± 6.3%) i/s -    800.000  in   5.072208s
     where with sanitize    169.141  (± 3.5%) i/s -    855.000  in   5.060878s
    
    Comparison:
     where with sanitize:      169.1 i/s
          where with ids:      158.3 i/s - same-ish: difference falls within error
    ```
    
    Co-authored-by: default avatarAaron Patterson <aaron.patterson@gmail.com>
    72fd0bae
    Perf: Improve performance of where when using an array of values
    eileencodes authored
    
    
    A coworker at GitHub found a few months back that if we used
    `santitize_sql` over `where` when we knew the values going into `where`
    it was a lot faster than `where`.
    
    This PR adds a new Arel node type called `HomogenousIn` that will be
    used when Rails knows the values are all homogenous and can therefore
    pick a faster codepath. This new codepath skips some of the required
    processing by `where` to make `wheres` with homogenous arrays faster
    without requiring the application author to know when to use which query
    type.
    
    Using our benchmark code:
    
    ```ruby
    ids = (1..1000).each.map do |n|
      Post.create!.id
    end
    
    Benchmark.ips do |x|
      x.report("where with ids") do
        Post.where(id: ids).to_a
      end
    
      x.report("where with sanitize") do
        Post.where(ActiveRecord::Base.sanitize_sql(["id IN (?)", ids])).to_a
      end
    
      x.compare!
    end
    ```
    
    Before this PR comparing where with a list of IDs to santitize sql:
    
    ```
    Warming up --------------------------------------
          where with ids    11.000  i/100ms
     where with sanitize    17.000  i/100ms
    
    Calculating -------------------------------------
          where with ids    115.733  (± 4.3%) i/s -    583.000  in   5.045828s
     where with sanitize    174.231  (± 4.0%) i/s -    884.000  in   5.081495s
    
    Comparison:
     where with sanitize:      174.2 i/s
          where with ids:      115.7 i/s - 1.51x  slower
    ```
    
    After this PR comparing where with a list of IDs to santitize sql:
    
    ```
    Warming up --------------------------------------
          where with ids    16.000  i/100ms
     where with sanitize    19.000  i/100ms
    
    Calculating -------------------------------------
          where with ids    158.293  (± 6.3%) i/s -    800.000  in   5.072208s
     where with sanitize    169.141  (± 3.5%) i/s -    855.000  in   5.060878s
    
    Comparison:
     where with sanitize:      169.1 i/s
          where with ids:      158.3 i/s - same-ish: difference falls within error
    ```
    
    Co-authored-by: default avatarAaron Patterson <aaron.patterson@gmail.com>
Loading