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