Starr Duskk
asked on
EXISTS with AND/OR is killing me softly ....
I am using SQL Server 2008 R2. I have an EXISTS with an OR EXISTS... When I use either one by itself, it takes less than a second. But when the two are together as an OR, it takes 54 seconds to run. Here is the clause that's killing me:
Am I doing something wrong on my AND/OR?
BTW, this query, when completed, returns 1 row. The top half returns one row, the second have returns 0 rows.
thanks!
P.S. Just for kicks I also tried an IN and it gives the same amount of time:
AND (
EXISTS (
select 1
from EmployeeUnitJobTypeUnitView
INNER JOIN Unit ON Unit.UnitId = EmployeeUnitJobTypeUnitView.UnitId
where EmployeeUnitJobTypeUnitView.EmployeeId = Employee.EmployeeId
AND Unit.StateCode
IN (219)
)
OR EXISTS (
select 1
from EmployeeAccessZoneDistrictUnitView
INNER JOIN Unit ON Unit.UnitId = EmployeeAccessZoneDistrictUnitView.UnitId
where EmployeeAccessZoneDistrictUnitView.EmployeeId = Employee.EmployeeId
AND Unit.StateCode
IN (219)
)
Am I doing something wrong on my AND/OR?
BTW, this query, when completed, returns 1 row. The top half returns one row, the second have returns 0 rows.
thanks!
P.S. Just for kicks I also tried an IN and it gives the same amount of time:
AND
(
Employee.EmployeeId IN
(Select EmployeeId from EmployeeUnitJobTypeUnitView
INNER JOIN Unit ON Unit.UnitId = EmployeeUnitJobTypeUnitView.UnitId
AND Unit.StateCode
IN (219) )
OR
Employee.EmployeeId IN
(Select EmployeeId from EmployeeAccessZoneDistrictUnitView
INNER JOIN Unit ON Unit.UnitId = EmployeeAccessZoneDistrictUnitView.UnitId
AND Unit.StateCode
IN (219) )
)
ASKER
This query, with a FULL OUTER JOIN and a coalesce took less than a second and returns the same 3 rows:
SELECT Distinct COALESCE(table1.EmployeeId, table2.EmployeeId) AS EmployeeId
from
(SELECT Distinct Employee.EmployeeId
FROM Employee
where
Employee.ClientId = 1
AND
EXISTS (
select 1
from EmployeeUnitJobTypeUnitView
INNER JOIN Unit ON Unit.UnitId = EmployeeUnitJobTypeUnitView.UnitId
where EmployeeUnitJobTypeUnitView.EmployeeId = Employee.EmployeeId
AND Unit.StateCode
IN (219)
)
) AS TABLE1
FULL OUTER JOIN (
SELECT Distinct Employee.EmployeeId
FROM Employee
where
Employee.ClientId = 1
AND EXISTS (
select 1
from EmployeeAccessZoneDistrictUnitView
INNER JOIN Unit ON Unit.UnitId = EmployeeAccessZoneDistrictUnitView.UnitId
where EmployeeAccessZoneDistrictUnitView.EmployeeId = Employee.EmployeeId
AND Unit.StateCode
IN (219)
)
) table2 ON
table2.Employeeid = table1.Employeeid
ORDER BY
Employeeid
ASKER
Oh, and you might ask, "Well, why don't you just use the FULL OUTER JOIN?"
Because when building this query based on search/filter values, I could have a lot of these AND EXISTS/OR EXISTS on various tables. (Six to be exact.)
So not sure how I would pull that off with 6 different ones versus 6 different ones. Because this AND/OR has to filter, then this next AND/OR has to filter, etc.
thanks.
Because when building this query based on search/filter values, I could have a lot of these AND EXISTS/OR EXISTS on various tables. (Six to be exact.)
So not sure how I would pull that off with 6 different ones versus 6 different ones. Because this AND/OR has to filter, then this next AND/OR has to filter, etc.
thanks.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
figured it out.
ASKER
Just to show stuff at the top and an entire query, this simple one (without further AND clauses for filtering), took 2:33. It returns 3 rows:
Open in new window