April 9, 2010 at 1:29 pm
I have a query that joins several tables.
It basically looks like:
SELECT TOP 10000 EmpID, EmpFName, EmpLName, DeptID, DeptName, DeptType, OtherStuff
FROM dbo.Employee AS E INNER JOIN
dbo.Department AS D ON E.DeptFID = D.DeptID INNER JOIN OtherStuff....
WHERE (D.DeptType = 1 AND E.Type <> 3) OR (D.DeptType = 2 AND E.Type = 3 )
It brings back 97 rows when I run it.
I can run two separate queries (the same query as above but broken apart at the OR statement:
Query1:
SELECT TOP 10000 EmpID, EmpFName, EmpLName, DeptID, DeptName, DeptType, OtherStuff
FROM dbo.Employee AS E INNER JOIN
dbo.Department AS D ON E.DeptFID = D.DeptID INNER JOIN OtherStuff....
WHERE (D.DeptType = 1 AND E.Type <> 3)
Query2:
SELECT TOP 10000 EmpID, EmpFName, EmpLName, DeptID, DeptName, DeptType, OtherStuff
FROM dbo.Employee AS E INNER JOIN
dbo.Department AS D ON E.DeptFID = D.DeptID INNER JOIN OtherStuff....
WHERE (D.DeptType = 2 AND E.Type = 3 )
---
Separating out the queries causes them to execute in less than 2 seconds.
Running them as a single OR statement causes them to execute in 25 to 30 seconds.
I tried using NOLOCK for each table, just to see if it was some sort of locking issue, but that didn't seem to cause any substantial increase in performance. Also, I don't intend to run them with NOLOCK anyway other than for testing due to the various (though rare) issues that can result from using NOLOCK.
What should I look at to help explain why the execution time is so much slower when using the OR statement in the WHERE clause rather than just running two separate queries?
April 9, 2010 at 1:33 pm
The OR clause is probably making it do a table scan (clustered index scan), instead of using individual indexes that work in the separate queries.
What you can do, if you can't get it to work otherwise, is hook the two separate queries together vertically with a Union or Union All statement. That does the same thing as an Or clause, and is sometimes much faster.
Before you do that, though, dig into the execution plan a bit and see if you can work out an index that will handle the Or clause better.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply