SQLite .NET 性能，如何加快速度? [英] SQLite .NET performance, how to speed up things?

问题描述

在我的系统上，大约 86000 次 SQLite 插入需要长达 20 分钟，这意味着每秒大约有 70 次插入.我要做几百万，我怎样才能加快速度?在 SQLiteConnection 对象上为每一行调用 Open() 和 Close() 会降低性能吗?交易有帮助吗?

On my system, ~86000 SQLite insertions took up to 20 minutes, means ~70 insertions per second. I have to do millions, how can I speed up it? Calling Open() and Close() on SQLiteConnection object for every line can slow down performance? Can transactions help?

单行的典型插入方法:

    public int InsertResultItem(string runTag, int topicId,
        string documentNumber, int rank, double score)
    {
        // Apre la connessione e imposta il comando
        connection.Open();

        command.CommandText = "INSERT OR IGNORE INTO Result "
          + "(RunTag, TopicId, DocumentNumber, Rank, Score) " +
            "VALUES (@RunTag, @TopicId, @DocumentNumber, @Rank, @Score)";

        // Imposta i parametri
        command.Parameters.AddWithValue("@RunTag", runTag);
        command.Parameters.AddWithValue("@TopicId", topicId);
        command.Parameters.AddWithValue("@DocumentNumber", documentNumber);
        command.Parameters.AddWithValue("@Rank", rank);
        command.Parameters.AddWithValue("@Score", score);

        // Ottieni il risultato e chiudi la connessione
        int retval = command.ExecuteNonQuery();
        connection.Close();

        return retval;
    }

如您所见，插入非常简单.

As you can see, insertions are very simple ones.

推荐答案

您肯定需要一个事务.如果不这样做，SQLite 会为每个插入命令启动自己的事务，因此您可以按原样有效地执行 86000 个事务.

You definitely need a transaction. If you don't, SQLite starts its own transaction for every insert command so you're effectively doing 86000 transactions as is.

看起来您每次也在打开和关闭连接，并每次都重置 CommandText.这是不必要的，无疑会减慢您的速度，如果您这样做，它会更快:

It looks you're also opening and closing the connection each time, along with resetting the CommandText each time. This is unnecessary and doubtless slowing you down, it'll go much faster if you:

• 打开连接一次
• 构建命令一次，向其添加参数一次.
• 开始交易
• 循环，仅在调用 ExecuteNonQuery 之前更改参数值
• 提交交易.
• 关闭连接.

我认为您可以通过这种方式将 20 分钟缩短到几秒钟.

I think you could reduce your 20 minutes down to just a few seconds this way.

这就是我的意思:

public void InsertItems()
{
    SQLiteConnection connection  = new SQLiteConnection(SomeConnectionString);
    SQLiteCommand command = connection.CreateCommand();
    SQLiteTransaction transaction = connection.BeginTransaction();

    command.CommandText = "INSERT OR IGNORE INTO Result "
+ "(RunTag, TopicId, DocumentNumber, Rank, Score) " +
  "VALUES (@RunTag, @TopicId, @DocumentNumber, @Rank, @Score)";

    command.Parameters.AddWithValue("@RunTag", "");
    command.Parameters.AddWithValue("@TopicId", "");
    command.Parameters.AddWithValue("@DocumentNumber", "");
    command.Parameters.AddWithValue("@Rank", "");
    command.Parameters.AddWithValue("@Score", "");

    foreach ( /* item to loop through and add to db */ )
    {
        InsertResultItem(runTag, topicId, documentNumber, rank, score, command);
    }

    transaction.Commit();
    command.Dispose();
    connection.Dispose();
}

public int InsertResultItem(string runTag, int topicId, string documentNumber, int rank, double score, SQLiteCommand command)
{
    command.Parameters["@RunTag"].Value = runTag;
    command.Parameters["@TopicId"].Value = topicId;
    command.Parameters["@DocumentNumber"].Value = documentNumber;
    command.Parameters["@Rank"].Value = rank;
    command.Parameters["@Score"].Value = score;
    return command.ExecuteNonQuery();
}

它只使用一个连接、一个事务和一个命令，因此您每次更改的只是参数值.

It only uses one connection, one transaction and one command, so all you're changing is the parameter values each time.

这篇关于SQLite .NET 性能，如何加快速度?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！