Mayank Nagar's Blog-mayanknagar.blogspot.com

Mayank Nagar's Blog

Time 2021-10-21 13:05:10

Web Name: Mayank Nagar's Blog

WebSite: http://mayanknagar.blogspot.com

ID:209047

Keywords:

Mayank,Nagar,Blog,

Description:

keywords:
description:

Mayank Nagar's Blog

Wednesday, January 1, 2020 Performance of the SQL MERGE vs. INSERT/UPDATE
MERGEis designed to apply both UPDATE and INSERTs into a target table from a sourcetable. The statement can do both at once, or simply do INSERTs or onlyUPDATEs. One might even get the impression that INSERT and UPDATE are nolonger needed. Why not always use MERGE? MERGEcan also do DELETEs from the target tableSample Data and Some Basic Examples Toillustrate our case, lets set up some very simplistic source and targettables, and populate them with some data that we can demonstrate with. CREATE TABLE #Target ( ID BIGINT PRIMARY KEY , Value INT );
CREATE TABLE #Source ( IDBIGINT PRIMARY KEY , Value INT );
INSERT INTO #Target VALUES ( 1, 2342 ), ( 2, 345 );INSERT INTO #Source VALUES ( 1, 975 ), ( 3, 683 );Whenwe MERGE into #Target, our matching criteria will be the ID field, so thenormal case is to UPDATE like IDs and INSERT any new ones like this: --Standard MERGE of all #Source rows into #TargetMERGE #Target t USING #Source s ON s.ID = t.ID WHEN MATCHED THEN UPDATE SET Value = s.Value WHEN NOT MATCHED THEN INSERT ( ID, Value ) VALUES ( s.ID , s.Value );
SELECT * FROM #Target;Thisproduces quite predictable results that look like this: IDValue197523453683Letschange the values in our #Source table, and then use MERGE to only do anUPDATE. --Change the values of our source rowsUPDATE #Source SET Value = CASE ID WHEN 1 THEN 555 WHEN 3 THEN 999 END
--MERGE that only updates MERGE #Target t USING #Source s ON s.ID = t.ID WHEN MATCHED THEN UPDATE SET Value = s.Value;
SELECT * FROM #Target;Theresults now in #Target are: IDValue155523453999Finally,we know we can also use MERGE to replace INSERT by omitting the MATCHEDclause. Lets INSERT a new row into #Source and do this. --Insert a new row into our source INSERT #Source VALUES ( 4, 242 );
--MERGE that only inserts MERGE #Target t USING #Source s ON s.ID = t.ID WHEN NOT MATCHED THEN INSERT ( ID, Value ) VALUES ( s.ID , s.Value );
SELECT * FROM #Target;Unsurprisingly,the results now in #Target are these: IDValue1555234539994242Sorryif you know all this stuff and Ive bored you, but we needed to get thesebasics out of the way. Exploring the Performance of MERGE Toeffectively test the performance of our alternatives, well need to set up atest harness with a non-trivial number of rows in our #Source and #Targettables. You can open the Test Harness 1.sql file in theresources section of this article and follow along. The basic set-up datais as follows. TRUNCATE TABLE #Source;TRUNCATE TABLE #Target;
WITH Tally ( n ) AS ( SELECT TOP 1000000 ROW_NUMBER() OVER ( ORDER BY ( SELECT NULL) ) FROM sys.all_columns a CROSS JOIN sys.all_columns b ) INSERT INTO #Target SELECT 2 * n , 1 + ABS(CHECKSUM(NEWID())) % 1000 FROM Tally;
WITH Tally ( n ) AS ( SELECT TOP 1000000 ROW_NUMBER() OVER ( ORDER BY ( SELECT NULL) ) FROM sys.all_columns a CROSS JOIN sys.all_columns b ) INSERT INTO #Source SELECT CASE WHEN n = 500000 THEN 2 * n - 1 ELSE 2 * n END , 1 + ABS(CHECKSUM(NEWID())) % 1000 FROM TallWevepurposely set up our source table so that the INSERTs it will do when mergedwith the target are interleaved with existing records for the first 500,000rows. UsingSQL Profiler, well compare two identical query scripts: --MERGE MERGE #Target t USING #Source s ON s.ID = t.ID WHEN MATCHED THEN UPDATE SET Value = s.Value WHEN NOT MATCHED THEN INSERT ( ID, Value ) VALUES ( s.ID , s.Value );
--TRUNCATE and re-populate Source and Target tables--UPDATE/INSERTBEGIN TRANSACTION T1; UPDATE t SET Value = s.Value FROM #Target t JOIN #Source s ON s.ID = t.ID;
INSERT INTO #Target SELECT s.ID , s.Value FROM #Source s LEFT JOIN #Target t ON s.ID = t.ID WHERE t.ID IS NULL;
COMMIT TRANSACTION T1;Bothof these INSERT 500,000 rows and UPDATE 500,000 rows, the latter enclosed in aTRANSACTION. We omit error handling and possible ROLLBACK of thetransaction for simplicity. The results that we obtained running the testharness in SQL Profiler (5 runs) are as follows: QueryCPU Reads Writes DurationMERGE4492 4513786 2578 4864INSERT/UPDATE 3588 30724895496 3847MERGE4820 4514303 2899 5253INSERT/UPDATE 3572 30729794007 4035MERGE4462 4513793 2571 4770INSERT/UPDATE 3635 30724635497 3829MERGE4524 4513795 2931 4800INSERT/UPDATE 3588 30724745505 3665MERGE4648 4513814 2939 4955INSERT/UPDATE 3479 30724915522 3716Theseindicate that MERGE took about 28% more CPU and 29% more elapsed time than theequivalent INSERT/UPDATE. Not surprising considering all the complexitythat MERGE must handle, but possibly forgivable for the additional safety,convenience and simplicity it represents (no need for a TRANSACTION with errorhandling). Using MERGE as a Substitute for INSERT Totest the performance of MERGE as a substitute for INSERT, the same test harnessthat set up the #Target table can be used, but well change the set up for the#Source table as follows. WITH Tally (n) AS(SELECTTOP1000000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))FROMsys.all_columnsa CROSSJOINsys.all_columnsb)INSERT INTO #SourceSELECT CASE WHEN n = 500000 THEN 2*n-1 ELSE 2000000+n END,1+ABS(CHECKSUM(NEWID()))%1000FROM Tally;Aquick check of the row counts generated from Test Harness #2.sqlconfirms that both MERGE and INSERT insert exactly 1,000,000 rows. Theresults from a Profiler trace using these queries give us this: QueryCPU Reads Writes DurationMERGE5054 6024150 2397 5576INSERT4992 6248001 7563 5507MERGE 52266024165 3868 5529INSERT5383 6248005 7571 6298MERGE5257 6023557 3689 5473INSERT4851 6247403 7431 5546MERGE5273 6023589 2167 5662INSERT4914 6247440 7427 5281MERGE5179 6024619 1426 5476INSERT5039 6248483 6211 5954Inthis case, CPU and elapsed time are probably too close to call. CPU usagewas about 3% more for the MERGE and elapsed time was about 3% less for theMERGE. Using MERGE as a Substitute for UPDATE Onceagain, to test this well change the test harness only for the #Source table sothat it creates the rows with exactly the same IDs as the #Target table. WITH Tally (n) AS(SELECTTOP1000000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))FROMsys.all_columnsa CROSSJOINsys.all_columnsb)INSERT INTO #SourceSELECT 2*n,1+ABS(CHECKSUM(NEWID()))%1000FROM Tally;Afterconfirming that both MERGE and UPDATE update 1,000,000 rows, our Profile tracefrom running Test Harness #3.sql gave us these results. QueryCPU Reads Writes DurationMERGE1903 7982 2568 2010UPDATE1763 7954 2568 1840MERGE1904 7986 2576 2303UPDATE1809 7955 2560 1974MERGE1903 7968 2576 1951UPDATE1763 7940 2568 2005MERGE1918 7957 2568 2009UPDATE1731 7464 2584 1809MERGE1903 8005 2560 2023UPDATE1732 7977 2584 2063MERGE was just about 9% more costly in CPU and 7% more in elapsed time and with this UPDATE seems to be more advantageous.
Posted bymayank nagarat8:19 AMNo comments: HomeSubscribe to:Posts (Atom)My Blog ListSQL ServerIs Truncate Table DDL or DML Statement? - Many times, categories of concepts and things overlap. It can be hard to categorize some items in a single category. The SQL TRUNCATE TABLE statement is an...Rare Himalayan flowers
HeavenWonderful Himalaya
Shivalingam in HimalayaRainbow of Life
colorsWho am I ?About Memayank nagarView my complete profileBlog Archive 2020(1) January(1)Performance of the SQL MERGE vs. INSERT/UPDATE 2015(1) September(1) 2014(1) October(1) 2013(2) December(2) 2009(8) July(2) June(1) May(1) April(1) January(3) 2008(2) August(2)

TAGS:Mayank Nagar Blog

<<< Thank you for your visit >>>

Websites to related :
Red Cross

　　keywords:
description:

Flevy.com - Marketplace for Busi

Visit Grand Junction, Colorado |

　　keywords:visit grand junction, grand junction colorado, grand junction tourism, grand junction vacations, official website grand junction tourism, col

Conference Series LLC Ltd: Inter

　　keywords:
description:
Get App Meet Inspiring Speakers and Experts at our 3000+ Global Conference Series LLC LTD Events with over 1000+ Conferences,

Word importable meaning. Word im

　　keywords:
description:What is a importable, definition of importable, meaning of importable, importable anagrams, words starting with importable.
Twee

PyMOTW

　　keywords:
description:
Redirecting to Python 3 examples.

da Informative Blog

　　keywords:
description:An informative weblog, which covers tips on how to make money, blogging, tech, social media, pc, mobile and general tips and tri

gumbyframeworkcom

　　keywords:
description:

NBN Weathershots

　　keywords:The best weather shots supplied by our community of weather photograhers
description:

Home - Murphy Reed P.L.C. » We

　　keywords:
description:We are MURPHY & REED, P.L.C. lawyers in Kalamazoo are proud of their proven record for improving personal injury clients lives F

ads

Hot Websites

WHY YOU SHOULD Be listed

Matt Cutts, a software engineer and former head of the web spam team at Google, stated back in 2012 that "spammy directories" are disregarded while those who "excercise editorial discretion" worth it. Even though countless business directories litter the internet, they won't all offer a positive change. Don't take unneeded risks and suggest your website to one that Google loves!

Email: liunxqq$$$126.com ($$$=@) ie websites |