SQL-Ex blog

А может быть это даст вам смутное представление о прослушивании параметров в хранимых процедурах.

Скажем, у меня есть хранимая процедура, которая принимает параметр с именем @Reputation.

Тело процедуры выглядит примерно так:

    SELECT TOP (1000) 

            u.*

    FROM dbo.Users AS u

    WHERE u.Reputation = @Reputation

    ORDER BY u.CreationDate DESC;

В таблице users имеется множество людей с репутацией 1.

Но не так много с репутацией 2.

+--------------+------------+
| Reputation | records |
+--------------+------------+
| 1 |1090043 |
| 2 | 1854 |
+--------------+------------+

Два для медленных

Подобные распределения данных относятся к нашему рассмотрению. Они меняют подход SQL Server к построению плана выполнения запроса.

Какие индексы использовать, какой тип соединения использовать, как агрегировать данные, должен ли план быть последовательным или параллельным...

Список можно продолжать.

В данном случае мы имеем узковатый некластеризованный индекс:

    CREATE INDEX ix_whatever 

        ON dbo.Users (Reputation, Age, CreationDate);

Когда я выполняю мою хранимую процедуру для Reputation = 2, план очень быстрый.

EXEC dbo.WORLDSTAR @Reputation = 2;

Это великолепный план для небольшого числа строк.

Когда я выполняю запрос для большого числа строк, он далеко не такой быстрый.

EXEC dbo.WORLDSTAR @Reputation = 1;

Мы пришли от доли секунды к более чем трем секундам.

Это плохое прослушивание параметра.

Если мы сначала выполним процедуру для Reputation = 1, у нас не будет подобной проблемы.

Это хорошее прослушивание параметра.

Наилучшее для каждого

Многие вещи, которые предотвращают прослушивание параметра могут дать вам только так себе план. Это может быть лучше, чем альтернатива, но это определенно не "исправление".

Возможно получить лучший план для каждого в этой ситуации, переписав Key Lookup как самосоединение

    SELECT TOP (1000) 

            u2.*

    FROM dbo.Users AS u

    JOIN dbo.Users AS u2

        ON u.Id = u2.Id

    WHERE u.Reputation = @Reputation

    ORDER BY u.CreationDate DESC;

Причина сложновата, но я постараюсь объяснить это просто.

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

Заметьте, что план Key Lookup по-прежнему выполняется за ~3 секунды, в то время как план самосоединения отрабатывает примерно за полсекунды.



В то время как в Key Lookups возможно ввести Sort (сортировка) для оптимизации ввода/вывода ... Здесь этого не происходит.

Главное отличие между этими двумя планами (помимо времени выполнения) - это место сортировки.

В плане Key Lookup (верхний) поиск закладок между некластеризованным и кластеризованным индексами выполняется при завершении.

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

В плане самосоединения (нижний) все строки идут в Sort, но выходит только 1000.

Другой мир

Разница становится более очевидной при просмотре в Plan Explorer.



В плане Key Lookup строки не ужимаются вплоть до конца, поэтому поиск выполняется примерно 1 миллион раз.

В плане самосоединения они непосредственно устраняются после поиска по индексу, поэтому соединение выполняется только для 1000 строк и производит 1000 поисков.

Сказанное не означает, что сортировки TOP N Sorts - это плохо, оно лишь означает, что они могут не давать наиболее оптимальных планов для Key Lookup'ов.

Когда это не работает

Без TOP шаблон самосоединения не будет радикально быстрее, а только на половину секунды лучше (4,3с против 3,8с) для плохого сценария прослушивания параметра, и значительно меньше для остальных.

Конечно, изменение индекса на включение CreationDate в качестве второго ключевого столбца решит проблему, вообще устраняя необходимость сортировки данных.

    CREATE INDEX ix_whatever --Current Index

        ON dbo.Users (Reputation, Age, CreationDate);

    GO 

        CREATE INDEX ix_apathy --лучший индекс для этого запроса

        ON dbo.Users (Reputation, CreationDate, Age);

    GO

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