Having issues for calling a sp multiple times per second async?

All we need is an easy explanation of the problem, so here it is.

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.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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)

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply