许多行SQLite的更新速度非常慢 [英] SQLite update of many rows is very slow

问题描述

我相信我已经用许多方法来加快许多行的UPDATE， 但到目前为止，没有任何帮助。

I believe I have used many ways to speed up the UPDATE of many rows, But nothing helped so far.

我们打开一个交易， 像这样的：

We are opening a transaction, like this:

private SQLiteTransaction BeginTransaction(SQLiteConnection connection)
{
    return connection.BeginTransaction();
}

和关闭它在过程的结束。 我们打​​开一个连接，一个事务，然后我们更新数据库的多行。 这意味着我们累积许多不同的SQL语句进行很久了，几桌。

and close it at the end of course. We open one connection, one transaction, and then we update many rows on the database. This means we are cumulate many different SQL statements over quite a few tables .

其中很大一部分是12000条记录在一个表中更新

One big part is the update of 12000 records in one table

protected override void UpdateRows(SQLiteConnection connection, IEnumerable<DataRow> rowsToUpdate)     
{
    var command = new SQLiteCommand(Queries.SQLUpdateDocument, connection);

        foreach (DataRow documentRow in rowsToUpdate)
        {
               command.Parameters.AddWithValue("@Filename", documentRow[Constants.Col_Document_Filename]);
               command.Parameters.AddWithValue("@ClassID", documentRow[Constants.Col_Document_ClassID]);
               command.Parameters.AddWithValue("@PageCount", documentRow[Constants.Col_Document_PageCount]);
               command.Parameters.AddWithValue("@DocID", documentRow[Constants.Col_Document_GlobalDocID]);
               command.Parameters.AddWithValue("@ReadOnly", documentRow[Constants.Col_Document_ReadOnly]);
               command.Parameters.AddWithValue("@Confirmed", documentRow[Constants.Col_Document_Confirmed]);
               command.Parameters.AddWithValue("@ParentFolderID", documentRow[Constants.Col_Document_ParentFolderID]);
               command.Parameters.AddWithValue("@SequenceNumber", documentRow[Constants.Col_Document_SequenceNumber]);
               command.Parameters.AddWithValue("@XmlRepr", documentRow[Constants.Col_Document_XmlRepr]);

               command.ExecuteNonQuery();

               documentRow.AcceptChanges();
    }
}

与查询beeing：

with the query beeing:

UPDATE T_Doc SET
Filename = @Filename,
ClassID = @ClassID,
PageCount = @PageCount,
ReadOnly = @ReadOnly,
Confirmed = @Confirmed,
ParentFolderID = @ParentFolderID,
SequenceNumber = @SequenceNumber,
XmlRepr = @XmlRepr
WHERE ID = @DocID;

在的AcceptChanges（）并不需要很长时间。 为ExecuteNonQuery（）是远远低于预期。

the AcceptChanges() does not take long. The ExecuteNonQuery() is much slower than expected.

PRAGMA foreign_keys = ON;

已更新命令的ClassID和ParentFolderID参考其他表之前执行的。

has been executed before the update command and ClassID and ParentFolderID reference other tables.

不管怎样 - 更新非常慢，更新12000条记录需要像15-30分钟

Anyways - the update is extremely slow, updating 12000 records takes like 15-30 minutes.

任何人可以帮助我呢？

推荐答案

我怀疑你不是叫的BeginTransaction ，或者你不与相关联的命令交易......如果没有明确指定交易，新创建隐含每次执行一个命令。

I suspect you're not calling BeginTransaction, or perhaps you're not associating the command with the transaction... If a transaction is not specified explicitly, a new one is created implicitly every time you execute a command.

试试这个：

protected override void UpdateRows(SQLiteConnection connection, IEnumerable<DataRow> rowsToUpdate)     
{
    using (var command = new SQLiteCommand(Queries.SQLUpdateDocument, connection))
    using (var transaction = connection.BeginTransaction())
    {
        command.Transaction = transaction;

        foreach (DataRow documentRow in rowsToUpdate)
        {
               command.Parameters.AddWithValue("@Filename", documentRow[Constants.Col_Document_Filename]);
               command.Parameters.AddWithValue("@ClassID", documentRow[Constants.Col_Document_ClassID]);
               command.Parameters.AddWithValue("@PageCount", documentRow[Constants.Col_Document_PageCount]);
               command.Parameters.AddWithValue("@DocID", documentRow[Constants.Col_Document_GlobalDocID]);
               command.Parameters.AddWithValue("@ReadOnly", documentRow[Constants.Col_Document_ReadOnly]);
               command.Parameters.AddWithValue("@Confirmed", documentRow[Constants.Col_Document_Confirmed]);
               command.Parameters.AddWithValue("@ParentFolderID", documentRow[Constants.Col_Document_ParentFolderID]);
               command.Parameters.AddWithValue("@SequenceNumber", documentRow[Constants.Col_Document_SequenceNumber]);
               command.Parameters.AddWithValue("@XmlRepr", documentRow[Constants.Col_Document_XmlRepr]);

               command.ExecuteNonQuery();

               documentRow.AcceptChanges();
        }

        transaction.Commit();
    }
}

请注意，如果你有一个非常大的行，你可能要提交之前，你有更新的一切，如每1000行。在这种情况下，你需要在提交previous一前一后，以创建一个新的事务。

Note that if you have a very large number of rows, you might want to commit before you have updated everything, e.g. every 1000 rows. In that case you need to create a new transaction after committing the previous one.

这篇关于许多行SQLite的更新速度非常慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！