| Author |
 Topic  |
|
|
srujanavinnakota
Starting Member
30 Posts |
 Posted - 12/06/2012 : 18:58:21
|
Hi,
From the below query, I am trying to remove OR condition in the JOIN clause to optimize the query. Please help me with this.
Thanks in advance.
Select
empDoc. empDocId,
empDep . empDepId
empStatus.StatusDescrip
FROM empDoc WITH (NOLOCK)
INNER JOIN emp WITH (NOLOCK) ON
Emp.empID=empDoc.empID
LEFT JOIN empDep WITH (NOLOCK)
ON ((isnull(empDoc. empDepId,0) >0 AND empDep.empDepId = empDoc.empDepId)
OR (isnull(empDoc. empDepId,0)=0 AND empDep . empDepId =(SELECT empDepId FROM dbo.if_CurrentDepartment(Emp.empID))))
INNER JOIN dbo.empStatus WITH (NOLOCK)
ON (( ISNULL(empDoc. empDepId, 0) > 0 AND empStatus.empStatusID= empDep.empStatusID)
OR ( ISNULL(empDoc. empDepId, 0) = 0 AND empStatus.empStatusID = Emp.empStatusID ))
|
|
|
LoztInSpace
Aged Yak Warrior
878 Posts |
Posted - 12/06/2012 : 19:15:05
|
Your best optimisation is most likely to remove your function if_CurrentDepartment and join properly.
Depending on your data, can also move your second expression to the ISNULL - you make it zero then check for zero in your condition. That might not work if you genuinely have zeros (which would be bad database design)
And get rid of "NOLOCK". That's rarely a good thing.
|
 |
|
|
srujanavinnakota
Starting Member
30 Posts |
Posted - 12/06/2012 : 19:45:38
|
Thank you for the response. I am new to SQL, can you write it down for me please..I will try to get rid of if_CurrentDepartment function, but for now, can I rewrite by keeping the function.
quote:
Originally posted by LoztInSpace
Your best optimisation is most likely to remove your function if_CurrentDepartment and join properly.
Depending on your data, can also move your second expression to the ISNULL - you make it zero then check for zero in your condition. That might not work if you genuinely have zeros (which would be bad database design)
And get rid of "NOLOCK". That's rarely a good thing.
|
|
|
|
namman
Constraint Violating Yak Guru
USA
259 Posts |
Posted - 12/07/2012 : 13:59:58
|
For optimization, you have to provide definition of all tables (with indexes) involve, and the function.
Also, you have to provide some sample data of those tables, so that people can test to make sure the new query is correct before considering if it is faster than the current one. |
|
|
|
sodeep
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 12/07/2012 : 16:21:59
|
I think the killer is ISNULL condition in join where index won't be used. Why you need that or you have null in child table where you are trying to associate.
Maybe more information would be helpful. |
|
|
| |
Topic |
|
Need help with query optimization
