Need Optimization the Query

Question

Need Optimization the Query

tamil.selvanmca

Ten Centuries

Points: 1367
More actions
December 14, 2011 at 1:49 am

#261598

SELECT
E.EmployeeID,
E.EmpCode AS "Emp. Code",
E.FName AS "Emp. Name",
D.DeptName AS Department,
De.Descriptions As Designation,
CONVERT(VARCHAR,SP.InTime,103) AS ShiftDate,
S.Descriptions AS Shift,
RIGHT(CONVERT(VARCHAR,S.InTime,100),7) +' - ' + RIGHT(CONVERT(VARCHAR,S.OutTime,100),7) AS ShiftTime,
ISNULL(RIGHT(CONVERT(VARCHAR,SP.InTime,100),7),'00:00') + ' - ' + ISNULL(RIGHT(CONVERT(VARCHAR,SP.OutTime,100),7),'00:00')AS InOut,
CASE WHEN SP.ShiftID=0 THEN '0:00'
WHEN E.EmployeeID = SP.EmployeeID AND SP.ShiftID=S.HShiftID AND SP.InTime IS NOT NULL AND
CONVERT(TIME,SP.InTime) > CONVERT(TIME,DATEADD(MI,10,CONVERT(TIME,S.InTime)))THEN
CONVERT(VARCHAR,DATEDIFF("MINUTE",CONVERT(TIME,S.InTime),CONVERT(TIME,SP.InTime))/60) +':'+
CONVERT(VARCHAR,DATEDIFF("MINUTE",CONVERT(TIME,S.InTime),CONVERT(TIME,SP.InTime))%60)
ELSE'-' END AS Late,
CASE WHEN SP.ShiftID=0 THEN '0:00'
WHEN E.EmployeeID = SP.EmployeeID AND SP.ShiftID = S.HShiftID AND SP.OutTime IS NOT NULL AND
CONVERT(TIME,DATEADD(MI,10,CONVERT(TIME,SP.OutTime)))<CONVERT(TIME,S.OutTime) THEN
CONVERT(VARCHAR,DATEDIFF("MINUTE",CONVERT(TIME,S.OutTime),CONVERT(TIME,SP.OutTime))/-60) +':'+
CONVERT(VARCHAR,-(DATEDIFF("MINUTE",CONVERT(TIME,S.OutTime),CONVERT(TIME,SP.OutTime)))%-60)
ELSE'-' END AS EarlyDepature
FROM dbo.HEmployee E
INNER JOIN HShiftPost SP ON SP.EmployeeID = E.EmployeeID AND E.IsRoll = 0
INNER JOIN Department D ON E.DeptID = D.DeptCode
INNER JOIN HShift S ON SP.ShiftID = S.HShiftID
INNER JOIN Designation De ON E.DesignationID = De.DesignationID
INNER JOIN [dbo].[HInstitution] I ON E.InstitutionID = I.InstitutionID
INNER JOIN [dbo].[DesignationCategory] DC ON DC.DesignationCategoryID = De.DesignationCategoryID
WHERE E.Status = 1 AND CONVERT(DATE,SP.InTime) BETWEEN CONVERT(DATE,@FromDate) AND CONVERT(DATE,@ToDate) AND
(CONVERT(TIME,SP.InTime) > CONVERT(TIME,DATEADD(MI,10,CONVERT(TIME,S.InTime))) OR CONVERT(TIME,DATEADD(MI,10,CONVERT(TIME,SP.OutTime)))<CONVERT(TIME,S.OutTime))
AND SP.IsDeleted=0
The Above Query Will Execute 00.01 Sec
But I Have Include the Line
LEFT OUTER JOIN HLeaveRequestLR ON Shift.EmployeeID <> LR.EmployeeID AND LR.LeaveTypeID = 6
AND CONVERT(DATE,LR.FromDate) BETWEEN CONVERT(DATE,@FromDate) AND CONVERT(DATE,@ToDate)
It Take 12 to 15 Min

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Koen Verbeeck SSC Guru Points: 259197 More actions · Answer 2

Please don't crosspost. It wastes people's time and fragments replies.

Original thread with replies can be found here:

http://www.sqlservercentral.com/Forums/Topic1221436-391-1.aspx

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP