update/join query problem

[ktucci]

when i run the following sql script the update portions seems to take hours..it has been 2 so far...the temp_20010331_02 has only 120000 records each of the 2 queries return between 15000-20000 records and the update should effect only 4000 records...why is this taking so long or is there a better way to do this....
btw...when i remove the update and try to run the same query as a select it takes a few seconds...

go
print "drop ##aaa06"
drop table ##aaa06

go
SELECT DISTINCT SD_LINE_ID, type, carrier
INTO ##aaa06
FROM dbo.temp_20010331_02
WHERE (type IS NOT NULL) AND (carrier LIKE '- 0%')

go
print "drop ##aaa07"
drop table ##aaa07

go
SELECT DISTINCT SD_LINE_ID, type, carrier
INTO ##aaa07
FROM dbo.temp_20010331_02
WHERE (carrier NOT LIKE '- 0%')


go
print "update final join"

UPDATE temp_20010331_02
SET temp_20010331_02.carrier = ##aaa07.carrier
FROM ##aaa06 INNER JOIN
##aaa07 ON
##aaa06.SD_LINE_ID = ##aaa07.SD_LINE_ID AND
##aaa06.type = ##aaa07.type

 

[tlbroadbent]

The queries will not likely use indexes because of the "LIKE", "NOT LIKE" and "IS NOT" criteria. Therefore, all access to the tables will be by table scan. Certainly that is slower than indexed reads.

How long are the Select Into queries running? If they seem to run long, try creating the temp tables first and doing Insert Into ... Select queries instead of Select Into queries.

Try creating an index in the two temp tables on the sd_line_id and type columns to speed the update query. Terry

X-) "Life would be easier if I had the source code." -Anonymous