SQL Server 2005 的首选合并方法是什么？
我主要使用存在方法将行合并到表中，但我正在考虑切换到行计数方法。有什么理由不这样做吗？
Exists 方法
If Exists(Select * From Table Where ID = @ID) Begin
Update Table Set Value = @Value Where ID = @ID
End Else Begin
Insert Into Table (Value) Values (@Value);
End
行计数方法
Update Table Set Value = @Value Where ID = @ID
If (@@RowCount = 0) Begin
Insert Into Table (Value) Values (@Value);
End
性能
行计数方法 似乎要快得多。在大约有 50k 行的表上，它的计时时间是 Exists 方法 时间的 1/5。这些测试不太科学，但即使保守的 +/- 15% 也相当可观。这是我想转行的主要原因。
注意
为了便于阅读，这些示例特意变得简单。它们绝不反映我的实际情况。
I have mainly been using the Exists Method for merging a row into a table but I am considering switching to the Row Count Method. Is there any reason not to?
Exists Method
If Exists(Select * From Table Where ID = @ID) Begin
Update Table Set Value = @Value Where ID = @ID
End Else Begin
Insert Into Table (Value) Values (@Value);
End
Row Count Method
Update Table Set Value = @Value Where ID = @ID
If (@@RowCount = 0) Begin
Insert Into Table (Value) Values (@Value);
End
Performance
The Row Count Method seems to be dramatically faster. On a table with about 50k rows it clocks in at 1/5 the time of the Exists Method. The tests were not too scientific but even with a conservative +/- 15% that is considerable. This is the main reason I want to switch.
NOTE
The examples were purposely made simple for readability. They in no way reflect my actual situation.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我没有看到任何特别的问题。不过，您必须尝试哪一个性能更高（尽管我认为这在本例中微不足道）。但正如凯德指出的，使用事务。
另请注意，对于 SQL Server 2008，您可以使用 MERGE 语句（以防万一您要升级）。
I don't see any particular problem. You would have to try which one is more performant, though (although I think that's insignificant in this example). But as Cade pointed out, use a transaction.
Also, note that for SQL Server 2008 you can use the MERGE statement (just in case you are going to upgrade).
无论哪种方式，您可能需要将其包装在事务中。
Either way, you might need to wrap that in a transaction.
不切换的最大原因是，您现在所拥有的正在运行，进行更改可能会出现新的错误。如果您想在更新其他内容时进行更改，那是可以的，但是您真正能从中获得什么好处改变？我怀疑性能增益（如果有的话）可能非常小，因为您给出的示例似乎使用单个记录。
The biggest reason not to switch is that what you have now is working and making a change introduces the possibilty of new bugs creeping in. If you want to change as you update other things, that's OK, but what are you really going to gain from the change? I suspect the performance gain if any would likely be very small as the examples you are giving appear to use single records.