【WPF】SQLiteのインサートが遅いので速度アップの方法を試してみる

2017年11月14日2019年7月3日C#,SQLite,開発

おはようございます。

SQLiteって手軽で便利なんですが、
大量のデータを扱うとなるとそれなりに速度が気になってきます。

試しに何万件のインサート処理をやってみたら案の定とても待ち切れる時間で処理が終わらなかったのでちょっと調べてみました。

プログラムは次の記事のものを流用します。

【WPF】TextFieldParser で CSVファイルを読み込む | ドラブロ – let bygones b

Microsoft.VisualBasic.FileIO の「TextFieldParser」を使ってサクッとCSVファイルを読み込み、SQLite ...

https://www.doraxdora.com/blog/2017/06/29/post-1354/

1. 元の処理
2. プラグマステートメントを設定
3. 明示的にトランザクションを開始する
4. Sqlコマンドに変更する
5. まとめ

元の処理

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();
        }

/// <summary>

/// CSV読込ボタンクリックイベント.

/// </summary>

/// <param name="sender"></param>

/// <param name="e"></param>

privatevoidimp_button_Click(objectsender,RoutedEventArgse)

{

OpenFileDialog ofd=newOpenFileDialog();

ofd.FileName="";

ofd.DefaultExt="*.csv";

if(ofd.ShowDialog()==false)

{

return;

}

List<Cat>list=readFile(ofd.FileName);

// 接続

intcount=0;

// データを追加する

using(varconn=newSQLiteConnection("Data Source=SampleDb.sqlite"))

{

using(DataContext context=newDataContext(conn))

{

foreach(Cat cat inlist)

{

// 対象のテーブルオブジェクトを取得

vartable=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();
            }

using(varconn=newSQLiteConnection("Data Source=SampleDb.sqlite;version=3;synchronous=Normal;journal mode=Wal"))

{

using(DataContext context=newDataContext(conn))

{

foreach(Cat cat inlist)

{

// 対象のテーブルオブジェクトを取得

vartable=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();
                }

using(varconn=newSQLiteConnection("Data Source=SampleDb.sqlite;version=3;synchronous=Normal;journal mode=Wal"))

{

using(DataContext context=newDataContext(conn))

{

using(varts=conn.BeginTransaction())

{

foreach(Cat cat inlist)

{

// 対象のテーブルオブジェクトを取得

vartable=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();
            }

using(varconn=newSQLiteConnection("Data Source=SampleDb.sqlite;version=3;synchronous=Normal;journal mode=Wal"))

{

using(DataContext context=newDataContext(conn))

{

using(varts=conn.BeginTransaction())

{

foreach(Cat cat inlist)

{

using(SQLiteCommand cmd=conn.CreateCommand())

{

// 対象のテーブルオブジェクトを取得

vartable=context.GetTable<Cat>();

// データが存在するかどうか判定

if(table.SingleOrDefault(x=>x.No==cat.No)==null)

{

Stringsql=@"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();

}

こちらも勿論速度アップしました。

まとめ

ちょっと殴り書きみたいな記事ですが、とりあえず上記のような対策を施すとひとまず何万件のインサートも問題なく実行できるかと思います。

もっと劇的に速度アップさせる必要がある場合は、
登録するデータの存在確認をしないようにするといいと思います。
（この場合はテーブル定義や、別で一時テーブルを用意するなど別途検討が必要となります）

ではでは。

2017年11月14日2019年7月3日C#,SQLite,開発C#,SQLite,WPF,プログラミング

Posted by doradora