通过计时器比较SqlDependency(Service Broker)与查询的效率如何?

问题描述

当我的数据库之一(SQL Server 2008)的表中添加了新记录时，我需要通知用户(大约100个客户端)我的软件(C#、. NET 3.5).如果我理解正确，则此类操作应视为缓存无效，这正是 SqlDependency 的目的，如果我错了，请对此进行更正.

I need to notify users (around 100 clients) of my software (C#, .NET 3.5) when there are new records added in one of the tables of my database (SQL Server 2008). If I understand it right, such operation should qualify as cache invalidation which is exactly what is SqlDependency for, please correct me on this one if I'm wrong.

为此，我可以看到2个选项:

For that I can see 2 options:

1. SqlDependency ，这将允许我接收实际上的实时通知.
2. 通过计时器检查表格.

1. SqlDependency which will allow me to receive practically realtime notifications.
2. Checking table by some timer.

但是我以前从未使用过 SqlDependency ，也不知道它到底有多可靠?我的意思是，我可以让我的应用程序在一天开始时只运行 SqlDependency.Start()并确保它无论10个小时都将监听通知吗?当然，我知道我必须对服务查询通知做出正确的反应.相对于按计时器查询就数据库性能而言，这样做真的更好吗?实际上数据库有什么显着差异吗?

But I never used SqlDependency before and have no idea how reliable is it really? I mean, can I allow my application just run SqlDependency.Start() at the start of a day and be sure it will listen for notifications no matter what for 10 hours? Of course I understand I must correctly react on service query notifications. And is it really better to do so in comparison to query by timer in terms of performance of database? Is there any considerable difference actually for database?

我正在考虑使用 SqlDependency ，主要是因为我不确定让所有用户每隔5分钟打开连接并在同一数据库中查询同一表是最好的主意.

I’m considering using SqlDependency mostly because I’m not sure it’s a best idea to have all of my users to open connection and query the same table in the same database every 5 minutes.

推荐答案

我也一直在寻找SqlDependency类的性能指标，但不幸的是没有找到任何指标.但是，如果我不得不猜测，我会说SqlDependency比外部轮询机制更有效，因为其他所有条件都保持不变(包括轮询间隔= SqlDependency的内部轮询间隔).我之所以这样说，是因为在进行外部轮询时，您必须考虑每个轮询请求产生的开销.使用SqlDependency，可以优化服务以减少开销(至少，开销将小于外部轮询).只是我的两分钱，但我已经阅读了使用SQLDependency与定期对表进行轮询(对性能的影响)，效果完全相反.我将进行自己的测试，然后回发适用于我的方案的

I have also been looking for performance metrics of the SqlDependency class, but unfortunately haven't found any. But if I had to guess, I'd say a SqlDependency is more efficient than an external polling mechanism, given everything else stays the same (including polling interval = internal polling interval of the SqlDependency). I say this because when polling externally, you have to consider the overhead that each polling request makes. With a SqlDependency, the service is optimized to reduce the overhead (at the very least, the overhead would be less than external polling). Just my two cents, but I've read at Using SQLDependency vs. periodic polling of a table (performance impact) that the effect is quite the opposite. I will be doing my own testing and post back what works for my scenario.

这篇关于通过计时器比较SqlDependency(Service Broker)与查询的效率如何?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！