Когда речь идет о извлечении данных из таблицы, которая не существует в другой таблице, SQL Server предоставляет нам несколько команд для достижения этой цели. В этой статье мы рассмотрим производительность четырех различных подходов: NOT IN, SQL NOT EXISTS, LEFT JOIN и EXCEPT.
Прежде чем приступить к сравнению производительности, давайте кратко разберем каждую из этих команд:
- NOT IN: Эта команда позволяет указать несколько значений в операторе WHERE и возвращает все значения из первой таблицы, которые не найдены во второй таблице.
- SQL NOT EXISTS: Используется для проверки наличия определенных значений в подзапросе. Она возвращает значения TRUE или FALSE на основе проверки наличия значений в подзапросе.
- LEFT JOIN: Возвращает все записи из первой левой таблицы, совпадающие записи из второй правой таблицы и значения NULL с правой стороны для записей из левой таблицы, которые не имеют совпадений в правой таблице.
- EXCEPT: Возвращает все уникальные записи из первого оператора SELECT, которые не возвращаются из второго оператора SELECT.
Для сравнения производительности этих команд мы создадим две таблицы, Category_A и Category_B, и заполним их по 10 тысяч записей каждая. Затем мы включим статистику TIME и IO, чтобы проанализировать производительность каждого метода.
Давайте взглянем на T-SQL запросы, используемые для каждого метода:
USE SQLShackDemo
SET STATISTICS TIME ON
SET STATISTICS IO ON
-- NOT IN
SELECT Cat_ID FROM Category_A WHERE Cat_ID NOT IN (SELECT Cat_ID FROM Category_B)
-- SQL NOT EXISTS
SELECT A.Cat_ID FROM Category_A A WHERE NOT EXISTS (SELECT B.Cat_ID FROM Category_B B WHERE B.Cat_ID = A.Cat_ID)
-- LEFT JOIN
SELECT A.Cat_ID FROM Category_A A LEFT JOIN Category_B B ON A.Cat_ID = B.Cat_ID WHERE B.Cat_ID IS NULL
-- EXCEPT
SELECT A.Cat_ID FROM Category_A A EXCEPT SELECT B.Cat_ID FROM Category_B B
После выполнения этих запросов мы обнаруживаем, что все четыре метода возвращают одинаковый результат. Однако производительность значительно различается.
Давайте проанализируем статистику IO и TIME, сгенерированную каждым методом:
- Команда NOT IN выполняет 10062 логических чтений на таблице Category_B, занимает 228 мс для завершения и использует 63 мс процессорного времени.
- Команда SQL NOT EXISTS выполняет всего 29 логических чтений на таблице Category_B, занимает 154 мс для завершения и использует 15 мс процессорного времени.
- Команда LEFT JOIN выполняет такое же количество логических чтений, как и метод SQL NOT EXISTS, занимает 151 мс для завершения и использует 16 мс процессорного времени.
- Команда EXCEPT также выполняет 29 логических чтений, занимает 218 мс для завершения и использует 15 мс процессорного времени.
Из статистики IO и TIME ясно, что команды SQL NOT EXISTS и LEFT JOIN лучше всего справляются с общей производительностью.
Давайте ближе рассмотрим планы выполнения, сгенерированные каждым методом:
- Команда NOT IN генерирует сложный план выполнения с тяжелыми операторами, включая оператор Row Count Spool, который выполняет сканирование неотсортированной таблицы Category_B.
- Команда SQL NOT EXISTS генерирует более простой план выполнения с оператором Hash Match, который выполняет операцию Left Anti Semi Join.
- Команда LEFT JOIN заменяет частичное соединение оператором FILTER и оператором Right OUTER JOIN.
- Команда EXCEPT также выполняет операцию Left Anti Semi Join и операцию Aggregate из-за большого размера таблицы.
Исходя из планов выполнения, мы можем заключить, что команды SQL NOT EXISTS и LEFT JOIN продолжают быть лучшими выборами с точки зрения производительности.
Однако мы можем дополнительно улучшить производительность команды EXCEPT, добавив индексы к столбцу объединения, Cat_ID, в обеих таблицах. После добавления индексов команда EXCEPT показывает улучшенную производительность.
В заключение, SQL Server предлагает различные методы для извлечения данных из таблиц. В этой статье мы исследовали производительность команд NOT IN, SQL NOT EXISTS, LEFT JOIN и EXCEPT. Мы обнаружили, что команды SQL NOT EXISTS и LEFT JOIN постоянно превосходят другие методы. Кроме того, добавив индексы к таблицам, мы смогли улучшить производительность команды EXCEPT.
Помните, работая с SQL Server, важно учитывать производительные последствия различных подходов и выбирать метод, который лучше всего соответствует вашим конкретным требованиям.