4

I want to use SqlDependency in my project, but the table that I want dependency is being used by several programs for very important purposes. So they have to be able to insert this table while SqlDependency in action. Is that possible?

I've read this question but didn't find my answer.

Community
  • 1
  • 1
Hasan Akgün
  • 467
  • 1
  • 4
  • 15
  • 1
    Obviously it doesn't *lock* the table, that would be completely useless :) How would it notify you of changes to the table if it prevented all the changes? So your question is more about the performance impacts of using `SqlDependency`. Just from anecdotal evidence, I've never seen anything that would affect querying the table - there might be some if the table gets a lot of inserts and updates, though. Test it. – Luaan Apr 09 '15 at 07:36
  • Haha good point :) I concern about inserts while notifying. I need to catch every single change in table. – Hasan Akgün Apr 09 '15 at 07:56
  • The notifications are not guaranteed, so you won't catch all the changes anyway. If you need to do that, you'll have to find your own way (and perhaps use `SqlDependency` of just notifying there was *some* change, and another mechanism to determine exactly what changed. The `insert` is not going to wait for you to handle the callback. Maybe you could use triggers to accomplish what you're trying to do? Or use triggers to append to something like a log table that you could analyze later? – Luaan Apr 09 '15 at 07:59
  • 1
    Do understand that the purpose of `SqlDependency` is to allow *faster* cache invalidation. You still want to invalidate the cache periodically even if there's no notifications from `SqlDependency`, and you really don't want to use it to be *sure* if some particular row changed or not. – Luaan Apr 09 '15 at 08:04
  • Triggers mean danger in this project, no way. Does `insert` wait for me to handle or just wait for `SqlDependency` to notify? – Hasan Akgün Apr 09 '15 at 08:06
  • Neither. It goes completely separate. And of course, it gets tricky if you're using transactions. What are actually you trying to do? – Luaan Apr 09 '15 at 08:14
  • I want to detect one column's change in the table and if there is any, I'll handle changes. I want table to be able to be changed during this process. To be or not to be, there are many tos and bes in this comment :( – Hasan Akgün Apr 09 '15 at 08:20
  • 1
    Yup, that should mostly work. You'll still have to poll the DB periodically, though. The polling rate would mostly depend on how much of a delay you can afford - depending on your usecase, you could poll once an hour for example. If you need to respond in less than e.g. a minute, there probably isn't much of a benefit for using `SqlDependency` - unless you want to usually respond very fast, but can tolerate some instances where it will take much longer than usual. – Luaan Apr 09 '15 at 08:39

1 Answers1

6

To answer your question, SqlDependency will not 'lock' the table, but may increase lock contention in high-write environments as it uses the same mechanism as indexed views to detect changes to underlying data.

However, it should be a good fit unless:

  • The frequency of changes is likely to be high. To define 'high', you really need to test your ecosystem, but a suggested guideline is that if your data changes many times per second, it's probably not a good fit as you: the response time is not guaranteed for SqlDependency, and the callback mechanism is not designed to reliably handle many concurrent changes where you need to be notified of every change. In addition, the SqlDependency can increase blocking/contention on the underlying table as the index used to keep track of changes can form a bottle-neck with a high frequency of writes.

  • You are intending to build the SqlDependency into a client application (e.g. desktop app) which accesses the database directly, and of which there will be many instances. In this case, the sheer volume of listeners, queues and messages could impact database performance and is just inefficient. In this case you need to put some middleware in between your database and your app before thinking about SqlDependency.

  • You need to be reliably notified of every single change. The mechanism underlying SqlDependency within SQL Server will generate a notification for every change, but the .NET side of things is not inherently designed to handle them in a multi-threaded way: if a notification arrives while the SqlDependency's worker thread is already handling another notification, it will be missed. In this case, you may be able to use SqlNotificationRequest instead.

  • You need to be notified immediately of the change (i.e. guaranteed sub-second). SqlDependency is not designed to be low-latency; it's designed for a cache-invalidation scenario.

If SqlDependency is not a good fit, have a look at the Planning for Notifications and underlying Query Notifications pages on MSDN for more guidance and suggestions of alternatives. Otherwise see below for a bit more detail on how to assess performance based on the underlying technologies at play.

SqlDependency largely relies upon two key SQL Server technologies: query notifications (based on indexed views), and service broker. It effectively hooks into the mechanism that updates an indexed view whenever the underlying data changes. It adds a message to a queue for each change, and service broker handles the messaging and notifications. In cases where the write frequency is very high, SQL Server will work hard to handle the writes, keep its 'indexed view' up-to-date, as well as queueing and serving up the many resulting messages. If you need near-instant notification, this may still be the best approach, otherwise have a look at either polling, or using an After Update trigger which perhaps uses Service Broker as suggested on MSDN.

decates
  • 3,406
  • 1
  • 22
  • 25