Получение последней записи в день / Оптимизация SQL

Question

Имеется следующая таблица базы данных, в которой записываются события (статус) для разных объектов (id) со своей отметкой времени:

ID | Date       | Time | Status
-------------------------------
 7 | 2016-10-10 | 8:23 | Passed
 7 | 2016-10-10 | 8:29 | Failed
 7 | 2016-10-13 | 5:23 | Passed
 8 | 2016-10-09 | 5:43 | Passed

Я хочу получить таблицу результатов, используя простой SQL (MS SQL) следующим образом:

ID | Date       | Status
------------------------
 7 | 2016-10-10 | Failed
 7 | 2016-10-13 | Passed
 8 | 2016-10-09 | Passed

где "статус" - это самая последняя запись за день, учитывая, что было записано хотя бы одно событие для этого объекта.

Мое текущее решение использует "Outer Apply" и "TOP(1)" следующим образом:

SELECT DISTINCT rn.id,
                tmp.date,
                tmp.status

FROM run rn OUTER apply
  (SELECT rn2.date, tmp2.status AS 'status'
   FROM run rn2 OUTER apply
     (SELECT top(1) rn3.id, rn3.date, rn3.time, rn3.status
      FROM run rn3
      WHERE rn3.id = rn.id
        AND rn3.date = rn2.date
      ORDER BY rn3.id ASC, rn3.date + rn3.time DESC) tmp2
   WHERE tmp2.status <> '' ) tmp

Насколько я понимаю, эта команда внешнего применения работает так:

For every id
  For every recorded day for this id
     Select the newest status for this day and this id

Но я сталкиваюсь с проблемами производительности, поэтому считаю, что это решение неадекватно. Любые предложения, как решить эту проблему или как оптимизировать SQL?

Answer 1

Ваш код кажется слишком сложным. Почему бы просто не сделать это?

SELECT r.id, r.date, r2.status
FROM run r OUTER APPLY
     (SELECT TOP 1 r2.*
      FROM run r2
      WHERE r2.id = r.id AND r2.date = r.date AND r2.status <> ''
      ORDER BY r2.time DESC
     ) r2;

Для производительности я бы предложил индекс на run(id, date, status, time),

Answer 2

Использование CTE, вероятно, будет самым быстрым:

with cte as 
(
    select ID, Date, Status, row_number() over (partition by ID, Date order by Time desc) rn
    from run
)
select ID, Date, Status 
from cte
where rn = 1

Answer 3

Не ВЫБИРАЙТЕ из таблицы журнала, вместо этого напишите триггер, который обновляет таблицу latest_run, например:

CREATE TRIGGER tr_run_insert ON run FOR INSERT AS 
BEGIN
    UPDATE latest_run SET Status=INSERTED.Status WHERE ID=INSERTED.ID AND Date=INSERTED.Date
    IF @@ROWCOUNT = 0
        INSERT INTO latest_run (ID,Date,Status) SELECT (ID,Date,Status) FROM INSERTED
END

Затем выполните чтение из намного более короткой таблицы lastest_run. Это добавит снижение производительности при записи, потому что вам понадобится две записи вместо одной. Но даст вам гораздо более стабильное время отклика на чтение. И если вам не нужно выбирать из таблицы "Выполнить", вы можете избежать ее индексации, поэтому снижение производительности двух записей частично компенсируется меньшим количеством поддерживаемых индексов.