C# + SQL Server - 将新行读入内存的最快/最有效的方法
我有一个 SQL Server 2008 数据库，并使用 C# 4.0 和 Linq to Entities 类设置来进行数据库交互。
存在一个在 DateTime 列上建立索引的表，其中值是该行的插入时间。每秒添加几行新行（~20），我需要有效地将它们拉入内存，以便可以在 GUI 中显示它们。为简单起见，假设我需要在通过 WPF 显示的列表中显示最新的 50 行。
我担心轮询可能会对数据库造成负载，以及处理新结果所需的时间，迫使我成为一个缓慢的消费者（陷入积压）。我希望得到一些关于方法的建议。我正在考虑的是；
- 以紧密循环的方式轮询数据库（每个查询约 1 个结果）
- 每秒轮询数据库（每个查询约 20 个结果）
- 为插入创建数据库触发器并将其与 C# 中的事件关联 (SqlDependency）
我还有一些访问选项；
- Linq-to-Entities 表 选择
- 原始 SQL 查询
- Linq-to-Entities 存储过程
如果您能阐明利弊或完全建议另一种方式，我很乐意听到。
将行添加到表中的过程不受我的控制，我只想读取行而不修改或添加。最重要的是不要让 SQL Server 超载，保持 GUI 最新且响应迅速，并使用尽可能少的内存......你知道，基础知识；）
谢谢！
I have an SQL Server 2008 Database and am using C# 4.0 with Linq to Entities classes setup for Database interaction.
There exists a table which is indexed on a DateTime column where the value is the insertion time for the row. Several new rows are added a second (~20) and I need to effectively pull them into memory so that I can display them in a GUI. For simplicity lets just say I need to show the newest 50 rows in a list displayed via WPF.
I am concerned with the load polling may place on the database and the time it will take to process new results forcing me to become a slow consumer (Getting stuck behind a backlog). I was hoping for some advice on an approach. The ones I'm considering are;
- Poll the database in a tight loop (~1 result per query)
- Poll the database every second (~20 results per query)
- Create a database trigger for Inserts and tie it to an event in C# (SqlDependency)
I also have some options for access;
- Linq-to-Entities Table Select
- Raw SQL Query
- Linq-to-Entities Stored Procedure
If you could shed some light on the pros and cons or suggest another way entirely I'd love to hear it.
The process which adds the rows to the table is not under my control, I wish only to read the rows never to modify or add. The most important things are to not overload the SQL Server, keep the GUI up to date and responsive and use as little memory as possible... you know, the basics ;)
Thanks!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
我来晚了一点，但是如果您的 SQL Server 2008 版本上有该功能，则有一个名为 更改数据捕获可能会有所帮助。基本上，您必须为数据库和需要捕获的特定表启用此功能。内置的更改数据捕获进程查看事务日志以确定对表进行了哪些更改，并将它们记录在预定义的表结构中。然后，您可以查询该表或将表中的结果提取到更友好的内容中（也许完全在另一台服务器上？）。我们正处于使用此功能来满足特定业务需求的早期阶段，到目前为止，它似乎运行得很好。
您必须测试此功能是否能够满足您的速度需求，但它可能有助于维护，因为不需要触发器，并且数据捕获不会占用您的数据库表本身。
I'm a little late to the party here, but if you have the feature on your edition of SQL Server 2008, there is a feature known as Change Data Capture that may help. Basically, you have to enable this feature both for the database and for the specific tables you need to capture. The built-in Change Data Capture process looks at the transaction log to determine what changes have been made to the table and records them in a pre-defined table structure. You can then query this table or pull results from the table into something friendlier (perhaps on another server altogether?). We are in the early stages of using this feature for a particular business requirement, and it seems to be working quite well thus far.
You would have to test whether this feature would meet your needs as far as speed, but it may help maintenance since no triggers are required and the data capture does not tie up your database tables themselves.
也许您可以使用 SQL Server 服务代理并从那里执行读取，甚至推送哪些行是新的，而不是轮询数据库。然后您可以从表中进行选择。
我在这里看到的最重要的事情是在识别新行的方式上有一个索引（时间戳？）。这样，您的查询将从索引中选择顶部条目，而不是每次都查询表。
测试，测试，测试！对您想要尝试的任何策略的表现进行基准测试。要解决的最大问题是数据的存储方式以及需要处理的锁定和一致性问题。
Rather than polling the database, maybe you can use the SQL Server Service broker and perform the read from there, even pushing which rows are new. Then you can select from the table.
The most important thing I would see here is having an index on the way you identify new rows (a timestamp?). That way your query would select the top entries from the index instead of querying the table every time.
Test, test, test! Benchmark your performance for any tactic you want to try. The biggest issues to resolve are how the data is stored and any locking and consistency issues you need to deal with.
如果您的表以每秒 20 行的速度不断更新，那么没有什么比每秒或每隔几秒拉取一次更好的了。只要您有一种有效的方法（即索引或聚集索引）可以检索最后插入的行，此方法将消耗最少的资源。
如果更新发生在每秒 20 次更新的爆发中，但中间有很长一段时间不活动（分钟），那么您可以使用 SqlDependency （它与触发器完全无关，顺便说一句，请阅读 神秘通知用于了解它的实际工作原理）。您可以将 LINQ 与 SqlDependency 混合使用，请参阅 linq2cache。
If you table is updated constantly with 20 rows a second, then there is nothing better to do that pull every second or every few seconds. As long as you have an efficient way (meaning an index or clustered index) that can retrieve the last rows that were inserted, this method will consume the fewest resources.
IF the updates occur in burst of 20 updates per second but with significant periods of inactivity (minutes) in between, then you can use SqlDependency (which has absolutely nothing to do with triggers, by the way, read The Mysterious Notification for to udneratand how it actually works). You can mix LINQ with SqlDependency, see linq2cache.
您必须查询才能收到新数据通知吗？
您最好使用来自服务总线的推送通知（例如：NServiceBus）。
使用通知（即事件）几乎总是比使用轮询更好的解决方案。
Do you have to query to be notified of new data?
You may be better off using push notifications from a Service Bus (eg: NServiceBus).
Using notifications (i.e events) is almost always a better solution than using polling.