I want SQLite to insert the structural list as it is

Asked 2 years ago, Updated 2 years ago, 103 views

We are developing at Unity.

create table hoge_view(hoge_id1 integer, hoge_id2 integer, hoge_id3 integer, hoge_id4 integer, hoge_id5 integer);

and so on.
I would like to insert the values of the structural list below into this table.

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

The number of lists here can be around 40000 and it takes a lot of time to call the insert command.
Is it possible to insert the list as it is?

Please give me some advice.
Thank you for your cooperation.

c# sqlite unity2d

2022-09-30 16:50

2 Answers

According to Bulk Insert,

SQLite does not have a special way to insert data in bulk. To get the best performance when inserting or updating data:

  • Using transactions.
  • Reuse the same parameterization command. Reuse the compilation of the first run in subsequent runs.

I was informed that

Different machine environments require different time, but

  • Takemori_kondo's code: 402ms
  • Insert Bulk: 230ms
  • Comment out trans from bulk insert: 243,755ms

It is quite fast even if you loop straight with .

#if false
    foreach(var group in list.Select((hv,i)=>(hv,i)) .GroupBy(x=>x.i/1000))
    {
        using varcmd=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(vartran=conn.BeginTransaction())
    using(varcmd=conn.CreateCommand())
    {
        cmd.CommandText="insert into hoge_view values ($hoge_id1, $hoge_id2, $hoge_id3, $hoge_id4, $hoge_id5);";;
        varhoge_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);
        varhoge_id4=cmd.CreateParameter(); hoge_id4.ParameterName=nameof(hoge_id4); cmd.Parameters.Add(hoge_id4);
        varhoge_id5=cmd.CreateParameter(); hoge_id5.ParameterName=nameof(hoge_id5); cmd.Parameters.Add(hoge_id5);
        foreach(var hvin 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


2022-09-30 16:50

I measured it in my environment, and even if I run SQLite in an SSD environment,
It seems to take about 20 to 30 ms per Insert.
The problem is the number of SQL issues, so for example,

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

Formatting reduces the number of SQL issues.
SQLite seems to be able to handle 40,000 value clauses as they are, so it seems that you can go with 1 SQL.
However, if there are too many Value clauses, it will start to take an unusually long time, so
Splitting in about 1000 value clauses is the fastest way to process it.

When I ran the sample below, it was about 1.5 seconds.

using System;
using System.Collections.General;
using System.Data.SQLite;
using Dapper;

...

        static void Main (string[]args)
        {
            var length = 40000;
            var list = new List <HogeValue>();;
            for (inti=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);
            }

            varsw = new System.Diagnostics.Stopwatch();
            using(var conn=new SQLiteConnection("Data Source=sqlitb.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();
                varsqlpart1 = "insert into hoge_view values";
                varsql = sqlpart1;
                var counter = 0;
                foreach (vari 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;
                }
                
            }
            varms=sw.ElappedMilliseconds;
            Console.WriteLine($"{ms}ms, done!");
            Console.WriteLine("end");
        }


2022-09-30 16:50

If you have any answers or tips


© 2024 OneMinuteCode. All rights reserved.