【WPF】SQLiteのインサートが遅いので速度アップの方法を試してみる
SQLiteって手軽で便利なんですが、
大量のデータを扱うとなるとそれなりに速度が気になってきます。
試しに何万件のインサート処理をやってみたら案の定とても待ち切れる時間で処理が終わらなかったのでちょっと調べてみました。
プログラムは次の記事のものを流用します。
【WPF】TextFieldParser で CSVファイルを読み込む
元の処理
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 ライブラリの動作を変更するためのものです。）
次の記事に色々と詳しく書いてありますので参考にしてみてください。
参考 : http://devlights.hatenablog.com/entry/2014/02/01/151642
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();
}
こちらも勿論速度アップしました。
まとめ
ちょっと殴り書きみたいな記事ですが、とりあえず上記のような対策を施すとひとまず何万件のインサートも問題なく実行できるかと思います。
もっと劇的に速度アップさせる必要がある場合は、
登録するデータの存在確認をしないようにするといいと思います。
（この場合はテーブル定義や、別で一時テーブルを用意するなど別途検討が必要となります）
ではでは。
IT関係の仕事をしています/1985年生まれの東京在住/便利なサービスやツール漁りや料理などが好き/2017年～ブログやってます/自分でサービスとか作ってリリースしたい/何かありましたらお気軽にDMどうぞ
Crieitは誰でも投稿できるサービスです。 是非記事の投稿をお願いします。どんな軽い内容でも投稿できます。
また、「こんな記事が読みたいけど見つからない！」という方は是非記事投稿リクエストボードへ！
こじんまりと作業ログやメモ、進捗を書き残しておきたい方はボード機能をご利用ください。
ボードとは？
コメント