SQLiteの高速化とトランザクション

ruby

sqlite

SQLiteはファイルベースだとINSERTがあまり速くないなと思っていたのですが、ググってみると大量にINSERTを発行したい場合は明示的にトランザクションを使うというのが定番のテクニックだということがわかって愕然としました。SQLiteはAUTOCOMMITだとトランザクションが発生しない訳ではなくて、内部的にはINSERTの度にBEGINとCOMMITが発行されてしまうような仕組みなんでしょうかね。

ファイルベースのベンチマーク (INSERT*100回)

$ ./test.rb
                user     system      total        real
AUTOCOMMIT   0.000000   0.010000   0.010000 ( 16.499489)
TRANSACTION  0.010000   0.000000   0.010000 (  0.199884)

インメモリのベンチマーク (INSERT*100000回)

$ ./test2.rb
                user     system      total        real
AUTOCOMMIT   3.150000   0.350000   3.500000 (  3.508833)
TRANSACTION  3.090000   0.340000   3.430000 (  3.444818)

確かにベンチマークを取ってみると、全然速度が違いますね。インメモリの場合もほんのわずかだけ明示的にトランザクションを指定した方が速いようです。同じマシンでもファイルベースとインメモリでインサート速度が劇的に違うところもなかなか興味深いです。

† ファイル利用版ベンチマーク

#!/usr/bin/ruby

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

db0 = SQLite3::Database.new('0.db')
db0.execute(<<EOS)
  CREATE TABLE test (
    id varchar(255)
  )
EOS
db1 = SQLite3::Database.new('1.db')
db1.execute(<<EOS)
  CREATE TABLE test (
    id varchar(255)
  )
EOS

stmt0 = db0.prepare(<<EOS)
  INSERT INTO test(id) VALUES(:id)
EOS
stmt1 = db1.prepare(<<EOS)
  INSERT INTO test(id) VALUES(:id)
EOS


Benchmark.bm(10) do |x|
  x.report('AUTOCOMMIT ') {
    100.times{ stmt0.execute( :id => "hogehogehogehoge" ) }
  }
  x.report('TRANSACTION') {
    db1.transaction do
      100.times{ stmt1.execute( :id => "hogehogehogehoge" ) }
    end
  }
end

stmt0.close
db0.close
stmt1.close
db1.close

† インメモリ版ベンチマーク

$ diff -u test.rb test2.rb
--- test.rb     2010-03-08 20:45:15.000000000 +0900
+++ test2.rb    2010-03-08 20:47:52.000000000 +0900
@@ -4,13 +4,13 @@
 require 'rubygems'
 require 'sqlite3'

-db0 = SQLite3::Database.new('0.db')
+db0 = SQLite3::Database.new(':memory:')
 db0.execute(<<EOS)
   CREATE TABLE test (
     id varchar(255)
   )
 EOS
-db1 = SQLite3::Database.new('1.db')
+db1 = SQLite3::Database.new(':memory:')
 db1.execute(<<EOS)
   CREATE TABLE test (
     id varchar(255)
@@ -27,11 +27,11 @@

 Benchmark.bm(10) do |x|
   x.report('AUTOCOMMIT ') {
-    100.times{ stmt0.execute( :id => "hogehogehogehoge" ) }
+    100000.times{ stmt0.execute( :id => "hogehogehogehoge" ) }
   }
   x.report('TRANSACTION') {
     db1.transaction do
-      100.times{ stmt1.execute( :id => "hogehogehogehoge" ) }
+      100000.times{ stmt1.execute( :id => "hogehogehogehoge" ) }
     end
   }
 end