CODEWITHSUNDEEP
sql-serverperformancet-sql
halit
halit

Reputation: 1128

Merge vs Select than insert update Performance Compare

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

Merge Statement

Client Statistics: for Select than insert/Update

Select Then update-insert

TableName is different on live DB. Adaptv_Report = Table1, Adaptv_Report_Temp = Table1_temp

Execution Plan for Merge Statement MErge Execution Plan

Execution Plan for Select Insert/Update enter image description here

Upvotes: 7

Views: 13345

Answers (1)

Sheldon
Sheldon

Reputation: 166

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.

Upvotes: 3

Related Questions