In the beginning of our app's development, we were using SQLDependency quite heavily to cache db results until the notifications told our app to grab a fresh copy.
在我们应用程序开发的开始阶段，我们一直在大量使用SQLDependency来缓存db结果，直到通知告诉我们的应用程序获取一个新的副本。
During testing, we've noticed that the sql db's performance was getting hammered by the sqldependency notification service. We scaled back the number of tables that we were using sqldependency and noticed a large gain in performance. So, we thought we were just over using it and we moved on. We are down to only a few tables now.
在测试期间，我们注意到sql db的性能受到sqldependency通知服务的影响。我们缩小了使用sqldependency的表的数量，并注意到性能有了很大的提高。所以，我们认为我们已经过度使用它了，我们继续前进。我们现在只剩下几张桌子了。
Later we discovered that we couldn't scale back the security access level for the username that will establish the dependendency. We could have more than one connection string for each db (one for dependency and one for the rest of the app) but with multiple db's and db mirroring, this is a pain (from sql db admin point of view and app development)
后来我们发现，我们无法缩小将建立依赖关系的用户名的安全访问级别。我们可以为每个db提供多个连接字符串(一个用于依赖项，另一个用于应用程序的其余部分)，但是对于多个db和db镜像，这是一种痛苦(从sql db管理的角度和应用程序开发的角度来看)
At this point we are just thinking about moving away from SQLDependency altogether based on the following logic:
此时，我们正在考虑基于以下逻辑完全摆脱SQLDependency:
- We don't need "instant" notification that the data has changed. If we knew within 1 second, that would be fast enough.
- 我们不需要“即时”通知，数据已经改变。如果我们在1秒内知道，那就足够快了。
- With some slight re-factoring, we could get it down to just 1 table and poll that table once a second.
- 通过稍微重构，我们可以将其减少到1个表，并每秒轮询该表一次。
Does anyone see a flaw in this logic?
有人看出这种逻辑的缺陷吗?
Would polling one table once a second cause more or less load on the db than SQLDependency?
对一个表进行轮询是否会使数据库的负载比SQLDependency多多少少?
Has anyone had similar performance issue with SQLDependency?
是否有人对SQLDependency有类似的性能问题?
1 个解决方案
#1
9
I do dare try answer your question. But I am not sure you'll get the answer you was hoping for...
我敢回答你的问题。但我不确定你会得到你希望的答案……
I remember back in the early 90ies when Borland promoted this grand new feature of 'callbacks' in their database Interbase that would give the caller (Delphi) 'notifications' via some very nifty new tech where promises was made that the database could be 'active'.
我记得在90年代早期Borland在他们的数据库内部推广了这个重要的新特性“回调”它会给调用者(Delphi)“通知”通过一些非常漂亮的新技术，承诺数据库可以“活跃”。
This was later known as the 'waste of time theory'.
这后来被称为“浪费时间理论”。
And I guess why this never took of is perhaps that while the concept of DBMS was looking very promising, the database is one of your tiers that you can only scale up and not horizontally.
我猜这为什么没有被采纳也许DBMS的概念看起来很有前途，但是数据库是你只能扩展而不能横向扩展的层之一。
So programming languages to the rescue. Or rather the idea of Service Oriented Architecture (SOA). Many confuse SOA for 'Webservices' that was indeed an included hype in this new concept.
编程语言拯救了我们。或者更确切地说是面向服务体系结构(SOA)的概念。许多人将SOA混淆为“Webservices”，这在这个新概念中确实是一种炒作。
But if you check out the Fiefdom/Emissary design pattern (or Master/Agent pattern renamed to make it sound more cool and professional), you will find that the major idea is having exclusive control of its resources (read databases) and that all calls are being funneled via one single data adapter.
但是，如果您查看Fiefdom/Emissary设计模式(或重命名为Master/Agent模式，以使其听起来更酷、更专业)，您将发现，主要的思想是对其资源(读取数据库)进行独占控制，并且所有调用都是通过一个数据适配器进行的。
Obviously such a design does not work at all with triggers nor any callback frameworks.
显然，这种设计对于触发器或任何回调框架都不起作用。
But I think you should reconsider your entire design. If you funnel all actions and all calls via a single 'DataLayer', perhaps using Entity Framework, and perhaps on top on that a Caching mechanism you would not have to rely on your database to forward messages back up the food chain.
但我认为你应该重新考虑你的整个设计。如果您通过一个“DataLayer”来传递所有的操作和所有的调用，可能使用实体框架，或者在这个基础上使用缓存机制，那么您就不需要依赖数据库将消息转发到食物链中。
To show how weird things can get when being to 'database-centric', here is an extreme actual live example of how not to send an email, written a long long time ago, by a coder I was not so much impressed with:
为了展示“以数据库为中心”时，事情会变得多么奇怪，这里有一个极端真实的例子，告诉你如何不发送一封很久以前由一个程序员写的电子邮件。
Fact 1: Sql Server can send emails.
事实1:Sql Server可以发送电子邮件。
Fact 2: Asp3 coder does not know if or how this can be done in VbScript.
事实2:Asp3编码器不知道是否或者如何在VbScript中实现这一点。
Asp3: read textbox email-address, send to com+ layer
Asp3:读取文本框邮件地址，发送到com+层
Com+: take email-address and forward to datalayer
Com+:取电子邮件地址，转发至datalayer
Datalayer: take email-address and forward to a stored procedure
Datalayer:使用电子邮件地址并转发到存储过程。
Sproc: take email-address and forward to sql function
Sproc:以电子邮件地址和转发到sql函数。
function: do weird sub-string things to check that email-adress has @ . in it. return true or false.
函数:做一些奇怪的子字符串检查邮件-adress有@。在里面。返回true或false。
Sproc: return a recordset with one column and one row containing 1 or 0
返回一个记录集，其中一列和一行包含1或0
Datalayer: return the table as is.
Datalayer:按原样返回表。
Com+: convert the first column and row with value 1 or 0 to true or false
Com+:将值为1或0的第一列和行转换为true或false
Asp3: if true, send email-adress with email subject and email text to com+
Asp3:如果是真的，发送电子邮件-adress与电子邮件主题和电子邮件文本到com+
Com+: sends the exact information to datalayer
Com+:将准确的信息发送给datalayer
Datalayer: calls an stored procedure..
Datalayer:调用存储过程。
Sproc: calls a sql-function...
Sproc:调用sql函数…
function: uses sql server email agent to send the email
功能:使用sql server邮件代理发送邮件。
If you read this far, my advice is to let sql server manage tables, relations, indexes and transactions. It is very good at that. Anything beyond those tasks, and with that I do include cursors in stored procedures, is better handled via proper code.
如果您阅读了这篇文章，我的建议是让sql server管理表、关系、索引和事务。它在这方面做得很好。除了这些任务之外的任何事情(我确实在存储过程中包含游标)，都可以通过正确的代码进行更好的处理。
#1
9
I do dare try answer your question. But I am not sure you'll get the answer you was hoping for...
我敢回答你的问题。但我不确定你会得到你希望的答案……
I remember back in the early 90ies when Borland promoted this grand new feature of 'callbacks' in their database Interbase that would give the caller (Delphi) 'notifications' via some very nifty new tech where promises was made that the database could be 'active'.
我记得在90年代早期Borland在他们的数据库内部推广了这个重要的新特性“回调”它会给调用者(Delphi)“通知”通过一些非常漂亮的新技术，承诺数据库可以“活跃”。
This was later known as the 'waste of time theory'.
这后来被称为“浪费时间理论”。
And I guess why this never took of is perhaps that while the concept of DBMS was looking very promising, the database is one of your tiers that you can only scale up and not horizontally.
我猜这为什么没有被采纳也许DBMS的概念看起来很有前途，但是数据库是你只能扩展而不能横向扩展的层之一。
So programming languages to the rescue. Or rather the idea of Service Oriented Architecture (SOA). Many confuse SOA for 'Webservices' that was indeed an included hype in this new concept.
编程语言拯救了我们。或者更确切地说是面向服务体系结构(SOA)的概念。许多人将SOA混淆为“Webservices”，这在这个新概念中确实是一种炒作。
But if you check out the Fiefdom/Emissary design pattern (or Master/Agent pattern renamed to make it sound more cool and professional), you will find that the major idea is having exclusive control of its resources (read databases) and that all calls are being funneled via one single data adapter.
但是，如果您查看Fiefdom/Emissary设计模式(或重命名为Master/Agent模式，以使其听起来更酷、更专业)，您将发现，主要的思想是对其资源(读取数据库)进行独占控制，并且所有调用都是通过一个数据适配器进行的。
Obviously such a design does not work at all with triggers nor any callback frameworks.
显然，这种设计对于触发器或任何回调框架都不起作用。
But I think you should reconsider your entire design. If you funnel all actions and all calls via a single 'DataLayer', perhaps using Entity Framework, and perhaps on top on that a Caching mechanism you would not have to rely on your database to forward messages back up the food chain.
但我认为你应该重新考虑你的整个设计。如果您通过一个“DataLayer”来传递所有的操作和所有的调用，可能使用实体框架，或者在这个基础上使用缓存机制，那么您就不需要依赖数据库将消息转发到食物链中。
To show how weird things can get when being to 'database-centric', here is an extreme actual live example of how not to send an email, written a long long time ago, by a coder I was not so much impressed with:
为了展示“以数据库为中心”时，事情会变得多么奇怪，这里有一个极端真实的例子，告诉你如何不发送一封很久以前由一个程序员写的电子邮件。
Fact 1: Sql Server can send emails.
事实1:Sql Server可以发送电子邮件。
Fact 2: Asp3 coder does not know if or how this can be done in VbScript.
事实2:Asp3编码器不知道是否或者如何在VbScript中实现这一点。
Asp3: read textbox email-address, send to com+ layer
Asp3:读取文本框邮件地址，发送到com+层
Com+: take email-address and forward to datalayer
Com+:取电子邮件地址，转发至datalayer
Datalayer: take email-address and forward to a stored procedure
Datalayer:使用电子邮件地址并转发到存储过程。
Sproc: take email-address and forward to sql function
Sproc:以电子邮件地址和转发到sql函数。
function: do weird sub-string things to check that email-adress has @ . in it. return true or false.
函数:做一些奇怪的子字符串检查邮件-adress有@。在里面。返回true或false。
Sproc: return a recordset with one column and one row containing 1 or 0
返回一个记录集，其中一列和一行包含1或0
Datalayer: return the table as is.
Datalayer:按原样返回表。
Com+: convert the first column and row with value 1 or 0 to true or false
Com+:将值为1或0的第一列和行转换为true或false
Asp3: if true, send email-adress with email subject and email text to com+
Asp3:如果是真的，发送电子邮件-adress与电子邮件主题和电子邮件文本到com+
Com+: sends the exact information to datalayer
Com+:将准确的信息发送给datalayer
Datalayer: calls an stored procedure..
Datalayer:调用存储过程。
Sproc: calls a sql-function...
Sproc:调用sql函数…
function: uses sql server email agent to send the email
功能:使用sql server邮件代理发送邮件。
If you read this far, my advice is to let sql server manage tables, relations, indexes and transactions. It is very good at that. Anything beyond those tasks, and with that I do include cursors in stored procedures, is better handled via proper code.
如果您阅读了这篇文章，我的建议是让sql server管理表、关系、索引和事务。它在这方面做得很好。除了这些任务之外的任何事情(我确实在存储过程中包含游标)，都可以通过正确的代码进行更好的处理。