SQL Server 2005首选的合并方法是什么? [英] What is the preferred merge method for SQL Server 2005?
问题描述
我主要是使用 Exists方法将行合并到表中，但是我正在考虑切换到行计数方法.有什么理由不这样做吗?
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?
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
行计数方法似乎更快.在具有约5万行的表上，其时钟的时间为 Exists方法的时间的1/5.测试不是太科学，但即使保守地+/- 15％也相当可观.这是我要切换的主要原因.
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.
注意
故意为了易于阅读而简化了示例.它们绝不能反映我的实际情况.
The examples were purposely made simple for readability. They in no way reflect my actual situation.
推荐答案
我看不到任何特殊问题.但是，您将不得不尝试哪一个性能更好(尽管我认为在此示例中这无关紧要).但是正如Cade所指出的，请使用事务.
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.
另外，请注意，对于SQL Server 2008，您可以使用MERGE语句(以防万一您要升级).
Also, note that for SQL Server 2008 you can use the MERGE statement (just in case you are going to upgrade).
这篇关于SQL Server 2005首选的合并方法是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
