Best practice for storing persistent data stream

Question

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

Seeing that each project consists of several TcpClient reads in separate threads, there will be many simultaneous instances of the block executed below:

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

I understand that caching parsed messages can be an alternative, and a voluminous insert is scheduled.

Using the Entity Framework is an option, but perhaps redundant for simple requirements.

The program requires a permanent connection 24 hours a 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 to use the global db connection shared and passed by reference?

Answer 1

Why are you using Sql Server as a data warehouse? I do not mean it rhetorically. I mean, what are the requirements for querying data after inserting it? Will existing data ever be updated? Misses? 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 permanent connection 24 hours a day and therefore never closes the db connection.

You must open and close the db connection in your code independently. ADO.NET combines connections, so you do not need to open the connection 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(); } }