Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
SQLiteの書き込み速度アップ (SyncMode, JournalMode)
namespace SqlitePerformance
{
using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Diagnostics;
using System.Linq;
using Dapper;
class Program
{
static void Main(string[] args)
{
var prg = new Program();
prg.Go();
Console.WriteLine("Press any key to exit...");
Console.ReadKey();
}
public void Go()
{
//
// PRAGMAの以下の項目の設定次第で書き込み速度が変わる。
// ・SyncMode
// ・JournalMode
// SyncMode=OFF, JournalMode=Memoryの組合せが一番速い.
// ただし、SyncMode=OFFにすると同期を取らなくなるので
// そこが心配な場合は、SyncMode=Normal, JournalMode=Walの
// 組合せが速度も速くてベター。
//
// Walが選択できるのは、バージョンが3.7.0以降となる。
//
var builder = new SQLiteConnectionStringBuilder()
{
DataSource = "test.db",
Version = 3,
LegacyFormat = false,
SyncMode = SynchronizationModes.Off,
JournalMode = SQLiteJournalModeEnum.Memory
};
using (var conn = new SQLiteConnection(builder.ToString()))
{
conn.Open();
try { conn.Execute("drop table table1"); } catch {}
try { conn.Execute("create table table1 (id integer primary key, name text)"); } catch {}
var totalWatch = Stopwatch.StartNew();
for (int i = 0; i < 100; i++)
{
using (var tran = conn.BeginTransaction())
{
var watch = Stopwatch.StartNew();
conn.Execute(string.Format("insert into table1 values ({0}, 'name-{1}')", i, i));
tran.Commit();
watch.Stop();
Console.WriteLine("\t{0}: {1} microseconds", i, watch.ElapsedMicroseconds());
}
}
totalWatch.Stop();
Console.WriteLine("TOTAL: {0} milliseconds", totalWatch.ElapsedMilliseconds);
Console.WriteLine("ConnectionString: {0}", builder.ToString());
}
}
}
public static class StopwatchExtensions
{
public static long ElapsedMicroseconds(this Stopwatch self)
{
return (self.ElapsedTicks * (1000 * 1000) / Stopwatch.Frequency);
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.