Эффективный поиск ближайшего числа или даты в SQL, где столбец даты / числа покрывается индексом

Question

Используя SQL2008, я пытаюсь найти эффективный запрос, чтобы найти строку, дата которой ближе всего к определенной целевой дате.

Существуют очевидные неэффективные решения (например, сканирование таблицы с использованием ABS и DATEDIFF), на которые я не потрудился посмотреть, потому что моя таблица уже имеет индекс покрытия, где дата - первый столбец. Я могу сузить результаты, используя этот индекс, прежде чем точно определить, какая строка является ближайшей.

В теории я должен быть в состоянии удовлетворить запрос, используя поиск по одному индексу, а затем последовательное извлечение 2 строк данных из этого индекса.

Но до сих пор я не смог найти более оптимальное решение, чем это:

DECLARE @target DATETIME = '01/02/2011'

SELECT TOP 1 Val, Measured
FROM (
   SELECT TOP 1 Val, Measured 
       FROM tbl 
       WHERE Measured <= @Target 
       ORDER BY Measured desc
   UNION ALL
   SELECT TOP 1 Val, Measured 
       FROM tbl 
       WHERE Measured >= @Target 
       ORDER BY Measured asc
) x
ORDER BY ABS (DATEDIFF (second, Measured, @Target))

Это быстро (4 логических чтения в тестовой схеме ниже, 9 логических чтений в моей реальной таблице), но это все еще решение с 2 сканированиями. Есть ли более эффективное решение, которое попадает в этот индекс только один раз?

Или мое существующее решение "достаточно хорошо", потому что при втором поиске по индексу будут извлекаться кэшированные страницы, к которым обращается первый запрос, а это означает, что оно будет настолько быстрым, что дальнейшая оптимизация (даже если это возможно) приведет к минимальному фактическому улучшению производительности?

Вот схема и некоторые примеры данных. И то, и другое упрощено по сравнению с моей реальной схемой, хотя итоговый план запроса совпадает с моей более сложной таблицей:

CREATE TABLE tbl
(
    ID int IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL,
    Measured DATETIME NOT NULL,
    Val int NOT NULL
);
CREATE NONCLUSTERED INDEX IX_tbl ON tbl (Measured) INCLUDE (Val)
INSERT tbl VALUES ('2011-01-01 12:34',6);
INSERT tbl VALUES ('2011-01-01 23:34',6);
INSERT tbl VALUES ('2011-01-03 09:03',12);
INSERT tbl VALUES ('2011-02-01 09:24',18);
INSERT tbl VALUES ('2011-02-08 07:12',7);
INSERT tbl VALUES ('2011-03-01 12:34',6);
INSERT tbl VALUES ('2011-04-03 09:03',12);
INSERT tbl VALUES ('2011-05-01 09:24',18);
INSERT tbl VALUES ('2011-06-08 07:12',7);
-- insert another few million rows here to compare to my real-world table

Answer 1

Попробуйте сначала определить, где в таблице находится ваш @target, а затем ограничить область поиска +1 / -1 с точностью до дня или недели. Тогда сортировка по дате в пределах этого набора для нахождения ближайшего будет стоить дешевле, чем применение TOP 1/ORDER BY ко всему набору с каждой стороны.