构建高性能数据库队列 [英] Building a high performance database queue

问题描述

我有一个项目，我可以从发送的电子邮件中获得响应，这可能是每秒100个。最后，我必须将收到的信息插入到一个表中，该表将包含许多用于报告目的的索引列。我的想法是建立一个完全剥离任何索引的主表，并将在那里记录进来的响应。在另一个过程中，来自第一个主表的这些记录将以有序的方式移动到具有索引的最终表。



- 有没有人有任何建议这种方法还是已经看到了一种更有效的方法来处理大量数据？



- 可以使用这样的主表来添加记录不断删除？它是否在SQL服务器中碎片过多？



谢谢

I have a project where I get responses from emails that are sent out which may be 100 per second. Eventually I have to insert the information received into a table that will have many indexed columns for reporting purposes. My thought was to set up a primary table which is completely stripped any indexes and the responses coming in will be recorded there. In a different process, these records from this first primary table will be moved to the final table with the indexes in an orderly fashion.

- Does anyone have any suggestions on this method or have seen a more efficient method to handle a large influx of data?

- Is it OK to have a primary table like this in which records are added and deleted continually? Does it fragment in the SQL server too heavily?

Thank you

推荐答案

我提出了一个解决方案与是在一定时间间隔发出此声明：



删除tblUnsubscribeLogQueue

OUTPUT已删除。* INTO tblUnsubscribeLog



我通过使用OUTPUT子句读取我可以确保不会发生死锁，因为这只是一个操作，我只有一个进程正在删除记录。



有任何意见吗？



如果这会导致碎片，还有其他想法吗？



谢谢

One solution I came up with was to fire this statement at an interval:

Delete tblUnsubscribeLogQueue
OUTPUT deleted.* INTO tblUnsubscribeLog

I read by using the OUTPUT clause I could be assured that deadlocks would not occur because this was all one operation and I will only have one processes that is deleting records.

Any comments?

Any other thoughts on if this will cause fragmentation?

Thanks

这篇关于构建高性能数据库队列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！