SQL Server: поиск или сканирование. N верхних позиций для группы
Содержание:
1. Сканирование против поиска;
2. Полусоединения и антиполусоединения;
3.N верхних позиций для группы (Вы читаете данный раздел).
Задача вывода N верхних позиций для каждой группы (например, последний заказ для каждого сотрудника компании или клиента) — еще один пример, когда типовые решения дают статическую конфигурацию плана; во всяком случае, это так в контексте противопоставления сканирования циклу операций поиска. Приведу два типовых решения:
1. Вычислить номера строк и отфильтровать строки с номером 1. Такое решение всегда предполагает сканирование.
2. Выполнить запрос к таблице, разбитой на группы или секции, и с помощью оператора CROSS APPLY применить запрос ТОР (1) к таблице большего размера. Это решение предполагает цикл операций поиска.
Два решения для выбора верхних позиций для каждой группы
Итак, чтобы вывести последний заказ для каждого клиента (низкая плотность), следует использовать решение с функцией ROW_ NUMBER. Для вывода последнего заказа для каждого сотрудника компании (высокая плотность) следует использовать решение с оператором CROSS APPLY. В коде, представленном выше, приведены оба примера. Планы запросов показаны на скриншоте ниже.
N верхних позиций для группы
Любопытную задачу, относящуюся к последнему примеру, предложил Эрланд Соммарског, MVP по SQL Server. Вокруг этой задачи развернулась интересная дискуссия с участием Хьюго Корнелиса, Пола Уайта и других специалистов. Предположим, в последнем запросе требуется пополнить список SELECT столбцами из таблицы Orders, не покрываемыми индексом. Например, приведенный ниже запрос добавляет столбец filler в список SELECT:
Это небольшое изменение заметно отражается на скорости выполнения запроса. В моей системе без этого дополнительного столбца запрос выполняется за 77 миллисекунд и производится всего несколько сотен операций чтения. С дополнительным столбцом число операций чтения вырастает до 2,5 млн, а время выполнения запроса — до 8 секунд. Казалось бы, оптимальным планом нового запроса стала бы следующая схема действий для каждого сотрудника компании:
1. Поиск в некластеризованном индексе idx eid oid i od cid для сбора ключа кластеризации.
2. Применение уточняющего запроса ключа (поиск в кластеризованном индексе) для сбора столбца filler из базовой строки данных.
N верхних позиций для группы, непокрывающий индекс, неоптимальный вариант
Однако, судя по плану запроса на скриншоте выше, оптимизатор выбрал другую стратегию. Вместо двойного поиска (поиск в некластеризованном индексе и уточняющий поиск в кластеризованном индексе) для каждого сотрудника компании этот план предусматривает (для каждого работника) упорядоченное сканирование кластеризованного индекса, которое выполняется до момента, когда строка найдена. Вспомним, что ключ кластеризованного индекса — это столбец orderid. Таким образом, как только первая строка с внешним значением empid найдена, сканирование останавливается, и запрос возвращает элементы заказа из этой строки. Такая стратегия может показаться странной, однако, хорошо это или плохо (в нашем случае — плохо), учитывая существующую модель оптимизации и оценки количества элементов, в таком образе действий есть своя логика.
1. Сканирование против поиска;
2. Полусоединения и антиполусоединения;
3.
Задача вывода N верхних позиций для каждой группы (например, последний заказ для каждого сотрудника компании или клиента) — еще один пример, когда типовые решения дают статическую конфигурацию плана; во всяком случае, это так в контексте противопоставления сканирования циклу операций поиска. Приведу два типовых решения:
1. Вычислить номера строк и отфильтровать строки с номером 1. Такое решение всегда предполагает сканирование.
2. Выполнить запрос к таблице, разбитой на группы или секции, и с помощью оператора CROSS APPLY применить запрос ТОР (1) к таблице большего размера. Это решение предполагает цикл операций поиска.
Два решения для выбора верхних позиций для каждой группы
Итак, чтобы вывести последний заказ для каждого клиента (низкая плотность), следует использовать решение с функцией ROW_ NUMBER. Для вывода последнего заказа для каждого сотрудника компании (высокая плотность) следует использовать решение с оператором CROSS APPLY. В коде, представленном выше, приведены оба примера. Планы запросов показаны на скриншоте ниже.
N верхних позиций для группы
Прокси CROSS APPLY
Любопытную задачу, относящуюся к последнему примеру, предложил Эрланд Соммарског, MVP по SQL Server. Вокруг этой задачи развернулась интересная дискуссия с участием Хьюго Корнелиса, Пола Уайта и других специалистов. Предположим, в последнем запросе требуется пополнить список SELECT столбцами из таблицы Orders, не покрываемыми индексом. Например, приведенный ниже запрос добавляет столбец filler в список SELECT:
- Логических чтений 2445833
SELECT E.empid, O.orderdate,
O.orderid, O.custid, O.filler
FROM dbo.Employees AS E
CROSS APPLY (SELECT TOP (1)*
FROM dbo.Orders AS O
WHERE O.empid = E.empid
ORDER BY orderid DESC) AS O; Это небольшое изменение заметно отражается на скорости выполнения запроса. В моей системе без этого дополнительного столбца запрос выполняется за 77 миллисекунд и производится всего несколько сотен операций чтения. С дополнительным столбцом число операций чтения вырастает до 2,5 млн, а время выполнения запроса — до 8 секунд. Казалось бы, оптимальным планом нового запроса стала бы следующая схема действий для каждого сотрудника компании:
1. Поиск в некластеризованном индексе idx eid oid i od cid для сбора ключа кластеризации.
2. Применение уточняющего запроса ключа (поиск в кластеризованном индексе) для сбора столбца filler из базовой строки данных.
N верхних позиций для группы, непокрывающий индекс, неоптимальный вариант
Однако, судя по плану запроса на скриншоте выше, оптимизатор выбрал другую стратегию. Вместо двойного поиска (поиск в некластеризованном индексе и уточняющий поиск в кластеризованном индексе) для каждого сотрудника компании этот план предусматривает (для каждого работника) упорядоченное сканирование кластеризованного индекса, которое выполняется до момента, когда строка найдена. Вспомним, что ключ кластеризованного индекса — это столбец orderid. Таким образом, как только первая строка с внешним значением empid найдена, сканирование останавливается, и запрос возвращает элементы заказа из этой строки. Такая стратегия может показаться странной, однако, хорошо это или плохо (в нашем случае — плохо), учитывая существующую модель оптимизации и оценки количества элементов, в таком образе действий есть своя логика.
