c# - .NET SqlDependency with many notifications vs. high rate polling?

Question

I have a small table (approx. 200 rows) which changes all the time (couple of times per second).
I was looking for a solution where i could receive notification from the database each time it changes, rather than polling it (let's say 5-10 times per second).

Moreover, i would like to receive only the changed rows each time and not fetching the whole table - i have found this rather difficult to implement this utilizing SqlDependency class.

Couple of things to take under consideration:

1. I do not have control over the changes in the table nor do i have the ability to receive the data other than using the database.
2. I intend to build a service which will broadcast the changes to my front servers

Is there a way to do this using SqlDependency?

What is the best practice given this case?

BTW: i am using SQL server 2012

Thanks in Advance

Answer 1

SqlDependency will only notify you that a change occurred, and you'll have to re-read the entire table. It works well with a low rate of notifications. At a high rate, you have to consider that notifications have a significant cost. See The Mysterious Notification for more details how SqlDependency works. As you cans see, there is significant cost:

• setting up the notification (writing in sys.dm_qn_subscriptions and in SSB system tables)
• firing the notification (writing sys.dm_qn_subscriptions)
• delivering the notification (writes in SSB system tables, target queue)
• receiving the notification (writes in target queue, SSB system tables)

That's quite a few writes, the cost will add up quickly if you get notified constantly.

But the real question is how exactly do you want to react in the application to a constant rate of change? what do you want to know, that something changed? well, you know, it always does, anytime the application needs the data it better read the latest state, because it certainly changed. Polling does not make sense either. Sounds like what you really want is either change tracking or, more likely, a custom based queue of changes fed by the change originators.