This query runs for 20+ minutes on a powerful 4 CPU server wtih 16GB RAM and DBs on fibre channel SAN. It is not being blocked. I looked at the plan and the hash join is the largest cost. I ran it through tunning wiz and there are no index recommendations. Table1 has close to 1 million rows. I know with a left join it has to scan through all records. Is it possible to write this using a subquery, temp table or other method?
set field1 = 'flag'
FROM dbo.table1 LEFT JOIN
dbo.table2 ON dbo.table1.ID = dbo.table2.ID
WHERE (dbo.table1.ID > '0') AND (dbo.table2.ID IS NULL)
Columbus, GA SQL Server User Group