C#とSQLiteの日時データ型とパフォーマンス

1年ほど前に手がけた仕事では、C#+Windows.Forms+Entity Framework+System.Data.SQLite という組み合わせでアプリケーションを作成しました。

次に始まる開発でもこの組み合わせにするつもりですが、いろいろとやりにくかった点をまとめておきたいと思います。まずは、SQLiteの日時カラムについて。

SQLite3には日時を表す内部データ表現が存在せず、代わりにTEXTやNumericなどのカラムによくある日付の表現（unixepoch, julianday, iso8601, C#のTicksなどなんでもいい）で入力します。取り出すときは組み込みのdatetime関数などを使ったり、アプリケーションで変換してDateTimeオブジェクトや人の読めるStringにフォーマットしたりして使います。

System.Data.SQLite.dll を使うときは、DATETIME型で定義したカラムについては接続文字列で正しいDateTimeFormatを指定することで、自動変換機能が使えるので、C#からはデータベースにどういうフォーマットで保存されているかを意識しなくてよくなります。

さて、どうせ内部表現が隠蔽できるのなら、精度が高く軽量な内部データにしたほうが速くて効率もいいだろうと思います。それで前の案件ではC#のTicks形式で保存するようにしていました。実際どれほど速くなったのかその時はよく調べませんでしたが、それよりもデバッグ時にTkSQLiteなどのGUIツールを使ってSQLiteデータベースを直接見ようとしたときに、Ticksから"yyyy-DD-mm HH:MM:SS"などのフォーマットに変換する手間が面倒くさくて、Ticksなんかにしなければよかったと後悔しました。実際の計算は以下のようになります。

SELECT datetime(m.created_at/pow(10,7) - 62135629200, 'unixepoch', 'localtime') AS created_at FROM ...;

秒以下の精度なんて必要ないので、最初から人間が読める形式にしたい。

それには DateTimeFormatを指定しないか、ISO8601 を使います。

それで、クエリが遅くなるのか調べてみました。
まず、テーブルを作成します。

CREATE TABLE log (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    msg TEXT,
    dt DATETIME,
    created_at DATETIME
);

インデックスなしの場合とありの場合でやります。

CREATE INDEX log_ix ON log (dt);

次に100万件INSERTします。

for (int cnt = 0; cnt < 1000000; cnt++)
{
    p_msg.Value = cnt;
    p_dt.Value = DateTime.Now;
    p_ca.Value = DateTime.Now;
    com.CommandText = "INSERT INTO log (msg,dt,created_at) VALUES (?, ?, ?)";
    com.ExecuteNonQuery();
}

最後にSELECTします。いったんid=50000の日時dtを取得し、その値にマッチするレコードを1件だけ取得しています。まあ、大体49000から50000件目の範囲のレコードがヒットしました。

INSERTとSELECTの時間をインデックスの有無、DateTimeFormatごとに計測します。

結果は以下の通り。

With index?	Query	Ticks	JulianDay	ISO8601
no	INSERT(100万件)	16.70 s	17.24 s	27.32 s
no	SELECT	182 ms	221 ms	303 ms
yes	INSERT(100万件)	22.91 s	25.11 s	37.16 s
yes	SELECT	<1ms	<1ms	<1ms

INSERTに関しては Ticks < JulianDay << ISO8601 となっていて、データ長が大きくなるISO8601は不利なようです。

SELECTに関しても同様ですが、インデックスありの場合の結果はいずれも1ms以下で十分速いようです。

私の用途の場合はいちどに大量のINSERTは必要なく、SELECTが高速なら事足りるので、次からはISO8601を使おうと思います。

VisualStudio2008 ソリューション
SQLite3BulkInsertTimestamp.zip

追加: データ型の話はもうちょっと複雑みたいです。
SQLiteのデータ型
自分のテーブルについても調べてみました。

SELECT typeof(id), typeof(msg), typeof(dt), typeof(created_at) FROM log LIMIT 1;

columns	Ticks	JulianDay	ISO8601
id	integer	integer	integer
msg	text	text	text
dt	integer	real	text
updated_at	integer	real	text

DATETIMEと宣言したものは実は全部TEXTに変換されているんじゃ、とか思いましたが、それぞれ異なっていますね。INSERTの値はADOのParameter経由で渡しているので、それぞれ適切なリテラルでSQL文を構成してくれているのだと思われます。ISO8601だけTEXT型で時間がかかるというのは納得いきました。

これも参考にした。
http://www.tamandua-webtools.net/sqlite3-date.html

I like Tcl/Tk.

C#とSQLiteの日時データ型とパフォーマンス

関連

コメントを残すコメントをキャンセル