Need help with query optimization

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Need help with query optimization

Author

Topic

srujanavinnakota
Starting Member

34 Posts

Posted - 2012-12-06 : 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

940 Posts

Posted - 2012-12-06 : 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

34 Posts

Posted - 2012-12-06 : 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

285 Posts

Posted - 2012-12-07 : 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
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-07 : 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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources