Query Fine-Tuning
-
I have a query that takes about 7 seconds to run in QA. This query lists all employees who are currently clocked in or those who are at lunch. Anything less than 4 seconds is acceptable
SELECT
Employee.FullName, Employee.Extension,
EmployeeDepartments.Description,
CASE WHEN EXISTS (
SELECT 1 FROM TimeClock
WHERE (TimeClock.EmployeeID = Employee.ID) AND
(CONVERT(varchar(12), GETDATE(), 101) = CONVERT(varchar(12), TimeClock.StartTime, 101)) AND --- Clocked in Today
(TimeClock.StopTime IS NULL) AND --- Have not clocked out yet
(TimeClock.Type <> 5) AND -- Type 5 is Lunch
(TimeClock.Deleted = 0))
THEN 1 ELSE 0 END AS ClockedIn,
CASE WHEN EXISTS (
SELECT 1 FROM TimeClock TimeClock_1
WHERE (TimeClock_1.EmployeeID = Employee.ID) AND
(CONVERT(varchar(12), GETDATE(), 101) = CONVERT(varchar(12), TimeClock_1.StartTime, 101)) AND --- Clocked in Today
(TimeClock_1.StopTime IS NULL) AND --- Have not clocked out yet
(TimeClock_1.Type = 5) AND -- Type 5 is Lunch
(TimeClock_1.Deleted = 0))
THEN 1 ELSE 0 END AS OnLunch
FROM Employee INNER JOIN
EmployeeDepartments ON Employee.Department = EmployeeDepartments.ID
WHERE (Employee.Status = 1) --- Active Employees
I have indexes on TimeClock.EmployeeID and TimeClock.StopTime
Can the performance of this query be improved ?
TIA
-
Try this
SELECT
Employee.FullName, Employee.Extension,
EmployeeDepartments.Description,
Case when TimeClock.Type <> 5 then 1 else 0 end as ClockedIn,
Case when TimeClock.Type = 5 then 1 else 0 end as OnLunch
FROM Employee
INNER JOIN EmployeeDepartments
ON Employee.Department = EmployeeDepartments.ID
INNER JOIN TimeClock
on Employee.ID = TimeClock.EmployeeID
and (TimeClock.startTime between (round(getDate() - 0.5, 0)) and (round(getDate() - 0.5, 0) + 1))
and TimeClock.StopTime is null
and TimeClock.deleted = 0
WHERE (Employee.Status = 1) --- Active Employees
Might be more efficient due to inner join rather than what is effectively a nested query repeated twice. If it doesn't help, post table definitions and index definitions here as well as the query plan (set Showplan On). I personally prefer the graphical query plan that query analyser can give you - to turn it on, look in the "Query" menu in QA and turn on showing the execution plan. There are articles here that explain what the different areas of the plan mean. Good luck!
-
An employee can have more than 1 time clock record in a day. He could be clocked in on multiple jobs. This returns multiple rows for each employee.
I get an error when I run the above query --
Implicit conversion from data type datetime to float is not allowed. Use the CONVERT function to run this query.
[Employee] (
[ID] [int] IDENTITY (1, 1) NOT NULL , ---- Primary Key
[FullName] [varchar] (60) NULL ,
[Department] [smallint] NULL ,
[Extension] [varchar] (10) NULL,
[Status] [tinyint] NULL )
[TimeClock] (
[ID] [int] IDENTITY (1, 1) NOT NULL , ---- Primary Key
[Type] [tinyint] NOT NULL ,
[EmployeeID] [int] NOT NULL ,
[StartTime] [datetime] NULL ,
[StopTime] [datetime] NULL ,
[Deleted] [bit] NOT NULL )
Index on EmployeeID and StopTime
[EmployeeDepartments] (
[ID] [int] IDENTITY (1, 1) NOT NULL , ---- Primary Key
[Description] [varchar] (25) NOT NULL )
|--Compute Scalar(DEFINE([Expr1007]=If [Expr1011] then 1 else 0, [Expr1010]=If [Expr1012] then 1 else 0))
|--Nested Loops(Left Semi Join, OUTER REFERENCES([Employee].[ID]), DEFINE([Expr1012] = [PROBE VALUE]))
|--Nested Loops(Left Semi Join, OUTER REFERENCES([Employee].[ID]), DEFINE([Expr1011] = [PROBE VALUE]))
| |--Nested Loops(Inner Join, OUTER REFERENCES([Employee].[Department]))
| | |--Clustered Index Scan(OBJECT([mi].[dbo].[Employee].[PK_Employee]), WHERE([Employee].[Status]=1))
| | |--Clustered Index Seek(OBJECT([mi].[dbo].[EmployeeDepartments].[PK_EmployeeDepartments]), SEEK([EmployeeDepartments].[ID]=Convert([Employee].[Department])) ORDERED FORWARD)
| |--Filter(WHERE((Convert(getdate())=Convert([TimeClock].[StartTime]) AND [TimeClock].[Type]<>5) AND Convert([TimeClock].[Deleted])=0))
| |--Index Spool(SEEK([TimeClock].[EmployeeID]=[Employee].[ID] AND [TimeClock].[StopTime]=NULL))
| |--Clustered Index Scan(OBJECT([mi].[dbo].[TimeClock].[PK_TimeClock]))
|--Filter(WHERE(Convert(getdate())=Convert([TimeClock_1].[StartTime]) AND Convert([TimeClock_1].[Deleted])=0))
|--Index Spool(SEEK([TimeClock_1].[EmployeeID]=[Employee].[ID] AND [TimeClock_1].[StopTime]=NULL AND [TimeClock_1].[Type]=5))
|--Clustered Index Scan(OBJECT([mi].[dbo].[TimeClock].[PK_TimeClock] AS [TimeClock_1]))
-
Get rid of your use of functions in the join clause. You could consider doing your rounding, converting and getdate to variables before hand and then use the variable values there.
--------------------------
Zach -
The root cause of the slowness are the sub-selects within the Select. These need to be evaluated for each row in the outer resultset, which usually gives horrible performance.
You need to express these as derived tables and join to them.
Here's an example of the 1st of your CASE WHEN EXISTS converted to a derived table:
SELECT
Employee.FullName, Employee.Extension,
EmployeeDepartments.Description,
CASE WHEN dtClockedInToday.EmployeeID Is Null Then 0 Else 1 END AS ClockedIn
CASE WHEN EXISTS (
SELECT 1 FROM TimeClock TimeClock_1
WHERE (TimeClock_1.EmployeeID = Employee.ID) AND
(CONVERT(varchar(12), GETDATE(), 101) = CONVERT(varchar(12), TimeClock_1.StartTime, 101)) AND --- Clocked in Today
(TimeClock_1.StopTime IS NULL) AND --- Have not clocked out yet
(TimeClock_1.Type = 5) AND -- Type 5 is Lunch
(TimeClock_1.Deleted = 0))
THEN 1 ELSE 0 END AS OnLunch
FROM Employee INNER JOIN
EmployeeDepartments ON Employee.Department = EmployeeDepartments.ID
LEFT JOIN
(
-- Derived table returning all Employees clocked in today
SELECT EmployeeID
FROM TimeClock
WHERE
(CONVERT(varchar(12), GETDATE(), 101) = CONVERT(varchar(12), TimeClock.StartTime, 101)) AND --- Clocked in Today
(TimeClock.StopTime IS NULL) AND --- Have not clocked out yet
(TimeClock.Type <> 5) AND -- Type 5 is Lunch
(TimeClock.Deleted = 0))
) dtClockedInToday
ON dtClockedInToday.EmployeeID = Employee.EmployeeID
WHERE (Employee.Status = 1) --- Active Employees
-
Ahhh, didn't know they could be clocked on several times a day - spose that's why you were using exists...
Then perhaps using a group by on the Fullname, Extension & Description columns, and a sum function on the two case statements would do the trick.
SELECT
Employee.FullName, Employee.Extension,
EmployeeDepartments.Description,
sum(Case when TimeClock.Type <> 5 then 1 else 0 end) as ClockedIn,
sum(Case when TimeClock.Type = 5 then 1 else 0 end) as OnLunch
FROM Employee
INNER JOIN EmployeeDepartments
ON Employee.Department = EmployeeDepartments.ID
INNER JOIN TimeClock
on Employee.ID = TimeClock.EmployeeID
and (TimeClock.startTime between (round(getDate() - 0.5, 0)) and (round(getDate() - 0.5, 0) + 1))
and TimeClock.StopTime is null
and TimeClock.deleted = 0
WHERE (Employee.Status = 1) --- Active Employees
GROUP BY Employee.Fullname, Employee.Extension, EmployeeDepartments.Description
As for the implicit conversion error, that must be ocurring in the round function (worked on my machine with some basic tests?)... Anyho, replace
getDate()
with
cast(getDate() as float)
and your problem should go away...
If the modified query with the group by doesn't work above for you (it may return 2, 3, etc, not just 0, 1 for OnLunch & clockedIn - just treat 0 as 0 and >0 as 1), you could use that entire SQL statement as a derived query and use a case statement to transform the 2, 3, etc sum results into a 1 and keep 0 sum results as a 0.
-
Thanks Ian. That did it - the query runs in less than 1 second.
One small change - I changed the query to do a LEFT OUTER JOIN on TimeClock table. This query would then return all employees even if they are not clocked in.
-
Glad to be of help
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply