Best practice for storing constant stream of data

Question

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.

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:

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.

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?

Answer 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?

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.

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:

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();
    }
}