SQLite的.NET性能，如何加快东西呢？ [英] SQLite .NET performance, how to speed up things?

问题描述

在我的系统中，〜86000 SQLite的插入了长达20分钟，意味着〜每秒70插入。我要做的几百万，我怎么能加速吗？调用open（）和close（）上SQLiteConnection对象的每一行会降低性能？可交易帮助？

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?

有关单行典型插入方式：

Typical insertion method for a single line:

    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的开始了自己的事务，每个INSERT命令让你有效地执行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:

• 打开连接一旦
• 生成一次命令，一旦加入参数吧。
• 启动事务
• 循环遍历，只有在调用的Ex​​ecuteNonQuery更改参数值
• 提交事务。
• 关闭连接。

我觉得你可以减少20个您分钟降低到只需几秒钟就这样。

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

编辑：这就是我的意思是：

this is what I mean:

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屋！