Here is my scenario,

I have a windows service that monitors queues of data for messages. If this services finds a message on the queue it will grab it, do a little work then send it to a wcf webservice.

This webservice does a little more processing and finally writes the message to the SQL database.

My issue is there can be thousands of messages in these queues which means 1000s of calls to this webservice and 1000s of constant database inserts on multiple threads asynchronously.

Is it good practice to constantly ping a SQL database like this? If not what is a better way to approach this? Also I must add the requirement is for these messages to be processed immediately. I thought about the bulk insert approach but that would require gathering all messages into a list before inserting and these messages have to be in the table within 2 seconds max.

  • 1
    Are you running into problems with your implementation of this? What are those problems? There are too many variables about the implementation for us to guess where you are having trouble. There are aspects of table design, indexing, and how your application inserts that can all affect this. Fundamentally, SQL Server can handle the insert volume, but it's also possible you'll run into issues. If you've already designed/implement something, please share the details of your design and your problems, so we can help.
    – AMtwo
    Aug 15 at 13:03
  • 2
    Please add a minimal reproducible example to your question.
    – Hannah Vernon
    Aug 15 at 15:58

The part of the question relating to SQL-Server is totally fine ie The webservice having 1000s of calls to the database, should pose no problem - per-se.

But as with anything SQL - the answer is: it all depends. Some of those dependencies you should be aware of are listed below.

  1. Hardware of you SQL Server needs to be sufficient for the load
  2. Your query to write messages to the database needs to be efficient. If its just an insert statement, then (probably) no problem, if it needs to query the DB before the insert, there's a bunch of scenarios that need to be tested for (deadlocks, race conditions, etc)
  3. Assuming your windows service is dotnet, you'd need to ensure connection pooling is turned on, (creating a connection is pretty expensive)
  • 1
    yea I found out with a simple load script that It does handle the load however I had to increase the Pooling Size to something like 10k. Otherwise I would get timeouts with very heavy load and the pool being exhausted Aug 18 at 20:48

Not the answer you're looking for? Browse other questions tagged or ask your own question.