bench/topic_perf.rb in mini_sql-0.1.10 vs bench/topic_perf.rb in mini_sql-0.2.1

- old
+ new

@@ -1,18 +1,19 @@ require 'bundler/inline' gemfile do source 'https://rubygems.org' - gem 'pg' + gem 'pg', github: 'ged/ruby-pg' gem 'mini_sql', path: '../' gem 'activesupport' gem 'activerecord' gem 'activemodel' gem 'memory_profiler' gem 'benchmark-ips' - gem 'sequel' - gem 'sequel_pg', require: 'sequel' + gem 'sequel', github: 'jeremyevans/sequel' + gem 'sequel_pg', github: 'jeremyevans/sequel_pg', require: 'sequel' + gem 'swift-db-postgres', github: 'deepfryed/swift-db-postgres' end require 'sequel' require 'active_record' require 'memory_profiler' @@ -129,13 +130,21 @@ def pg_title_id s = +"" # use the safe pattern here r = $conn.async_exec(-"select id, title from topics order by id limit 1000") - r.each do |row| - s << row["id"].to_s - s << row["title"] + + # this seems fastest despite extra arrays, cause array of arrays is generated + # in c code + values = r.values + + i = 0 + l = values.length + while i < l + s << values[i][0].to_s + s << values[i][1] + i += 1 end r.clear s end @@ -179,153 +188,158 @@ i += 2 end s end +# connects over unix socket +$swift = Swift::DB::Postgres.new(db: "test_db") + +def swift_select_title_id(l=1000) + s = "" + i = 0 + r = $swift.execute("select id, title from topics order by id limit 1000") + while i < r.selected_rows + s << r.get(i, 0).to_s + s << r.get(i, 1) + i += 1 + end + s +end + results = [ ar_title_id, ar_title_id_pluck, pg_title_id, mini_sql_title_id, sequel_pluck_title_id, sequel_select_title_id, - mini_sql_title_id_query_single + mini_sql_title_id_query_single, + swift_select_title_id ] exit(-1) unless results.uniq.length == 1 -def wide_topic_ar - Topic.first -end - -def wide_topic_pg - r = $conn.async_exec("select * from topics limit 1") - row = r.first - r.clear - row -end - -def wide_topic_sequel - TopicSequel.first -end - -def wide_topic_mini_sql - $conn.query("select * from topics limit 1").first -end - Benchmark.ips do |r| - r.report("wide topic ar") do |n| + r.report("ar select title id") do |n| while n > 0 - wide_topic_ar + ar_title_id n -= 1 end end - r.report("wide topic sequel") do |n| + r.report("ar select title id pluck") do |n| while n > 0 - wide_topic_sequel + ar_title_id_pluck n -= 1 end end - r.report("wide topic pg") do |n| + r.report("sequel title id select") do |n| while n > 0 - wide_topic_pg + sequel_select_title_id n -= 1 end end - r.report("wide topic mini sql") do |n| + r.report("pg select title id") do |n| while n > 0 - wide_topic_mini_sql + pg_title_id n -= 1 end end - r.compare! -end - - - -Benchmark.ips do |r| - r.report("ar select title id") do |n| + r.report("mini_sql select title id") do |n| while n > 0 - ar_title_id + mini_sql_title_id n -= 1 end end - r.report("ar select title id pluck") do |n| + r.report("sequel title id pluck") do |n| while n > 0 - ar_title_id_pluck + sequel_pluck_title_id n -= 1 end end - r.report("sequel title id select") do |n| + r.report("mini_sql query_single title id") do |n| while n > 0 - sequel_select_title_id + mini_sql_title_id_query_single n -= 1 end end - r.report("pg select title id") do |n| + r.report("swift title id") do |n| while n > 0 - pg_title_id + swift_select_title_id n -= 1 end end - r.report("mini_sql select title id") do |n| + r.compare! +end + + + +def wide_topic_ar + Topic.first +end + +def wide_topic_pg + r = $conn.async_exec("select * from topics limit 1") + row = r.first + r.clear + row +end + +def wide_topic_sequel + TopicSequel.first +end + +def wide_topic_mini_sql + $conn.query("select * from topics limit 1").first +end + +Benchmark.ips do |r| + r.report("wide topic ar") do |n| while n > 0 - mini_sql_title_id + wide_topic_ar n -= 1 end end - r.report("sequel title id pluck") do |n| + r.report("wide topic sequel") do |n| while n > 0 - sequel_pluck_title_id + wide_topic_sequel n -= 1 end end - r.report("mini_sql query_single title id") do |n| + r.report("wide topic pg") do |n| while n > 0 - mini_sql_title_id_query_single + wide_topic_pg n -= 1 end end + r.report("wide topic mini sql") do |n| + while n > 0 + wide_topic_mini_sql + n -= 1 + end + end r.compare! end -# Calculating ------------------------------------- -# wide topic ar 2.383k (± 4.9%) i/s - 12.005k in 5.050490s -# wide topic sequel 3.449k (± 3.2%) i/s - 17.591k in 5.104951s -# wide topic pg 7.345k (± 1.2%) i/s - 37.352k in 5.086015s -# wide topic mini sql 7.536k (± 1.4%) i/s - 38.220k in 5.072834s -# # Comparison: -# wide topic mini sql: 7535.8 i/s -# wide topic pg: 7345.1 i/s - same-ish: difference falls within error -# wide topic sequel: 3449.4 i/s - 2.18x slower -# wide topic ar: 2382.9 i/s - 3.16x slower +# pg select title id: 1519.7 i/s +# mini_sql query_single title id: 1335.0 i/s - 1.14x slower +# sequel title id pluck: 1261.6 i/s - 1.20x slower +# mini_sql select title id: 1188.6 i/s - 1.28x slower +# swift title id: 1077.5 i/s - 1.41x slower +# sequel title id select: 969.7 i/s - 1.57x slower +# ar select title id pluck: 738.7 i/s - 2.06x slower +# ar select title id: 149.6 i/s - 10.16x slower # -# Calculating ------------------------------------- -# ar select title id 131.572 (± 3.8%) i/s - 658.000 in 5.008231s -# ar select title id pluck -# 696.233 (± 3.7%) i/s - 3.519k in 5.061335s -# sequel title id select -# 916.841 (± 3.7%) i/s - 4.655k in 5.084499s -# pg select title id 1.002k (± 4.0%) i/s - 5.044k in 5.040584s -# mini_sql select title id -# 1.106k (± 2.4%) i/s - 5.618k in 5.084423s -# sequel title id pluck -# 1.181k (± 3.5%) i/s - 5.980k in 5.069815s -# mini_sql query_single title id -# 1.171k (± 3.1%) i/s - 5.880k in 5.025793s # # Comparison: -# sequel title id pluck: 1181.0 i/s -# mini_sql query_single title id: 1171.1 i/s - same-ish: difference falls within error -# mini_sql select title id: 1105.6 i/s - 1.07x slower -# pg select title id: 1002.2 i/s - 1.18x slower -# sequel title id select: 916.8 i/s - 1.29x slower -# ar select title id pluck: 696.2 i/s - 1.70x slower -# ar select title id: 131.6 i/s - 8.98x slower -# +# wide topic pg: 7474.0 i/s +# wide topic mini sql: 7355.2 i/s - same-ish: difference falls within error +# wide topic sequel: 5696.8 i/s - 1.31x slower +# wide topic ar: 2515.0 i/s - 2.97x slower + + # to run deep analysis run # MemoryProfiler.report do # ar # end.pretty_print