Merge with insert selection selection

Which query improves the performance of Query1 or Query2,

Query1 uses the merge operator, Query2 uses the standard option Choose how to insert the update.

I could not decide, because the Merge operator uses as a side comparison, Side 1: Table 1> TAble1_Temp Side 2: Table1_Appliance> Table1

Standard selection compares data on one side Table1_Temp> Table1, (exists or not)

Thank you for agreeing.

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 lines with lines of 2.68 M.

Select Insert / Update. Run time seems better than Merge.

Any idea?

Client Statistics: For Merge Operator

Merge statement

: , /

Select Then update-insert

TableName live DB. Adaptv_Report = 1, Adaptv_Report_Temp = Table1_temp

Merge MErge Execution Plan

/ enter image description here

+4
1

: MERGE . MERGE UPDATE/INSERT.

1) , . , . , .

2) , . , , , MD5 , . - , . .

+1

Source: https://habr.com/ru/post/1548464/


All Articles