![]() ![]() CPU usage was about 3% more for the MERGE and elapsed time was about 3% less for the MERGE. In this case, CPU and elapsed time are probably too close to call. The results from a Profiler trace using these queries give us this: SELECT CASE WHEN n <= 500000 THEN 2*n-1 ELSE 2000000+n ENDĪ quick check of the row counts generated from Test Harness #2.sql confirms that both MERGE and INSERT insert exactly 1,000,000 rows. SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))įROM sys.all_columns a CROSS JOIN sys.all_columns b To test the performance of MERGE as a substitute for INSERT, the same test harness that set up the #Target table can be used, but we’ll change the set up for the #Source table as follows. Not surprising considering all the complexity that MERGE must handle, but possibly forgivable for the additional safety, convenience and simplicity it represents (no need for a TRANSACTION with error handling). These indicate that MERGE took about 28% more CPU and 29% more elapsed time than the equivalent INSERT/UPDATE. The results that we obtained running the test harness in SQL Profiler (5 runs) are as follows: We omit error handling and possible ROLLBACK of the transaction for simplicity. TRUNCATE and re-populate Source and Target tablesīoth of these INSERT 500,000 rows and UPDATE 500,000 rows, the latter enclosed in a TRANSACTION. Using SQL Profiler, we’ll compare two identical query scripts: ![]() We’ve purposely set up our source table so that the INSERTs it will do when merged with the target are interleaved with existing records for the first 500,000 rows. SELECT CASE WHEN n <= 500000 THEN 2 * n - 1 ![]() ![]() The basic set-up data is as follows.ĪS ( SELECT TOP 1000000 ROW_NUMBER() OVER ( ORDER BY ( SELECT NULL You can open the Test Harness 1.sql file in the resources section of this article and follow along. To effectively test the performance of our alternatives, we’ll need to set up a test harness with a non-trivial number of rows in our #Source and #Target tables. Sorry if you know all this stuff and I’ve bored you, but we needed to get these basics out of the way. Unsurprisingly, the results now in #Target are these: Let’s INSERT a new row into #Source and do this. Let’s change the values in our #Source table, and then use MERGE to only do an UPDATE.įinally, we know we can also use MERGE to replace INSERT by omitting the MATCHED clause. This produces quite predictable results that look like this: Standard MERGE of all #Source rows into #Target When we MERGE into #Target, our matching criteria will be the ID field, so the normal case is to UPDATE like IDs and INSERT any new ones like this: To illustrate our case, let’s set up some very simplistic source and target tables, and populate them with some data that we can demonstrate with. MERGE can also do DELETEs from the target table, but for today we won’t explore that case. One might even get the impression that INSERT and UPDATE are no longer needed. The statement can do both at once, or simply do INSERTs or only UPDATEs. MERGE is designed to apply both UPDATE and INSERTs into a target table from a source table. Today we’ll try to explore some of the ways MERGE can be used and compare performance against equivalent INSERT and/or UPDATE statements. But naturally, curiosity got the best of me regarding the performance of this wonderful query tool. Clearly people have been doing the same thing that MERGE does using INSERT and/or UPDATE statements for many years before MERGE became available. As I wrote while exploring A Hazard of Using the SQL Merge Statement, I love MERGE because I think it is the greatest thing to happen to SQL querying since sliced bread. ![]()
0 Comments
Leave a Reply. |