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.
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:
I was informed that
Different machine environments require different time, but
trans from bulk insert: 243,755msIt 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
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");
}
© 2024 OneMinuteCode. All rights reserved.