Skip to content

Instantly share code, notes, and snippets.

@paneq
Last active December 11, 2016 13:00
Show Gist options
  • Save paneq/a643b9a3cc694ba3eb6e to your computer and use it in GitHub Desktop.
Save paneq/a643b9a3cc694ba3eb6e to your computer and use it in GitHub Desktop.
cost of using exceptions for control flow compared to one SQL statement (ruby 2.1.4, rails 4.1.7, sqlite) for rails-refactoring.com . Development mode executed in rails console.
require 'benchmark'
ActiveRecord::Base.logger = nil
Benchmark.bmbm do |bench|
bench.report("SQL query") do
1000.times { Whatever.count }
end
bench.report("exception hit") do
1000.times do
begin
raise StandardError.new
rescue
end
end
end
bench.report("exception miss") do
1000.times do
begin
raise StandardError.new if false
rescue
end
end
end
end
Rehearsal --------------------------------------------------
SQL query 0.180000 0.010000 0.190000 ( 0.199253)
exception hit 0.050000 0.000000 0.050000 ( 0.050654)
exception miss 0.000000 0.000000 0.000000 ( 0.000050)
----------------------------------------- total: 0.240000sec
user system total real
SQL query 0.180000 0.010000 0.190000 ( 0.179669)
exception hit 0.050000 0.000000 0.050000 ( 0.048447)
exception miss 0.000000 0.000000 0.000000 ( 0.000054)
@chastell
Copy link

I’m not sure how much (a) the used database engine, (b) the type of database, (c) the size of the table, (d) disk caching, (e) database’s caching and (f) Active Record’s caching matters, but FWIW here is my script that runs against an on-disk PostgreSQL empty table with a single text column:

require 'active_record'
require 'benchmark/ips'

ActiveRecord::Base.logger = nil
ActiveRecord::Base.establish_connection adapter:  'postgresql',
                                        database: 'whatevers'

Whatever = Class.new(ActiveRecord::Base)

Benchmark.ips do |bench|
  bench.report('SQL query')      { Whatever.count                     }
  bench.report('exception hit')  { raise StandardError.new rescue nil }
  bench.report('exception miss') { raise StandardError.new if false   }
  bench.compare!
end

ruby -v bench.rb runs:

ruby 2.1.5p273 (2014-11-13 revision 48405) [x86_64-linux]
Calculating -------------------------------------
           SQL query   153.000  i/100ms
       exception hit    18.223k i/100ms
      exception miss    37.730k i/100ms
-------------------------------------------------
           SQL query      1.603k (± 2.6%) i/s -      8.109k
       exception hit    573.842k (± 1.1%) i/s -      2.879M
      exception miss     10.039M (± 3.0%) i/s -     50.068M

Comparison:
      exception miss: 10039144.2 i/s
       exception hit:   573841.9 i/s - 17.49x slower
           SQL query:     1603.0 i/s - 6262.88x slower
ruby 2.2.0preview1 (2014-09-17 trunk 47616) [x86_64-linux]
Calculating -------------------------------------
           SQL query   154.000  i/100ms
       exception hit    18.144k i/100ms
      exception miss    37.680k i/100ms
-------------------------------------------------
           SQL query      1.569k (± 3.2%) i/s -      7.854k
       exception hit    610.828k (± 1.5%) i/s -      3.066M
      exception miss     10.329M (± 1.2%) i/s -     51.584M

Comparison:
      exception miss: 10328602.2 i/s
       exception hit:   610828.0 i/s - 16.91x slower
           SQL query:     1569.4 i/s - 6581.23x slower

@chastell
Copy link

chastell commented Nov 6, 2016

Just for kicks ;) I redid the above: ~two years later, on the same machine, with activerecord 5.0.0.1 and pg 0.19.0:

ruby 2.3.1p112 (2016-04-26 revision 54768) [x86_64-linux]
Warming up --------------------------------------
           SQL query   353.000  i/100ms
       exception hit    62.811k i/100ms
      exception miss   297.570k i/100ms
Calculating -------------------------------------
           SQL query      2.921k (±32.9%) i/s -     13.061k in   5.052108s
       exception hit    831.142k (± 3.2%) i/s -      4.208M in   5.068312s
      exception miss     10.467M (± 3.2%) i/s -     52.372M in   5.008549s

Comparison:
      exception miss: 10466869.0 i/s
       exception hit:   831142.0 i/s - 12.59x slower
           SQL query:     2921.4 i/s - 3582.81x slower
ruby 2.4.0preview2 (2016-09-09 trunk 56129) [x86_64-linux]
Warming up --------------------------------------
           SQL query   262.000  i/100ms
       exception hit    62.278k i/100ms
      exception miss   344.852k i/100ms
Calculating -------------------------------------
           SQL query      3.306k (±24.1%) i/s -     15.458k in   5.080964s
       exception hit    871.144k (± 0.9%) i/s -      4.359M in   5.004697s
      exception miss     11.577M (± 1.1%) i/s -     57.935M in   5.005123s

Comparison:
      exception miss: 11576670.6 i/s
       exception hit:   871144.2 i/s - 13.29x  slower
           SQL query:     3306.0 i/s - 3501.78x  slower

@chastell
Copy link

chastell commented Nov 6, 2016

Meanwhile @andrzejkrzywda duly pointed out that comparing exception handling time with writes (rather than – especially easily cacheable – reads) would be much more telling, so here’s the comparison with Whatever.create(text: 'meh') instead of Whatever.count:

ruby 2.3.1p112 (2016-04-26 revision 54768) [x86_64-linux]
Warming up --------------------------------------
           SQL query    18.000  i/100ms
       exception hit    57.640k i/100ms
      exception miss   269.546k i/100ms
Calculating -------------------------------------
           SQL query    182.124  (±15.9%) i/s -    882.000  in   5.003193s
       exception hit    808.613k (± 4.8%) i/s -      4.035M in   5.004163s
      exception miss     10.129M (± 3.2%) i/s -     50.675M in   5.007747s

Comparison:
      exception miss: 10129279.5 i/s
       exception hit:   808613.1 i/s - 12.53x slower
           SQL query:      182.1 i/s - 55617.43x slower
ruby 2.4.0preview2 (2016-09-09 trunk 56129) [x86_64-linux]
Warming up --------------------------------------
           SQL query    20.000  i/100ms
       exception hit    58.768k i/100ms
      exception miss   344.645k i/100ms
Calculating -------------------------------------
           SQL query    186.218  (±20.4%) i/s -    880.000  in   5.005586s
       exception hit    818.088k (± 6.9%) i/s -      4.114M in   5.069060s
      exception miss     11.347M (± 0.6%) i/s -     56.866M in   5.011838s

Comparison:
      exception miss: 11346771.3 i/s
       exception hit:   818087.5 i/s - 13.87x  slower
           SQL query:      186.2 i/s - 60932.79x  slower

@dpneumo
Copy link

dpneumo commented Nov 15, 2016

For me the take home is that when a (SQL) database is involved efforts to optimize the queries has a greater chance of improving app performance than futzing with flow control techniques. Not a surprise but it's nice to have some real numbers to point to. Thanks.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment