December 14, 2011 at 1:45 am
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 0.01 Sec.
But I 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.
How the Reduce the Time Please help me
December 14, 2011 at 1:58 am
Do you have any indexes?
Check out the following article for some best practices:
http://www.sql-server-performance.com/2006/tuning-joins/
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 14, 2011 at 2:00 am
Furthermore, try to get rid of functions in your join clause or where clause:
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 14, 2011 at 2:17 am
Yes i Create Index for All Tables
December 14, 2011 at 2:22 am
tamil.selvanmca (12/14/2011)
Yes i Create Index for All Tables
Is it possible to give all the information that Gail asked in the other thread?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 14, 2011 at 2:32 am
ok i will give
December 14, 2011 at 2:41 am
Please Send Your Mail Id
December 14, 2011 at 2:48 am
tamil.selvanmca (12/14/2011)
Please Send Your Mail Id
You need to post the requested information here on forum. When you reply to post attach the requested files.
December 14, 2011 at 2:57 am
Dev (12/14/2011)
tamil.selvanmca (12/14/2011)
Please Send Your Mail IdYou need to post the requested information here on forum. When you reply to post attach the requested files.
Indeed. That way everyone here on the forum can help. There are much better query tuners out there than me 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 14, 2011 at 4:16 am
Things like this:
CONVERT(DATE,LR.FromDate) BETWEEN CONVERT(DATE,@FromDate) AND CONVERT(DATE,@ToDate)
Are going to cause major performance headaches because indexes, if you have any, won't be used to find the data in LR.FromDate. Instead the table or an index will be scanned. You should not have functions on columns in the WHERE clause or JOIN operations.
You mention that all tables have indexes. But that doesn't mean that the indexes you have are good. Just throwing indexes at the tables isn't the right way to go about it. You need to examine the execution plan of the query to understand what it's doing in order to determine if indexes will help. In your case, with the one line I mention above, no amount of indexing will speed this up because it's always going to scan them. See what I mean?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 14, 2011 at 4:42 am
There's some strange date/time arithmetic going on here. If it's fixed, you won't need datetime functions around columns - there are better ways.
What datatype are the following: @ToDate, @FromDate, SP.InTime, SP.OutTime, S.InTime, S.OutTime? If you can't provide dml and ddl, can you at least provide some sample data for these date columns?
The same calculations are being performed repeatedly on the same columns throughout the query. Most of them look overcomplex. To investigate viable alternatives, I've put the calculations into a cross apply, which is then referenced in the output list and the where clause. This permits you to change a calculation in one place only, for testing.
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 SP.InTime IS NOT NULL AND x.SP_InTime > x.S_InTime10 THEN
CONVERT(VARCHAR,DATEDIFF(minute,x.S_InTime,x.SP_InTime)/60) +':'+
CONVERT(VARCHAR,DATEDIFF(minute,x.S_InTime,x.SP_InTime)%60)
ELSE'-' END AS Late,
CASE WHEN SP.ShiftID=0 THEN '0:00'
WHEN SP.OutTime IS NOT NULL AND x.SP_OutTime10 < x.S_OutTime THEN
CONVERT(VARCHAR,DATEDIFF(minute,x.S_OutTime,x.SP_OutTime)/-60) +':'+
CONVERT(VARCHAR,-(DATEDIFF(minute,x.S_OutTime,x.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
CROSS APPLY (
SELECT
SP_OutTime= CONVERT(TIME,SP.OutTime),
SP_OutTime10= CONVERT(TIME,DATEADD(MI,10,CONVERT(TIME,SP.OutTime))),
S_OutTime= CONVERT(TIME,S.OutTime),
SP_InTime= CONVERT(TIME,SP.InTime),
S_InTime= CONVERT(TIME,S.InTime),
S_InTime10= CONVERT(TIME,DATEADD(MI,10,CONVERT(TIME,S.InTime)))
) x
WHERE E.[Status] = 1
AND CONVERT(DATE,SP.InTime) BETWEEN CONVERT(DATE,@FromDate) AND CONVERT(DATE,@ToDate)
AND (x.SP_InTime > x.S_InTime10 OR x.SP_OutTime10 < x.S_OutTime)
AND SP.IsDeleted=0
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply