08 Februari 2012 19:38
I'm looking for a notification technique that will be able to notify my app on every change to the data in a table, that is robust and reliable.
I have looked at (and used) SqlDependency, but BOL suggest that this is not really appropriate for frequent updates. In my case, the table in question will certainly be updated at least every second, and frequently many times per second.
Ideally, any solution would notify of only the changed rows so that time does not need to be spent working that out. I could poll the table, but this would cluster the updates and would not be very efficient. The code that needs to be notified is in a Windows service.
09 Februari 2012 7:52Moderator
You may think about using SQL cache dependency appropriately in ASP.NET.
ASP.NET supports both table-based polling and query notification, depending on the version of SQL Server you are using. Table-based polling is supported by all versions of SQL Server. In table-based polling, if anything in a table changes, all listeners are invalidated. This can cause unnecessary churn in the application. Table-based polling is not recommended for tables that have many frequent changes. For example, table-based polling would be recommended on a catalog table that changes infrequently. It would not be recommended for an orders table, which would have more frequent updates. Query notification is supported by SQL Server 2005. Query notification supports specific queries, which reduces the number of notifications sent when a table is changed. While it can provide better performance than table-based polling, it does not scale to thousands of queries.
For more information on SQL cache dependency, see Walkthrough: Using ASP.NET Output Caching with SQL Server or Caching in ASP.NET with the SqlCacheDependency Class.
Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
09 Februari 2012 9:53
Thanks for the reply. I think you are reporting essentially what I discovered about SqlDependency: that it does not perform well for frequent updates. I have a query that I wish to be notified about, but the results from that query could change several times per second at peak rates. Unless I am mistaken, SQL cache dependency has the same caveat about frequent updates? Also, ASP.NET isn't really the right vehicle for what I'm trying to do.
I am using SQL Server 2008 R2 Enterprise edition, if that helps.
- Diedit oleh ckl42 09 Februari 2012 11:52
14 Februari 2012 11:10
Does anyone have any more thoughts/suggestions? I'd hate for the trail to go cold.