SQL-Ex blog

Предположим, что у вас есть реально крутой запрос типа такого:

WITH records AS

(

    SELECT

        ROW_NUMBER() OVER (

            PARTITION BY i.CustomerID

            ORDER BY i.OrderID) AS rownum

    FROM Sales.Invoices i

)

SELECT *

FROM records r

WHERE

    r.rownum = 0;

GO

Вы можете спросить себя, выполняется ли фильтрация по значению rownum = 0, если минимальное значение rownum должно быть 1? Вот почему! На самом деле для того, чтобы мы не учитывали время на выписывание строк. Так или иначе, вот план запроса:


Простой план

Этот запрос выполняется за 58 миллисекунд, включая 304 миллисекунды времени процессора, но при этом было выполнено 103384 операций ввода/вывода.

Давайте добавим индекс, начиная с ID заказчика и включая ID заказа в соответствии с нашими столбцами в partition и order.

IF NOT EXISTS

(

    SELECT 1

    FROM sys.indexes i

    WHERE

        i.name = N'IX_Sales_Invoices_WindowFunction'

)

BEGIN

    CREATE INDEX [IX_Sales_Invoices_WindowFunction] ON Sales.Invoices

    (

        CustomerID,

        OrderID

    );

END

GO

Теперь снова проверим план запроса.

Выглядит неплохо

Мы по-прежнему имеем сканирование индекса, но план выглядит проще, чем раньше. Нам не требуется выполнять сортировку. Это не вполне следует из самого плана, но преимущества довольно большие: понижение длительности до 35 мс для однопоточности (поэтому 35 мс времени процессора) и только 1497 чтений.

Но будьте осторожны: порядок имеет значение. Для этого запроса мы бы ожидали в точности такой же вывод. Кроме того, индексы имеют двойное связывание, поэтому я могу читать его в любом направлении.

WITH records AS

(

    SELECT

        ROW_NUMBER() OVER (

            PARTITION BY i.CustomerID

            ORDER BY i.OrderID DESC) AS rownum

    FROM Sales.Invoices i

)

SELECT *

FROM records r

WHERE

    r.rownum = 0;

GO

Одна печальная сортировка в конце

Оказывается, что когда вы создаете индекс для поддержки оконной функции, вам действительно нужно придерживаться порядка.

WPOC: почему правило большого пальца не работает

Это большое исключение из правила большого пальца POC: если вы имеете сильный фильтр, поместите ваши фильтры в начале. В примере выше мы сканировали весь набор счетов-фактур. Предположим, однако, что нам нужны только конкретные счета-фактуры.

WITH records AS

(

    SELECT

        ROW_NUMBER() OVER (

            PARTITION BY i.CustomerID

            ORDER BY i.OrderID) AS rownum

    FROM Sales.Invoices i

    WHERE

        i.TotalDryItems > 3

)

SELECT *

FROM records r

WHERE

    r.rownum = 0;

GO

Ну, это шаг назад

Мы вернулись к 103384 чтениям и сканированию кластеризованного индекса. В идеале мы сначала фильтруем по предложению WHERE и обрезаем число необходимых строк. Если подобный запрос выполняется множество раз, нам следует модифицировать наш индекс. Вот наша первая попытка:

CREATE INDEX [IX_Sales_Invoices_WindowFunction] ON Sales.Invoices

(

    TotalDryItems,

    CustomerID,

    OrderID

) WITH(DROP_EXISTING = ON);

Это уже лучше

План запроса не обязательно выглядит хорошо, но имеются улучшения: продолжительность 8мс, 35мс времени процессора и 472 чтения. Если предполагается, что запрос должен включать как минимум 3 сухих элемента, у нас может быть даже вариант использования фильтрованного индекса!

CREATE INDEX [IX_Sales_Invoices_WindowFunction] ON Sales.Invoices

(

    CustomerID,

    OrderID

)

WHERE TotalDryItems > 3

WITH(DROP_EXISTING = ON);

Теперь поговорим

Продолжительность 41мс для непараллельного выполнения запроса и всего 369 чтений.

Что насчет нескольких окон?

ОК, мистер Умный Парень, что случится, когда мы имеем несколько окон? Ну... вам придется с этим жить. Вы получите один индекс для извлечения данных (здесь даже более важно для индексирования сделать лидирующим предложение WHERE), а затем вы получите множество сортировок и операций с окнами. Если вам нужны все эти окна в единственном запросе, тут мало что вы могли бы сделать. Если производительность действительно плоха, сбросьте результаты в временную таблицу и попробуйте использовать более простые запросы.

Заключение

Если вам необходимо сканировать всю таблицу, то создавайте индексы на основе шаблона POC. Но как только вам нужно сузить ваши запросы, выбор за WPOC.