如何批量插入SQLITE数据库? [英] How to bulk insert into SQLITE database?

问题描述

我正在开发UWP应用程序.

I am developing UWP application.

我有一个数据库，应该使用大约20,000条记录进行初始化.记录定义如下:

I have a database that should be initialized with about 20,000 records. The records, that are defined as follows:

private static readonly ObservableCollection<TickRecord> TickRecords = new ObservableCollection<TickRecord>();

我试图一次插入一条记录，如下所示:

I tried to insert the records one at a time like this:

private void CreateFakeTickRecords()
{
if ( Database.Database.CountTickRecords() > 0 )
    {
    return;
    }

foreach ( var tickRecord in TickRecords )
    {
    Database.Database.AddOrUpdateTickRecord( tickRecord );
    }
}

和

public static void AddOrUpdateTickRecord( TickRecord tickRecord )
{
    // Create a new connection
    using ( var db = new SQLiteConnection( new SQLitePlatformWinRT(), DbPath ) )
    {
    if ( tickRecord.Id == 0 )
        {
        // New
        db.Insert( tickRecord );
        }
    else
        {
        // Update
        db.Update( tickRecord );
        }
    }
}

此代码可以正常工作，但是它太慢了.

This code works fine, however it is way too slow.

我想对其进行修改，以便可以进行批量插入".

I would like to modify it so I can do a "Bulk Insert".

我该怎么做?

Thx

推荐答案

尝试使用InsertAll和UpdateAll函数.希望这可以一次打开数据库表，并一次插入/更新所有内容.您将需要提前确定要插入/更新的对象，但这仍然可以真正加快操作速度.

Try the InsertAll and UpdateAll functions. Hopefully this opens up the database table just once and inserts/updates everything at once. You will need to figure out which objects to insert/update ahead of time, but this should still really speed things up.

List<TickRecords> updates = new List<TickRecords>(); 

List<TickRecords> inserts = new List<TickRecords>();  

foreach ( var tickRecord in tickRecords ) 
{   
    if ( tickRecord.Id == 0 )
    {       
        updates.Add(tickRecord);
    }
    else
    {       
        inserts.Add(tickRecords);
     } 
}



using ( var db = new SQLiteConnection( new SQLitePlatformWinRT(), DbPath ) ) 
{
     db.InsertAll(inserts);
     db.UpdateAll(updates);
}

这篇关于如何批量插入SQLITE数据库?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！