Merge vs Select than insert update Performance Compare
performance, sql-server, t-sql
Solution
Addressing the underlying question of performance: The MERGE statement frequently performs poorly when executed against a large number of records. There are ways to improve both the MERGE and the UPDATE/INSERT statements' performance.
1) Perform the operations in batches rather than against the full set of data. This can be done several ways one of which is to restrict the queries to a specific range of key values for each batch. Each batch execution would be performed against a different range of keys until the full range of keys had been used.
2) Only do updates on records for which the source and target data differ. An easy way to determine if the records differ is to create a computed column on the target and source tables such that the computed column contains an MD5 hash of the columns to be updated. If the source hash differs from the target hash, do the update. Otherwise don't update the record.
Problem
Which query is improve performance Query1 or Query2, Query1 uses merge statement, Query2 uses standart Select than insert update. I couldn't decide because Merge statement uses both side compare, Side 1 : Table1 > TAble1_Temp Side 2 : Table1_Tempt > Table1 Standart select compares data single side Table1_Temp > Table1, (exists or not) Thanks for advange. Query1 ``` MERGE Table1 AS T USING Table1_Temp AS S ON (T.col1= S.col1 and T.col2= S.col2) WHEN NOT MATCHED BY TARGET THEN INSERT(col1, col2,col3,col4,col5,col6,col7,col8,col9,col10,col11) VALUES(S.col1, S.col2,S.col3,S.col4,S.col5,S.col6,S.col7,S.col8,S.col9,S.col10,S.col11) WHEN MATCHED THEN UPDATE SET T.col3= S.col3,T.col4 = S.col4,T.col5=S.col5,T.col6=S.col6,T.col7=S.col7 ,T.col8= S.col8,T.col9= S.col9,T.col10= S.col10,T.col11= S.col11 ; ``` Query2 ``` UPDATE Table1 SET col3 = Table1_Temp.col3, col4 = Table1_Temp.col4, col5 = Table1_Temp.col5, col6 = Table1_Temp.col6, col7 = Table1_Temp.col7, col8 = Table1_Temp.col8, col9 = Table1_Temp.col9, col10 = Table1_Temp.col10, col11 = Table1_Temp.col11, FROM Table1 INNER JOIN Table1_Temp ON Table1.col1 = Table1_Temp.col1 and Table1.col2= Table1_Temp.col2 Insert Into Table1(col1, col2,col3,col4,col5,col6,col7,col8,col9,col10,col11) Select col1, col2,col3,col4,col5,col6,col7,col8,col9,col10,col11 from Table1_Temp S Where not exists (Select * from Table1 where S.col1 = Table1.col1 and S.col2 = Table1.col2) ``` 2.680.000 rows in table1 50.000 rows in table1_temp Compare 50.000 rows with 2.68 M rows. "Select Insert/Update" Execution time seems better than Merge. Any Idea ? Client Statistics: for Merge Statement Client Statistics: for Select than insert/Update TableName is different on live DB. Adaptv_Report = Table1, Adaptv_Report_Temp = Table1_temp Execution Plan for Merge Statement Execution Plan for Select Insert/Update