存储恒定数据流的最佳实践

The main requirement for my current project is to receive, parse and store millions of radio messages per day. This means many messages per second are processed. At the moment the method of storing the parsed messages uses simple a SqlCommand and ExecuteNonQuery for each individual message.

我当前项目的主要要求是每天接收，解析和存储数百万条无线电信息。这意味着每秒处理许多消息。目前，存储解析消息的方法对每个单独的消息使用简单的SqlCommand和ExecuteNonQuery。

Seeing as each the project consists of multiple TcpClients reading in on separate threads there will be many concurrent instances of the below block being executed:

看到每个项目由多个TcpClients读入不同的线程，将会执行以下块的许多并发实例：

query.Append(string.Format("INSERT INTO {0} ({1}) VALUES ({2})", this._TABLE_, col, val));
sqlCmd = new SqlCommand(query.ToString(), sql);
sqlCmd.ExecuteNonQuery();

I understand an alternative may be to cache the parsed messages and at scheduled times perform a bulk insert.

我理解另一种方法可能是缓存已解析的消息，并在预定的时间执行批量插入。

Using the Entity Framework is an option, but probably overkill for the simple requirements.

使用实体框架是一种选择，但对于简单的要求可能有点过分。

The program requires a persistent connection 24 hours per day and therefore never closes the db connection.

该程序需要每天24小时持久连接，因此永远不会关闭数据库连接。

So my question is, how reasonable is my current approach? Should I close and open connections for each message? Or continue using a global db connection shared and passed by reference?

所以我的问题是，我目前的方法有多合理？我应该关闭并打开每条消息的连接吗？或继续使用共享的全局数据库连接并通过引用传递？

1 个解决方案

#1

Why are you using Sql Server to as your data store? I don't mean this rhetorically. I mean, what are the requirements for querying the data after it has been inserted? Will there ever be updates to existing data? Deletions? Are there normalized tables somewhere in the near future for this data?

为什么使用Sql Server作为数据存储？我并不是说这种言辞。我的意思是，在插入数据后查询数据的要求是什么？是否会对现有数据进行更新？缺失？在不久的将来，这些数据是否存在规范化表格？

These answers will inform the reasonableness of your current approach.

这些答案将告知您当前方法的合理性。

The program requires a persistent connection 24 hours per day and therefore never closes the db connection.

该程序需要每天24小时持久连接，因此永远不会关闭数据库连接。

You should open and close the db connection in your code regardless. ADO.NET pools the connections so you don't have to keep the connection open manually:

您应该在代码中打开和关闭数据库连接，无论如何。 ADO.NET对连接进行池化，因此您无需手动打开连接：

using (SqlConnection sql = new SqlConnection("...")) {
    query.Append(string.Format("INSERT INTO {0} ({1}) VALUES ({2})", this._TABLE_, col, val));
    using (SqlCommand sqlCmd = new SqlCommand(query.ToString(), sql) {
        sql.Open();
        sqlCmd.ExecuteNonQuery();
    }
}

#1