如何同步 SQL Server 表的近实时读取
我们有一个报告应用程序，需要在数据写入相应的表时更新其图表。 （该报告仅基于一张表格）。目前，我们只是将上次读取的 sessionid + rowid （唯一组合）保留在内存中，并且轮询计时器仅在 rowid > 处执行选择。我们内存中的内容（以获取添加的最新行）。计时器大约每秒运行一次，快速的 sql 读取器可以很好地完成它的工作。到目前为止，一切都很好。但是我觉得这不是最佳选择，因为有时由于设计过程，数据写入会出现暂停。 （用户单击写入数据的系统上的暂停按钮..）。与此同时，我们的计时器不断访问数据库，并且没有获取任何新行。没有错误或任何东西。这种情况一般是怎么处理的。写入数据的应用程序与报告应用程序是分开的。这两个应用程序在不同的机器上运行。 底线：当数据写入 SQL Server 表时，如何将数据导入到 ac# 应用程序中，而无需进行不必要的轮询。 谢谢
We have a reporting app thats needs to update it's charts as the data gets written to it's corresponding table. (the report is based off just one table). Currently we just keep the last read sessionid + rowid (unique combo) in memory and a polling timer just does a select where rowid > what we have in memory (to get the latest rows added). Timer runs every second or so and the fast sql reader does it's job well. So far so good. However I feel this is not optimal because sometimes there are pauses in the data writes due to the process by design. (user clicking the pause button on the system that writes data ..). Meanwhile our timer keeps hitting the db and does not get any new rows. No errors or anything. How is this situation normally handled. The app that writes the data is separate from the reporting app. The 2 apps run on different machines. Bottomline : How to get data into a c# app as and when it is written into a sql server table without polling unnecessarily. thank you
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
SQL Server 能够通知正在等待的应用程序发生更改，请参阅神秘通知< /a>.这就是
SqlDependency有效。但这只能在数据变化率达到一定阈值时起作用。如果您的数据更改过于频繁，那么设置查询通知只是为了通过接收通知立即失效的成本太大。对于真正的高最终更改率，最好的地方是直接从编写者通知应用程序，通常通过某种形式的发布-订阅基础设施来实现。您还可以尝试混合方法：池化显示应用程序中的更改，并且仅在没有更改时才设置查询通知。这样，您可以避免在更改率较高时不断设置查询通知的成本，而且一旦写入稳定下来，您也可以获得非池化的好处。
SQL Server has the capability to notify a waiting application for changes, see The Mysterious Notification. This is how
SqlDependencyworks. But this will only work up to a certain threshold of data change rate. If your data changes too frequently then the cost of setting up a query notification just to be immediately invalidated by receiving the notification is too much. For really high end rates of changes the best place is to notify the application directly from the writer, usually achieved via some forms of a pub-sub infrastructure.You could also attempt a mixed approach: pool for changes in your display application and only set up a query notification if there are no changes. This way you avoid the cost of constantly setting up Query Notifications when the rate of changes is high, but you also get the benefits of non-pooling once the writes settle down.
不幸的是，唯一“正确”的方法是轮询，但是您可以通过让 SQL 在循环中等待（确保等待每个循环 30 毫秒）直到数据可用（或经过设定的时间段）来降低轮询的成本，例如 10 秒）。这在编写 SQL 伪队列时常用。
您可以使用扩展过程 - 但这很脆弱，或者您可以将消息放入 MSMQ。
Unfortunately the only 'proper' way is to poll, however you can reduce the cost of this polling by having SQL wait in a loop (make sure you WAITFOR something like 30ms each loop pass) until data is available (or a set time period elapses, e.g. 10s). This is commonly used when writing SQL pseudoqueues.
You could use extended procs - but that is fragile, or, you could drop messages into MSMQ.
如果您的报告应用程序在单个服务器上运行，那么您可以让将数据写入 SQL Server 的应用程序也向报告应用程序发送一条消息，让其知道有新数据可用。
但是，最常见的方法是让您的应用程序连接到服务器以查看是否添加了新记录。只要您在后台线程上进行轮询，就根本不会影响应用程序的性能。
If your reporting application is running on a single server then you can have the application that is writing the data to SQL Server also send a message to the reporting app letting it know that new data is available.
However, having your application connect to the server to see if new records have been added is the most common way of doing it. As long as you do the polling on a background thread, it shouldn't effect the performance of your application at all.
您需要将事件从数据库推送到应用程序的领域。
应用程序需要监听该消息。 （您需要决定监听的含义 - 什么端口、什么协议、什么格式等）
数据库将通过触发器根据事件发送消息。 （您需要查找如何在触发器中使用外部应用程序逻辑）
you will need to push the event out of the database into the realm of your application.
The application will need to listen for the message. (you will need to decide what listening means - what port, what protocol, what format etc.)
The database will send the message based on the event through a trigger. (you need to look up how to use external application logic in triggers)