February 10, 2009 at 9:04 am
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?
update dbo.table1
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)
go
Columbus, GA SQL Server User Group
http://columbusga.sqlpass.org/
February 10, 2009 at 9:28 am
Guest Columnist Ken Simmons suggested I look at EXCEPT. Sounds just like a left join. I'll post back and let readers know it it improved my query exec time.
From MSDN: "EXCEPT
Returns any distinct values from the query to the left of the EXCEPT operand that are not also returned from the right query."
Columbus, GA SQL Server User Group
http://columbusga.sqlpass.org/
February 10, 2009 at 9:34 am
If you can, post the structure and the execution plan. Just because the DTA didn't find a good index doesn't mean one shouldn't be created. That tool is spotty at the best of times.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 10, 2009 at 9:39 am
I assume you have the ID columns indexed on both tables. A "not exists" subquery might help, but as Grant said, you'd need to post more details and do some testing.
February 10, 2009 at 10:38 am
SELECT ID
FROM table1
EXCEPT
SELECT ID
FROM table2
Runs MUCH faster. Thanks.
Columbus, GA SQL Server User Group
http://columbusga.sqlpass.org/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply