どー言うことか
SQLiteにおいて、たとえば
sqlite3:partial.sql
Id INTEGER PRIMARY KEY AUTOINCREMENT
と言う定義を行うと、あんまり効率がよろしくないというお話。
早速試してみた
使用した環境
1．C# 5.0
1．Dapper 1.38.0.0
1．System.Data.SQLite 1.0.94.0
テーブルスキーマ
は以下の通り。
AUTOINCREMENT指定したPrimary keyを利用したテーブル
CREATE TABLE AutoincrementTable (
Id INTEGER PRIMARY KEY AUTOINCREMENT,
Value INTEGER NOT NULL
);
AUTOINCREMENT指定してないPrimary Keyを利用したテーブル
CREATE TABLE RowIdTable (
Id INTEGER PRIMARY KEY,
Value INTEGER NOT NULL
);
テストコード
計測に用いたテストコードは以下の通り
using System;
using System.Data;
using System.Data.SQLite;
using System.Diagnostics;
using System.Linq;
using Dapper;
namespace ConsoleApplication2
{
internal class Program
{
private const int InsertCount = 1000000;
private const string FileName = "Test.db3";
private static void Main(string[] args)
{
CreateDataBase(FileName);
AutoincrementTable();
Console.WriteLine();
RowIdTable();
}
private static void AutoincrementTable()
{
Stopwatch chronograph = new Stopwatch();
using (SQLiteConnection conn = CreateConnection(FileName))
{
GcFullCollect();
chronograph.Start();
using (SQLiteTransaction tran = conn.BeginTransaction())
{
conn.Execute("INSERT INTO AutoIncrementTable(Value) VALUES(@value)",
Enumerable.Range(0, InsertCount).Select(x => new {Value = x}), tran);
tran.Commit();
}
chronograph.Stop();
}
Console.WriteLine("Autoincrement case:" + chronograph.Elapsed);
}
private static void RowIdTable()
{
Stopwatch chronograph = new Stopwatch();
using (SQLiteConnection conn = CreateConnection(FileName))
{
GcFullCollect();
chronograph.Start();
using (SQLiteTransaction tran = conn.BeginTransaction())
{
conn.Execute("INSERT INTO RowIdTable(Value) VALUES(@value)",
Enumerable.Range(0, InsertCount).Select(x => new {Value = x}), tran);
tran.Commit();
}
chronograph.Stop();
}
Console.WriteLine("RowId case:" + chronograph.Elapsed);
}
private static SQLiteConnection CreateConnection(string fileName)
{
SQLiteConnectionStringBuilder bld = new SQLiteConnectionStringBuilder
{
DataSource = fileName,
DefaultIsolationLevel = IsolationLevel.ReadCommitted
};
SQLiteConnection ret = new SQLiteConnection(bld.ToString());
ret.Open();
return ret;
}
private static void GcFullCollect()
{
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
}
private static void CreateDataBase(string filePath)
{
SQLiteConnectionStringBuilder bld = new SQLiteConnectionStringBuilder
{
DataSource = filePath,
DefaultIsolationLevel = IsolationLevel.ReadCommitted
};
SQLiteConnection.CreateFile(filePath);
using (SQLiteConnection conn = new SQLiteConnection(bld.ToString()))
{
conn.Execute("CREATE TABLE AutoIncrementTable(" +
"Id INTEGER PRIMARY KEY AUTOINCREMENT," +
"Value INTEGER NOT NULL);");
conn.Execute("CREATE TABLE RowIdTable(" +
"Id INTEGER PRIMARY KEY," +
"Value INTEGER NOT NULL);");
}
}
}
}
結果
上記のコードを、
1.AnyCpu
1.Release build
1.デバッガアタッチ無し
似て実行したところ、以下の結果となった。
- Autoincrementを使った場合：04.7823556Sec
- Autoincrementを使わなかった場合：03.6289869Sec
差はさして大きくはないにしろ、有意な差はついたんじゃないかと思います。
まとめ
ドキュメントを読むと、
The AUTOINCREMENT keyword imposes extra CPU, memory, disk space, and disk I/O overhead and should be avoided if not strictly needed. It is usually not needed.
とあるので、必要で無い限りは使うべきじゃないかと思います。
また、
On an INSERT, if the ROWID or INTEGER PRIMARY KEY column is not explicitly given a value, then it will be filled automatically with an unused integer, usually the one more than the largest ROWID currently in use. This is true regardless of whether or not the AUTOINCREMENT keyword is used.
このような記載があるので、Autoincrementを付与しなくとも、先ほどのコードのように、自動的に付番することは保証されていますから、DBMSサイドで付番したいだけであれば、AUTOINCREMENTを明記する必要は無いと言えるでしょう。
それでは、AUTOINCREMENTの有無で何が変わるかというと、AUTOINCREMENTがついていない場合、たとえばDELETEされたレコードのIdを再利用しますが、AUTOINCREMENTが付与されていた場合、任意のテーブルで一度利用された番号を、当該テーブルの新しいレコードのIdとしてデータベースのライフタイムに渡って再利用することがないと言う差があります。
もし、このように、一度利用したIdを確実に再利用しないことが必要な場合は、AUTOINCREMENTは有効ですが、それ以外の場合、意図的に使う必要は無いと思います。