SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Need help with query optimization
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

srujanavinnakota
Starting Member

34 Posts

Posted - 12/06/2012 :  18:58:21  Show Profile  Reply with Quote
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

938 Posts

Posted - 12/06/2012 :  19:15:05  Show Profile  Reply with Quote
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.
Go to Top of Page

srujanavinnakota
Starting Member

34 Posts

Posted - 12/06/2012 :  19:45:38  Show Profile  Reply with Quote
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.


Go to Top of Page

namman
Constraint Violating Yak Guru

USA
263 Posts

Posted - 12/07/2012 :  13:59:58  Show Profile  Reply with Quote
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.
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/07/2012 :  16:21:59  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000