This is a common question but from the questions I've browsed I wasn't able to find a good answer. For every employee I want to display their last known status and the time of that status.

I have two SQL Server tables (well actually they're views):

Employee

Clock

There is some more additional data there but I think I'll be able to figure that out. My problem right now is that I need to find the latest entry for every employee in a performant way.

Current approach:

SELECT
e.EmployeeID,
c.Time AS LastClock,
c.ClockType
FROM Employee e
LEFT JOIN Clock c ON e.EmployeeID = c.EmployeeID
AND c.Time = (
  SELECT max(Time) FROM Clock c1
  WHERE e.EmployeeID = c1.EmployeeID
)

This works, but with many millions of rows, this is really slow. I've also tried simply limiting the inner select with a condition to only return the last X days but that breaks the requirements since I need it for every employee, even if they weren't here in the past month.

What are better ways to do this? The code would be called about every minute, so performance is quite important.

CodePudding user response：

I think a reasonable way of writing it is the following:

WITH c AS (
 SELECT clock.*, row_number() OVER (PARTITION BY employeeid ORDER BY time DESC) rn
 FROM clock
)
SELECT
 e.EmployeeID,
 c.Time LastClock,
 c.Clocktype
FROM Employee e
 LEFT JOIN c ON e.EmployeeID = c.EmployeeID
WHERE COALESCE(c.rn, 1) = 1

However, I think the most important thing for performance of this (or your original query) is going to be to ensure you have a proper index on clock (employeeid, time). e.g. create index ix_clock on clock (employeeid, time)

CodePudding user response：

for better performance use the below query to get the same result to optiomize the performance.

SELECT e.EmployeeID, c.Time AS LastClock, c.ClockType
FROM Employee e
LEFT JOIN (
  SELECT EmployeeID, Time, ClockType
  FROM Clock
  WHERE Time = (SELECT MAX(Time) FROM Clock c1 WHERE c1.EmployeeID = Clock.EmployeeID)
) c ON e.EmployeeID = c.EmployeeID