Jonathan Marshall (11/12/2013)
The queries are updating lookup tables that do not have a value by using a subquery to the lookup table.I like that idea of using a computed column as these will be scanning through lots of data.
WHERE [column1] + [column2] NOT IN
(SELECT [column3] + [column4] from dbo.table order by [column1] + [column2])
Step 1: remove the ORDER BY, it's useless.
Step 2: add a computed, persisted, indexed column on your source table
Step 3: add a computed, persisted, indexed column on your lookup table
Step 4: final query:
WHERE tableY.computedColumns1and2 NOT IN
(SELECT table.computedColumns3and4 from dbo.table)
Unfortunately, you'll usually still end up with an index scan on the large table since each and every value needs to be verified (except for the source table holding values of 1 .. 9 and the lookup table only holds 1 .. 2 (as a basic example), then there might be a chance of an index seek...
My approach would be an index as narrow as possible with just the computed column being a key column and the other values required being used as included columns.