いろいろ備忘録日記

主に .NET とか Go とか Flutter とか Python絡みのメモを公開しています。

SQLiteの書き込み速度をアップさせる (System.Data.SQLite, SyncMode, JournalMode, PRAGMA)

C# java

忘れないようメモメモ。
SQLiteさんは、そもそも速度が速いのですが書き込み速度をさらにアップさせたい時があります。
(データがもの凄い速さで流れてきて、それを出来るだけ遅延なく書き込みしたいときとか)
書き込み速度をアップさせるのに、すぐ適用できるのがPRAGMAを設定することです。
以下のPRAGMAを設定するとグッと速度が変わります。

  • SyncMode
  • JournalMode

デフォルトは、System.Data.SQLiteの場合

  • SyncMode=Normal
  • JournalMode=Default

です。これを

  • SyncMode=Off
  • JournalMode=Memory

にすると、爆速になります。または

  • SyncMode=Off
  • JournalMode=Wal

にしても同じくらい速くなります。SyncModeはNormalのままがいい場合は

  • SyncMode=Normal
  • JournalMode=Wal

が最もベターかと思います。
以下、サンプルです。100回書き込みして、かつ、毎回トランザクションをコミットしてます。

SQLiteの書き込み速度アップ (SyncMode, JournalMode)

以下、私の環境 (Windows Server 2012, 4プロセッサ, 4GBメモリ)で走らせた場合です。
上記のプログラムで、SyncMode=Normal, JournalMode=Defaultの場合

        ・・・省略
        90: 6964 microseconds
        91: 6368 microseconds
        92: 6534 microseconds
        93: 7768 microseconds
        94: 6894 microseconds
        95: 6071 microseconds
        96: 6155 microseconds
        97: 7119 microseconds
        98: 6403 microseconds
        99: 6185 microseconds
TOTAL: 733 milliseconds
ConnectionString: data source=test.db;version=3;legacy format=False;synchronous=Normal;journal mode=Default

SyncMode=Off, JournalMode=Walの場合

        ・・・省略
        90: 120 microseconds
        91: 235 microseconds
        92: 134 microseconds
        93: 121 microseconds
        94: 158 microseconds
        95: 114 microseconds
        96: 159 microseconds
        97: 125 microseconds
        98: 131 microseconds
        99: 129 microseconds
TOTAL: 74 milliseconds
ConnectionString: data source=test.db;version=3;legacy format=False;synchronous=Off;journal mode=Wal

SyncMode=Off, JournalMode=Memoryの場合

        ・・・省略
        90: 149 microseconds
        91: 191 microseconds
        92: 128 microseconds
        93: 122 microseconds
        94: 125 microseconds
        95: 119 microseconds
        96: 130 microseconds
        97: 153 microseconds
        98: 137 microseconds
        99: 120 microseconds
TOTAL: 77 milliseconds
ConnectionString: data source=test.db;version=3;legacy format=False;synchronous=Off;journal mode=Memory

SyncMode=Normal, JournalMode=Walの場合

        ・・・省略
        90: 133 microseconds
        91: 155 microseconds
        92: 120 microseconds
        93: 104 microseconds
        94: 110 microseconds
        95: 135 microseconds
        96: 116 microseconds
        97: 128 microseconds
        98: 113 microseconds
        99: 108 microseconds
TOTAL: 87 milliseconds
ConnectionString: data source=test.db;version=3;legacy format=False;synchronous=Normal;journal mode=Wal

SyncMode=Normal, JournalMode=Memoryの場合

        ・・・省略
        90: 1207 microseconds
        91: 1181 microseconds
        92: 1369 microseconds
        93: 1450 microseconds
        94: 1732 microseconds
        95: 1514 microseconds
        96: 1413 microseconds
        97: 1345 microseconds
        98: 1317 microseconds
        99: 1361 microseconds
TOTAL: 225 milliseconds
ConnectionString: data source=test.db;version=3;legacy format=False;synchronous=Normal;journal mode=Memory

どの場合でも、WAL (write ahead log)がいいパフォーマンスを出してくれてます。
Memoryの場合は、SyncModeをOffにしていないとパフォーマンスが出ません。

JavaでSQLiteを利用する場合でも、同じです。
jdbcの場合は、コネクションをオープンした直後にPRAGMA文を実行します。

conn.prepareStatement("pragma sync_mode=off").executeQuery();
conn.prepareStatement("pragma journal_mode=wal").executeQuery();

PRAGMAについては、以下の場所に記載されています。

過去の記事については、以下のページからご参照下さい。

サンプルコードは、以下の場所で公開しています。