.NET SqlDependency 与许多通知与高速率轮询? [英] .NET SqlDependency with many notifications vs. high rate polling?
问题描述
我有一个小桌子(大约 200 行)，它一直在变化(每秒几次).
我正在寻找一种解决方案，我可以在每次更改时从数据库接收通知，而不是轮询它(假设每秒 5-10 次).
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).
此外，我希望每次只接收更改的行，而不是获取整个表 - 我发现使用 SqlDependency 类很难实现这一点.
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.
需要考虑的几件事:
- 我无法控制表中的更改，也无法接收数据，只能使用数据库.
- 我打算构建一个服务，将更改广播到我的前端服务器
有没有办法使用 SqlDependency 做到这一点?
Is there a way to do this using SqlDependency?
鉴于这种情况，最佳做法是什么?
What is the best practice given this case?
顺便说一句:我使用的是 SQL Server 2012
BTW: i am using SQL server 2012
提前致谢
推荐答案
SqlDependency 只会通知您发生了更改，您必须重新读取整个表.它适用于低通知率.在高速率下，您必须考虑通知的成本很高.请参阅神秘通知，了解有关 SqlDependency 工作原理的更多详细信息.如您所见，成本很高:
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:
- 设置通知(写入 sys.dm_qn_subscriptions 和 SSB 系统表)
- 触发通知(写入 sys.dm_qn_subscriptions)
- 传递通知(写入 SSB 系统表、目标队列)
- 接收通知(写入目标队列、SSB 系统表)
那是相当多的写入，如果您不断收到通知，成本会迅速增加.
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.
这篇关于.NET SqlDependency 与许多通知与高速率轮询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
