ruby + sqlite でinsertのベンチマークをとってみた part2

引き続き、insertのベンチマークをとってみました。
今回は、insert分を大文字で書くかどうか、カラム名称を正確に書くのか、小文字にして書くのかでの速度差を見てみました。

1. batchはexecute_batchコマンドのこと
2. small letter commandはinsertなど、SQL分を小文字で書いた場合
3. small letter column nameは、登録を例えばDateとしているところをdateと小文字で書いた場合
4. loop_countは繰り返しの回数
5. 遅い処理はループ回数を増やした際にはスキップ

早速結果ですが、

>loop_count = 10000
                                               user     system      total        real
insert(correct command/column name)        0.734000   0.016000   0.750000 (  0.751058)
insert(small letter command)               0.766000   0.000000   0.766000 (  0.768043)
insert(small letter column name)           0.734000   0.016000   0.750000 (  0.741058)
execute2(correct command/column name)      0.641000   0.016000   0.657000 (  0.657053)
execute2(small letter command)             0.640000   0.000000   0.640000 (  0.663052)
execute2(small letter column name)         0.656000   0.015000   0.671000 (  0.660053)
batch(correct command/column name)       149.125000  10.813000 159.938000 (162.591500)
batch(small letter command)              157.204000   5.844000 163.048000 (163.215535)
insert(small letter column name)         158.031000   4.860000 162.891000 (162.988520)


>loop_count = 100000
                                               user     system      total        real
insert(correct command/column name)        7.906000   0.094000   8.000000 (  8.127800)
insert(small letter command)               8.016000   0.047000   8.063000 (  8.172925)
insert(small letter column name)           7.921000   0.110000   8.031000 (  8.113041)
execute2(correct command/column name)      6.953000   0.078000   7.031000 (  7.136310)
execute2(small letter command)             6.938000   0.109000   7.047000 (  7.177266)
execute2(small letter column name)         6.953000   0.047000   7.000000 (  7.199874)

カラム文字列は正確に書いたほうが早いと思ってたんですが、有意な差は見られませんでした。
残念。

ソースも合わせて載せておきます。

require 'benchmark'
require 'rubygems'
require 'sqlite3'

db = SQLite3::Database.new("Inventory.db")
#使える型は、NULL、INTEGER、REAL、TEXT、BLOB
db.execute(<<EOS
  CREATE TABLE IF NOT EXISTS Item
    (
     ID   INTEGER PRIMARY KEY AUTOINCREMENT, 
     Code INTEGER UNIQUE NOT NULL,
     Name TEXT NOT NULL,
     Date TEXT NOT NULL
    );
EOS
)

loop_count = 10000
insert_code = []

loop_count.times{
  insert_code.push((0...20).map{ ('A'..'Z').to_a[rand(26)] }.join)
}

db.execute("delete from Item")

Benchmark.bm(40) do |x|
  
  #insert(correct command/column name)
  db.transaction
  x.report('insert(correct command/column name)'){
    loop_count.times{|i|
      db.execute("INSERT INTO Item (Code, Name, Date) VALUES (#{i}, '#{insert_code[i]}', '2014-08-03')")
    }
  }
  db.commit
  db.execute("DELETE FROM Item")
  
  #insert(small letter command)
  db.transaction
  x.report('insert(small letter command)'){
    loop_count.times{|i|
      db.execute("insert into item (Code, Name, Date) values (#{i}, '#{insert_code[i]}', '2014-08-03')")
    }
  }
  db.commit
  db.execute("DELETE FROM Item")
  
  #insert(small letter column name)
  db.transaction
  x.report('insert(small letter column name)'){
    loop_count.times{|i|
      db.execute("INSERT INTO Item (code, name, date) VALUES (#{i}, '#{insert_code[i]}', '2014-08-03')")
    }
  }
  db.commit
  db.execute("DELETE FROM Item")
  
  #execute2(correct command/column name)
  db.transaction
  x.report('execute2(correct command/column name)'){
    loop_count.times{|i|
      db.execute2("INSERT INTO Item (Code, Name, Date) VALUES (#{i}, '#{insert_code[i]}', '2014-08-03')")
    }
  }
  db.commit
  db.execute("DELETE FROM Item")
  
  #execute2(small letter command)
  db.transaction
  x.report('execute2(small letter command)'){
    loop_count.times{|i|
      db.execute2("insert into item (Code, Name, Date) values (#{i}, '#{insert_code[i]}', '2014-08-03')")
    }
  }
  db.commit
  db.execute("DELETE FROM Item")
  
  #execute2(small letter column name)
  db.transaction
  x.report('execute2(small letter column name)'){
    loop_count.times{|i|
      db.execute2("INSERT INTO Item (code, name, date) VALUES (#{i}, '#{insert_code[i]}', '2014-08-03')")
    }
  }
  db.commit
  db.execute("DELETE FROM Item")
  
  #batch(correct command/column name)
  db.transaction
  x.report('batch(correct command/column name)'){
  	batch_str = ""
    loop_count.times{|i|
    	batch_str <<= "INSERT INTO Item (Code, Name, Date) VALUES (#{i}, '#{insert_code[i]}', '2014-08-03');"
    }
    db.execute_batch(batch_str)
  }
  db.commit
  db.execute("DELETE FROM Item")
  
  #batch(small letter command)
  db.transaction
  x.report('batch(small letter command)'){
  	batch_str = ""
    loop_count.times{|i|
    	batch_str <<= "insert into item (Code, Name, Date) values (#{i}, '#{insert_code[i]}', '2014-08-03');"
    }
    db.execute_batch(batch_str)
  }
  db.commit
  db.execute("DELETE FROM Item")
  
  #batch(small letter column name)
  db.transaction
  x.report('insert(small letter column name)'){
  	batch_str = ""
    loop_count.times{|i|
    	batch_str <<= "INSERT INTO Item (code, name, date) VALUES (#{i}, '#{insert_code[i]}', '2014-08-03');"
    }
    db.execute_batch(batch_str)
  }
  db.commit
  db.execute("DELETE FROM Item")
  
end