Update query Help

[oobern81]

Hi All,
I am having a difficult time optimizing a procedure to run more effeiently, and am hoping that someone may give some advise...
Basically, I have 2 tables:
-- Raw (staging table, max 50,000 records)
-- Lookup (over 200 million records and growing)

I have a package developed to import data from a text file into the Raw Table.. From there I have to perform updates on the Raw table to mark records as valid or not.
The raw table contains about 100 fields, of which 3 columns are used...
If data in any of these 3 columns matches any record from Lookup table, then the record in Raw Table must be updated...Lookup table is huge!

It is easy to write a simple update statement which will work, however I find the processing time is far too long!!

Below is the code from within procedure:

Update Raw
Set Col9=1000,
Col10='Invalid'

from Raw a, Lookup b
where (a.Col1 = b.Col2 or
a.Col2 = b.Col2 or
a.Col3 = b.Col2)


Does anyone have any tips on how to optimize?
Thanks in advance

oobern81

 

[markros]

If you're using SQL Server 2005+, try

;with cte as (select Pk, Col9, Col10 from Raw R
where exists (select 1 from Lookup L
where L.Col2 IN (R.Col1, R.Col2, R.Col3))

update cte set Col9 = 1000, Col10 = 'Invalid'

In order to test first, change UPDATE to select * from cte. If the selected records are correct, try using an update.


PluralSight Learning Library

 

[bborissov]

Try:

Update Raw Set Col9=1000,
               Col10='Invalid'
from Raw a
where EXISTS(SELECT * FROM lookup b
                WHERE a.Col1 = b.Col2 or
                      a.Col2 = b.Col2 or
                      a.Col3 = b.Col2)

or:

Update Raw
Set Col9=1000,
    Col10='Invalid'

from Raw a
INNER JOIN (SELECT DISTINCT Col2
                    FROM LookUp) b
where (a.Col1 = b.Col2 or
       a.Col2 = b.Col2 or
       a.Col3 = b.Col2)

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[markros]

Boris,

Vultures, right? )

PluralSight Learning Library

 

[bborissov]

Yep ))

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.