5

I am building a system to asynchronously send mails after processing data (it could take up to 10 seconds the processing).

My initial idea was to have a Windows service poll the database with data processing requests every 3 seconds for the first record to be processed. When done, poll and get the new first record to be processed.

When there are no records left wait 3 seconds and poll again to check for records.

I'm not sure if this is a good idea. The use of a timer is not a great way to program in my opinion. Also the performance and the use of the database even when not needed is a concern.

What is the best practice in this case?

Technologies used: .NET 3.5, SQL Server, and servers are in farm.

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
BBQ
  • 618
  • 1
  • 7
  • 20
  • You may want to see if a message queue could be used to fit your needs. Something like MSMQ, or do a web search for .net message queues. – CodingWithSpike Nov 06 '12 at 12:49

3 Answers3

4

It all depends on how much overhead you create.

If you can efficiently poll the database (i.e., with a simple query) and if such a polling does not require extensive network traffic (which it usually doesn't), why not poll every 3 seconds?

How did you reach the number 3? Is it arbitrary or is there a reason to check precisely every 3 seconds?

To keep the query simple, you may use 2 queries: one to detect whether there is new data, and another to actually fetch the data. This way you can optimize the one that will occur most often (the first one, obviously).

Roy Dictus
  • 32,551
  • 8
  • 60
  • 76
  • 3 is just a number, I want it to go as fast as possible but I don't want to overquery the db. 2 query thing is indeed a good idea since I do not expect to database to be constantly filled with records to process. – BBQ Nov 06 '12 at 12:53
1

Why would you get just one request at a time?

And consider notification

Working with Query Notification

paparazzo
  • 44,497
  • 23
  • 105
  • 176
  • I figured since the windows service will be running on different severs in the farm it will be faster to divide the work instead of claiming it to one server. Not sure if that's the best approach. – BBQ Nov 06 '12 at 12:56
  • Even on a farm I would still get a few at a time. Put farm in your question as that is an important design factor. – paparazzo Nov 06 '12 at 13:29
0

As long as your query doesn't cause blocking then I would say you could even poll 3 times a second without significant issue provided your database isn't under heavy CPU pressure already. And frankly, even if it was, I suspect this probably wouldn't cause much more noticeable difference.

On blocking, you have to ask if there are a lot of updates to the table you're polling because readers block writers (by default in SQL Server, unless you change your isolation level). If so, your query better be tuned with proper indexes so it's not doing anything crazy like locking the whole table while it scans it. It's unlikely you would be doing this, but it would certainly be easy to accomplish in certain scenarios so it's worth mentioning.

I'm going to make a pretty reasonable assumption that your polling query isn't going to constantly incur copious reads from the hard disk (which would obviously be terrible). I think we can also assume your query won't be parallelized (i.e. will run on a single thread).

So with that in mind, measure how long your query takes to run. If it takes, say, 10 milliseconds to run then at 3 times a second it would prevent some other queries from running for 30 milliseconds every second, right? But remember that this is just on one single core. You likely have a minimum of 2 cores if not more.

If you want to measure how long your query takes you can use SET STATISTICS TIME ON in Management Studio (I will let the reader google this on his own for more details if necessary). This is better than looking at the Elapsed Time in the Properties window as that includes the time it takes SSMS to render the results.

So the worst thing that can happen is that it will prevent another query from using the CPU for a few milliseconds out of every second. I'm tempted to say "unless you're on an OLTP server that's being constantly hammered don't worry about it". But the truth is that that's not even really accurate. Consider it more like trying to lift weights and you're about to try for a new max of 225 pounds. Is it really going to make a difference if someone tosses a paperclip or a bottlecap on top? You wouldn't even know they did it.

BVernon
  • 3,205
  • 5
  • 28
  • 64