Surrogate Key SQL - Bad Performance

Database
N/A

Surrogate Key SQL - Bad Performance

Hi,

I have below SQL which leads to a product join because of the OR join conditions.  It updates several surrogate keys at once in the STAGETABLE, but for each surrogate key, the join columns on the STAGETABLE are different (the ugly string concatenations)

Has somebody an idea how I could rewrite this UPDATE in order to have a better performing join (such as a MERGE JOIN)?

Thanks in advance,

Roland

UPDATE t01 FROM STAGETABLE t01,

 KeyTable t02 

SET SKEY=CASE WHEN COALESCE ( TRIM ( t01.COL2) ,'') || '|' || COALESCE ( TRIM ( t01.COL1) ,'') = t02.NAT_COL3 

AND ( TRIM(COL1)<>'' ) THEN t02.COL3 ELSE NULL END ,SK1=CASE WHEN COALESCE ( TRIM ( t01.COL2) ,'') || '|' || COALESCE ( TRIM ( t01.COL4) ,'') = t02.NAT_COL3 

AND ( TRIM(COL4)<>'' ) THEN t02.COL3 ELSE NULL END ,SK2=CASE WHEN COALESCE ( TRIM ( t01.COL2) ,'') || '|' || COALESCE ( TRIM ( t01.COL5) ,'') = t02.NAT_COL3 

AND ( TRIM(COL5)<>'' ) THEN t02.COL3 ELSE NULL END ,SK3=CASE WHEN COALESCE ( TRIM ( t01.COL2) ,'') || '|' || COALESCE ( TRIM ( t01.COL6) ,'') = t02.NAT_COL3 

AND ( TRIM(COL6)<>'' ) THEN t02.COL3 ELSE NULL END ,SK4=CASE WHEN COALESCE ( TRIM ( t01.COL2) ,'') || '|' || COALESCE ( TRIM ( t01.COL7) ,'') = t02.NAT_COL3 

AND ( TRIM(COL7)<>'' ) THEN t02.COL3 ELSE NULL END 

WHERE 'THE_NAT_KEY_TYPE' =t02.NAT_KEY_TYPE

 AND ((COALESCE ( TRIM ( t01.COL2) ,'') || '|' || COALESCE ( TRIM ( t01.COL1) ,'') = t02.NAT_COL3 )

  OR (  COALESCE ( TRIM ( t01.COL2) ,'') || '|' || COALESCE ( TRIM ( t01.COL4) ,'') = t02.NAT_COL3 ) OR

  (  COALESCE ( TRIM ( t01.COL2) ,'') || '|' || COALESCE ( TRIM ( t01.COL5) ,'') = t02.NAT_COL3 ) OR

   (  COALESCE ( TRIM ( t01.COL2) ,'') || '|' || COALESCE ( TRIM ( t01.COL6) ,'') = t02.NAT_COL3 ) OR

    (  COALESCE ( TRIM ( t01.COL2) ,'') || '|' || COALESCE ( TRIM ( t01.COL7) ,'') = t02.NAT_COL3 ) ) ;

Roland Wenzlofsky