【WPF】SQLiteのインサートが遅いので速度アップの方法を試してみる
おはようございます。
SQLiteって手軽で便利なんですが、
大量のデータを扱うとなるとそれなりに速度が気になってきます。
試しに何万件のインサート処理をやってみたら案の定とても待ち切れる時間で処理が終わらなかったのでちょっと調べてみました。
プログラムは次の記事のものを流用します。
スポンサーリンク
元の処理
CSVを読み込んで一括登録する処理を変更してみます。
MainWindow.xaml.cs
/// <summary>
/// CSV読込ボタンクリックイベント.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void imp_button_Click(object sender, RoutedEventArgs e)
{
OpenFileDialog ofd = new OpenFileDialog();
ofd.FileName = "";
ofd.DefaultExt = "*.csv";
if (ofd.ShowDialog() == false)
{
return;
}
List<Cat> list = readFile(ofd.FileName);
// 接続
int count = 0;
// データを追加する
using (var conn = new SQLiteConnection("Data Source=SampleDb.sqlite"))
{
using (DataContext context = new DataContext(conn))
{
foreach (Cat cat in list)
{
// 対象のテーブルオブジェクトを取得
var table = context.GetTable<Cat>();
// データが存在するかどうか判定
if (table.SingleOrDefault(x => x.No == cat.No) == null)
{
// データ追加
table.InsertOnSubmit(cat);
// DBの変更を確定
context.SubmitChanges();
count++;
}
}
}
conn.Close();
}
MessageBox.Show(count + " / " + list.Count + " 件 のデータを取り込みました。");
// データ再検索
searchData();
}
まずはプラグマというステートメントを設定してみます。
（プラグマステートメントとは、SQLite ライブラリの動作を変更するためのものです。）
次の記事に色々と詳しく書いてありますので参考にしてみてください。
using (var conn = new SQLiteConnection("Data Source=SampleDb.sqlite;version=3;synchronous=Normal;journal mode=Wal"))
{
using (DataContext context = new DataContext(conn))
{
foreach (Cat cat in list)
{
// 対象のテーブルオブジェクトを取得
var table = context.GetTable<Cat>();
// データが存在するかどうか判定
if (table.SingleOrDefault(x => x.No == cat.No) == null)
{
// データ追加
table.InsertOnSubmit(cat);
// DBの変更を確定
context.SubmitChanges();
count++;
}
}
}
conn.Close();
}
接続文字列にオプションを追加する方式でやってみました。
詳細に処理時間を載せませんが、処理速度の改善が見られました。
明示的にトランザクションを開始する
これは結構有名な話しですが、
一括処理する際にちゃんと明示的にトランザクションを制御しないと、
1件毎にそれなりに時間のかかるトランザクション処理が実行されてしまうので、明示的にトランザクションを開始するように変更します。
using (var conn = new SQLiteConnection("Data Source=SampleDb.sqlite;version=3;synchronous=Normal;journal mode=Wal"))
{
using (DataContext context = new DataContext(conn))
{
using (var ts = conn.BeginTransaction())
{
foreach (Cat cat in list)
{
// 対象のテーブルオブジェクトを取得
var table = context.GetTable<Cat>();
// データが存在するかどうか判定
if (table.SingleOrDefault(x => x.No == cat.No) == null)
{
// データ追加
table.InsertOnSubmit(cat);
// DBの変更を確定
context.SubmitChanges();
count++;
}
}
ts.Commit();
}
}
conn.Close();
}
これも処理速度アップとしては有効な手段でした。
Sqlコマンドに変更する
データコンテキストを使って更新や追加を行うのも結構時間がかかるようなので、SQLクエリを直書きして実行するようにしてみます。
using (var conn = new SQLiteConnection("Data Source=SampleDb.sqlite;version=3;synchronous=Normal;journal mode=Wal"))
{
using (DataContext context = new DataContext(conn))
{
using (var ts = conn.BeginTransaction())
{
foreach (Cat cat in list)
{
using (SQLiteCommand cmd = conn.CreateCommand())
{
// 対象のテーブルオブジェクトを取得
var table = context.GetTable<Cat>();
// データが存在するかどうか判定
if (table.SingleOrDefault(x => x.No == cat.No) == null)
{
String sql = @"INSERT INTO CAT (" +
" NO" +
", NAME" +
", SEX" +
", AGE" +
", KIND_CD" +
", FAVORITE" +
") VALUES (" +
cat.No +
"', '" + cat.Name +
"', '" + cat.Sex +
"', '" + cat.Age +
"', '" + cat.Kind +
"', '" + cat.Favorite + "')";
// データ追加
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
count++;
}
}
}
ts.Commit();
}
}
conn.Close();
}
こちらも勿論速度アップしました。
まとめ
ちょっと殴り書きみたいな記事ですが、とりあえず上記のような対策を施すとひとまず何万件のインサートも問題なく実行できるかと思います。
もっと劇的に速度アップさせる必要がある場合は、
登録するデータの存在確認をしないようにするといいと思います。
（この場合はテーブル定義や、別で一時テーブルを用意するなど別途検討が必要となります）
ではでは。