Performance Issue - Left Join vs Update


Performance Issue - Left Join vs Update

Hi everyone, I have 2 tables as below:

Main (20 mil rows, 50 other columns):

Col1  Col2  Col3

a       2      3

b       2      2

Join (50 mil rows, 20 other columns):

Col1  Col2  Col3

a       1      1

a       2      2

a       3      3

b       1      1

b       2      3

b       3      2

Join condition:

Main.Col1 = Join.ColA

Main.Col2 ge Join.ColB

Main.Col3 ge Join.ColC

Qualify 1 = row_number() over (partition by ColA order by ColB desc ColC desc)

In the past (before Col2 and Col3 were introduced) I used:

ALTER TABLE Main to add 20 columns

UPDATE Main, Join etc...

Since we can't do qualify on UPDATE and the join condition above will yield the error "Target row updated by multiple source rows", one way of doing it will be left joining Main and Join (+ qualify).

Are there more efficient ways?