如何离线存储大量数据，然后批量更新为SQLite数据库？

Currently, I am trying to fill an SQLite database with tens of thousands of text data using this this method:

目前,我正在尝试使用此方法填充SQLite数据库中的数万个文本数据:

SQLiteConnection = new SQLiteConnection(cntnStr);
connection.Open();

foreach(Page p in pages)
{
     using (SQLiteCommand command = new SQLiteCommand(String.Format("Insert Into Pages (ID, Data, Name) Values ({0}, '{1}', '{2}')", id, p.Data, p.Name), connection))
         command.ExecuteNonQuery();
}

However, I suspect that doing this about 10 times per second is probably slowing the whole process down. Is there a way I can collate the data in memory and then add every 5000 records or so into the database in batch (so it is faster)?

但是,我怀疑每秒这样做大约10次可能会减慢整个过程。有没有办法可以整理内存中的数据,然后将每5000条记录左右批量添加到数据库中(这样会更快)?

EDIT: Super-important: Make sure you perform all your SQL commands within a DbTransaction - in this case an SQLiteTransaction:

编辑:超级重要:确保在DbTransaction中执行所有SQL命令 - 在本例中为SQLiteTransaction:

SQLiteTransaction trans = connection.BeginTransaction();

// SQL centric code - repeated inserts/changes

trans.Commit(); // adds your changes

It improves performance by 1000x.

它将性能提高了1000倍。

3 个解决方案

#1

Use a parameterized query, instead of building the query using string concatenation :

使用参数化查询,而不是使用字符串连接构建查询:

using (SQLiteConnection = new SQLiteConnection(cntnStr))
{
    connection.Open();

    string query = "Insert Into Pages (ID, Data, Name) Values (?, ?, ?)";
    using (SQLiteCommand command = new SQLiteCommand(query, connection)
    {
        command.Parameters.Add("id", DbType.Int32);
        command.Parameters.Add("data", DbType.String);
        command.Parameters.Add("name", DbType.String);
        foreach(Page p in pages)
        {
             command.Parameters["id"].Value = p.Id;
             command.Parameters["data"].Value = p.Data;
             command.Parameters["name"].Value = p.Name;
             command.ExecuteNonQuery();
        }
    }
}

This will be faster because the DbCommand is only created once, and the query is only parsed once. Also, you avoid the risks of SQL injection due to the string concatenation

这将更快,因为DbCommand仅创建一次,并且查询仅被解析一次。此外,您可以避免由于字符串连接而导致SQL注入的风险

BTW, have a look at this article by Robert Simpson (author of the SQLite .NET provider)

BTW,看看Robert Simpson撰写的这篇文章(SQLite .NET提供者的作者)

#2

You will have to handle escaping the data yourself, but you can use a batch insert.

您必须自己处理数据转义,但您可以使用批量插入。

IE:

Insert Into Pages (ID, Data, Name) Values (...),(...),(...),(...)

#3

You could load the table from the SQLite DB into a DataTable-Object, then insert your records into the DataTable-Object and sync it back every 5000 records to the DB.

您可以将SQLite DB中的表加载到DataTable-Object中,然后将记录插入DataTable-Object,并将每5000条记录同步回DB。

#1