sql server 2008中“Merge”子句的性能如何?

时间:2021-02-12 03:38:35

Merge can performs insert, update, or delete operations on a target table based on the results of a join with a source table. For example, you can synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table.

Merge可以根据与源表的连接结果对目标表执行插入,更新或删除操作。例如,您可以根据在另一个表中找到的差异,通过在一个表中插入,更新或删除行来同步两个表。

Is anyone familiar with the performance to use "Merge" versus the traditional logic to check existence and decide the update or insert then?

熟悉性能的人是否使用“Merge”与传统逻辑来检查存在并决定更新或插入?

Thanks!

谢谢!

3 个解决方案

#1


8  

MERGE is generally faster because there are less DML operations, and it's also the recommended approach from the documentation. With the "traditional" way, you're processing the tables twice - once to check for existence and once to execute your DML. With MERGE, everything is encapsulated with one operation - hence one set of locks issued, one set of logging, etc. etc.

MERGE通常更快,因为DML操作较少,而且它也是文档中推荐的方法。使用“传统”方式,您将对表进行两次处理 - 一次检查是否存在以及一次执行DML。使用MERGE,一切都用一个操作封装 - 因此发出一组锁,一组记录等。等等。

However, it is pretty subjective on what your queries are actually doing. You should probably take a look at Optimizing MERGE Statement Performance on MSDN.

但是,对于您的查询实际执行的操作,这是非常主观的。您应该看看在MSDN上优化MERGE语句性能。

#2


1  

Technet has some information in Optimizing MERGE Statement Performance. It basically says that performance is better than doing individual checks, because only one pass over the data is required. However indexes etc. are of course still important.

Technet在优化MERGE语句性能方面有一些信息。它基本上表示性能优于单独检查,因为只需要对数据进行一次传递。然而索引等当然仍然很重要。

#3


0  

We used this Merge statement recently in a large Data Warehouse project and we found a good performance as well as easy of implementation. We feel Merge statement is very handy for development.

我们最近在一个大型数据仓库项目中使用了这个Merge语句,我们发现了良好的性能以及易于实现。我们认为Merge声明对于开发来说非常方便。

looking forward to discuss

期待着讨论

thanks prav

谢谢你

#1


8  

MERGE is generally faster because there are less DML operations, and it's also the recommended approach from the documentation. With the "traditional" way, you're processing the tables twice - once to check for existence and once to execute your DML. With MERGE, everything is encapsulated with one operation - hence one set of locks issued, one set of logging, etc. etc.

MERGE通常更快,因为DML操作较少,而且它也是文档中推荐的方法。使用“传统”方式,您将对表进行两次处理 - 一次检查是否存在以及一次执行DML。使用MERGE,一切都用一个操作封装 - 因此发出一组锁,一组记录等。等等。

However, it is pretty subjective on what your queries are actually doing. You should probably take a look at Optimizing MERGE Statement Performance on MSDN.

但是,对于您的查询实际执行的操作,这是非常主观的。您应该看看在MSDN上优化MERGE语句性能。

#2


1  

Technet has some information in Optimizing MERGE Statement Performance. It basically says that performance is better than doing individual checks, because only one pass over the data is required. However indexes etc. are of course still important.

Technet在优化MERGE语句性能方面有一些信息。它基本上表示性能优于单独检查,因为只需要对数据进行一次传递。然而索引等当然仍然很重要。

#3


0  

We used this Merge statement recently in a large Data Warehouse project and we found a good performance as well as easy of implementation. We feel Merge statement is very handy for development.

我们最近在一个大型数据仓库项目中使用了这个Merge语句,我们发现了良好的性能以及易于实现。我们认为Merge声明对于开发来说非常方便。

looking forward to discuss

期待着讨论

thanks prav

谢谢你