将大表中的每一行放入 ssb 队列中
我有一个包含 250 万行的表，每行有一列 xml 类型。当消息到达另一个队列（触发队列）时，所有记录都应被删除并排入 sqlserver 服务代理队列中。性能非常重要，但现在太慢了。实现这一目标的最佳方法是什么？
目前，我们在触发器队列上使用激活的 sp，它在 while(@message <> null) 循环中执行：
begin transaction
delete top (1) from table output @tempTable
select top 1 @message = message from @tempTable
send on conversation @message
commit transaction
是否有更快的方法来解决这个问题？
顺便说一句：在有人问之前：我们需要从表开始，因为它填充了先前计算的合并语句的输出
I have a table that contains 2.5 million rows, each row has one column of type xml. All records should be deleted and enqueued in a sqlserver service broker queue when a message arrives in another queue (triggerqueue). Performance is very important and now it's too slow. What would be the best way to achieve this?
currently we use an activated sp on the triggerqueue which does in a while(@message <> null) loop:
begin transaction
delete top (1) from table output @tempTable
select top 1 @message = message from @tempTable
send on conversation @message
commit transaction
are there faster ways to tackle this problem?
By the way: before someone asks: we need to start from the table, because it is filled with the output from an earlier calculated merge statement
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
所以你的性能问题是在发送端而不是接收端，对吧？ （从你的问题来看有点不清楚）。在这种情况下，您需要从尝试开始：
如果您在接收端遇到问题，请查看此Remus 的精彩文章。
So your performance problem is on the send side rather than receive side, right? (it's a bit unclear from your question). In this case, you'll want to start with trying:
In case you're experiencing problems on the receive side, take a look at this great article by Remus.