

Unityで開発を行っております。

[code]

    create table hoge_view( hoge_id1 integer, hoge_id2 integer, hoge_id3 integer, hoge_id4 integer, hoge_id5 integer );
    
[/code]

といったテーブルを作成しました。  
こちらのテーブルに下記の構造体リストの値をインサートしたいと考えております。

[code]

    public class HogeData
    {
        public List<HogeValue>  value;
    }
    
    [Serializable]
    public class HogeValue
    {
        public int  hoge_id1;
        public int  hoge_id2;
        public int  hoge_id3;
        public int  hoge_id4;
        public int  hoge_id5;
    }
    
[/code]

こちらリスト数が4万ほどになる場合があり、insertコマンドをその分呼び出すのに大変時間が掛かります。  
リストをそのままinsertすることは可能なのでしょうか

何かアドバイスいただけたらと思います。  
よろしくお願い致します。


CC BY-SA 4.0
2020-12-24T21:27:40.057
72872
2022-02-22T00:13:44.550
29606
question
c# sqlite unity2d
SQLiteで構造体リストをそのままinsertしたい


自分の環境で計測してみましたが、SQLiteをSSDの環境で実行しても、  
1Insert当たり20~30ms程度かかるようです。  
問題はSQLの発行回数なので、例えば、

insert into hoge_view values(...),(...)

形式にすれば、SQL発行回数を減らせます。  
SQLiteでは40000value句そのまま対応できているみたいなので、１SQLで行けるようです。  
ただし、Value句が多くなりすぎると異常に時間がかかり始めるようなので、  
1000value句程度で分割すると最も早く処理できます。

下記サンプルを実行したところ、1.5秒程度でした。

[code]

    using System;
    using System.Collections.Generic;
    using System.Data.SQLite;
    using Dapper;
    
    ...
    
            static void Main(string[] args)
            {
                var length = 40000;
                var list = new List<HogeValue>();
                for (int i = 0; i < length; i++)
                {
                    var val = new HogeValue()
                    {
                        hoge_id1 = i * 10 + 1,
                        hoge_id2 = i * 10 + 2,
                        hoge_id3 = i * 10 + 3,
                        hoge_id4 = i * 10 + 4,
                        hoge_id5 = i * 10 + 5,
                    };
                    list.Add(val);
                }
    
                var sw = new System.Diagnostics.Stopwatch();
                using (var conn = new SQLiteConnection("Data Source=sqlitedb.db;"))
                {
                    conn.Open();
                    conn.Query("drop table hoge_view;");
                    conn.Query("create table hoge_view( hoge_id1 integer, hoge_id2 integer, hoge_id3 integer, hoge_id4 integer, hoge_id5 integer );");                
                    sw.Start();
                    var sqlpart1 = "insert into hoge_view values";
                    var sql = sqlpart1;
                    var counter = 0;
                    foreach (var i in list)
                    {
                        counter++;
                        sql += $@"({i.hoge_id1}, {i.hoge_id2}, {i.hoge_id3}, {i.hoge_id4}, {i.hoge_id5}),";
                        if (1000 <= counter)
                        {
                            sql = sql.Substring(0, sql.Length - 1);
                            conn.Query(sql);
                            counter = 0;
                            sql = sqlpart1;
                        }
                    }
                    if (counter != 0)
                    {
                        sql = sql.Substring(0, sql.Length - 1);
                        conn.Query(sql);
                        counter = 0;
                    }
                    
                }
                var ms = sw.ElapsedMilliseconds;
                Console.WriteLine($"{ms} ms, done!");
                Console.WriteLine("end");
            }
    
[/code]


CC BY-SA 4.0
2020-12-25T09:19:50.653
72885
2022-02-22T00:13:44.550
2022-02-22T00:13:44.550
3060
25396
72872
answer


[一括挿入](https://docs.microsoft.com/ja-jp/dotnet/standard/data/sqlite/bulk-
insert)によると

> SQLite には、データを一括挿入するための特別な方法はありません。 データの挿入または更新時に最適なパフォーマンスを得るには、以下を行ってください。
>
>   * トランザクションの使用。
>   * 同じパラメーター化コマンドの再利用。 後続の実行で、最初の実行のコンパイルを再利用します。
>

と案内されています。

マシン環境が異なるので所要時間が異なりますが、

  * takemori_kondoさんのコード： 402ms
  * 一括挿入： 230ms
  * 一括挿入から`tran`をコメントアウト： 243,755ms

と愚直にループしてもかなり高速になります。

[code]

    #if false
        foreach (var group in list.Select((hv, i) => (hv, i)).GroupBy(x => x.i / 1000))
        {
            using var cmd = conn.CreateCommand();
            var values = String.Join(", ", group.Select(x => $"({x.hv.hoge_id1}, {x.hv.hoge_id2}, {x.hv.hoge_id3}, {x.hv.hoge_id4}, {x.hv.hoge_id5})"));
            cmd.CommandText = $"insert into hoge_view values {values};";
            cmd.ExecuteNonQuery();
        }
    #else
        using (var tran = conn.BeginTransaction())
        using (var cmd = conn.CreateCommand())
        {
            cmd.CommandText = "insert into hoge_view values ($hoge_id1, $hoge_id2, $hoge_id3, $hoge_id4, $hoge_id5);";
            var hoge_id1 = cmd.CreateParameter(); hoge_id1.ParameterName = nameof(hoge_id1); cmd.Parameters.Add(hoge_id1);
            var hoge_id2 = cmd.CreateParameter(); hoge_id2.ParameterName = nameof(hoge_id2); cmd.Parameters.Add(hoge_id2);
            var hoge_id3 = cmd.CreateParameter(); hoge_id3.ParameterName = nameof(hoge_id3); cmd.Parameters.Add(hoge_id3);
            var hoge_id4 = cmd.CreateParameter(); hoge_id4.ParameterName = nameof(hoge_id4); cmd.Parameters.Add(hoge_id4);
            var hoge_id5 = cmd.CreateParameter(); hoge_id5.ParameterName = nameof(hoge_id5); cmd.Parameters.Add(hoge_id5);
            foreach (var hv in list)
            {
                hoge_id1.Value = hv.hoge_id1;
                hoge_id2.Value = hv.hoge_id2;
                hoge_id3.Value = hv.hoge_id3;
                hoge_id4.Value = hv.hoge_id4;
                hoge_id5.Value = hv.hoge_id5;
                cmd.ExecuteNonQuery();
            }
            tran.Commit();
        }
    #endif
    
    
[/code]


CC BY-SA 4.0
2020-12-25T11:51:15.207
72888
2020-12-25T11:51:15.207
4236
72872
answer