Как мы знаем, статистика не создается на табличных переменных и оптимизатор SQL Server считает, что ожидаемое количество строк при выборке из табличной переменной будет равно единице. На прошедшей конференции SQL Rally 2012 после моего доклада прозвучал очень интересный вопрос: будет ли влиять PRIMARY KEY в табличной переменной на ожидаемое количество строк при выборке. Я решил проверить это на практике, а так как результат, на мой взгляд, получился очень интересным, я решил им поделиться.
Для нашего эксперимента я создам 2 временных таблицы, одну с PRIMARY KEY, а вторую без, и 2 аналогичных табличных переменных. И вставлю в них значения от 1 до 100. И давайте попробуем сделать несколько тестовых выборок из этих таблиц с разными предикатами, а потом проанализируем результаты. Перед запуском скрипта необходимо включить отображение реального плана выполнения запроса (можно сделать несколькими способами: сочетанием клавиш Ctrl+M, либо через меню Query -> Include Actual Execution Plan, либо нажать соответствующую кнопку на панели инструментов).
-- Создаем временную таблицу с числами от 1 до 1000 IF OBJECT_ID('tempdb.dbo.#nums', 'U') IS NOT NULL DROP TABLE #nums; CREATE TABLE #nums ( [n] int PRIMARY KEY ); DECLARE @max AS INT, @rc AS INT; SET @max = 800; SET @rc = 1; INSERT INTO #nums ([n]) VALUES(1); WHILE @rc * 2 <= @max BEGIN INSERT INTO #nums ([n]) SELECT [n] + @rc FROM #nums; SET @rc = @rc * 2; END INSERT INTO #nums ([n]) SELECT [n] + @rc FROM #nums WHERE [n] + @rc <= @max; /* Создаем 2 временные таблицы: одну с PRIMARY KEY, вторую без. И 2 аналогичные табличные переменные. Вставляем в них числа от 1 до 1000. */ IF OBJECT_ID('tempdb.dbo.#temp_table', 'U') IS NOT NULL DROP TABLE #temp_table; IF OBJECT_ID('tempdb.dbo.#temp_table_pk', 'U') IS NOT NULL DROP TABLE #temp_table_pk; CREATE TABLE #temp_table ( [i] int ); CREATE TABLE #temp_table_pk ( [i] int PRIMARY KEY ); DECLARE @table_var TABLE ( [i] int ); DECLARE @table_var_pk TABLE ( [i] int PRIMARY KEY ); INSERT INTO #temp_table ([i]) SELECT [n] FROM #nums; INSERT INTO #temp_table_pk ([i]) SELECT [n] FROM #nums; INSERT INTO @table_var ([i]) SELECT [n] FROM #nums; INSERT INTO @table_var_pk ([i]) SELECT [n] FROM #nums; -- Делаем несколько тестовых запросов SELECT [i] FROM #temp_table WHERE [i] < 145; SELECT [i] FROM #temp_table_pk WHERE [i] < 145; SELECT [i] FROM #temp_table WHERE [i] > 378 AND [i] < 634; SELECT [i] FROM #temp_table_pk WHERE [i] > 378 AND [i] < 634; SELECT [i] FROM @table_var WHERE [i] < 145; SELECT [i] FROM @table_var WHERE [i] < 145 OPTION (RECOMPILE); SELECT [i] FROM @table_var_pk WHERE [i] < 145; SELECT [i] FROM @table_var_pk WHERE [i] < 145 OPTION (RECOMPILE); SELECT [i] FROM @table_var WHERE [i] > 378 AND [i] < 634; SELECT [i] FROM @table_var WHERE [i] > 378 AND [i] < 634 OPTION (RECOMPILE); SELECT [i] FROM @table_var_pk WHERE [i] > 378 AND [i] < 634; SELECT [i] FROM @table_var_pk WHERE [i] > 378 AND [i] < 634 OPTION (RECOMPILE); |
А теперь давайте подробно проанализируем планы наших запросов.
1. Выборка и временной таблицы с фильтром по верхней границе.
Здесь в принципе нет ничего необычного, оптимизатор построил статистику на временной таблице и использовал ее при выборке. При выборке из временной таблицы с PRIMARY KEY ситуация будет аналогичная, поэтому план запроса не привожу.
2. Выборка из временной таблицы, но в предикате указываем нижнюю и верхнюю границы.
Опять нет ничего необычного, оптимизатор построил статистику на временной таблице и использовал ее при выборке. При выборке из временной таблицы с PRIMARY KEY ситуация будет аналогичная, поэтому план запроса не привожу.
3. Выборка из табличной переменной с фильтром по верхней границе.
Привожу сразу планы запросов для обычной табличной переменной и табличной переменной с PRIMARY KEY.
Как мы видим и в том и в другом случае количество ожидаемых строк равно единице. Таким образом, PRIMARY KEY никак не влияет на ожидаемое количество строк при выборке из табличной переменной.
4. Выборка из табличной переменной с опцией RECOMPILE. Привожу только план выборки из обычной табличной переменной, т.к. для табличной переменной с PRIMARY KEY будет аналогично.
Результаты оказались несколько неожиданными: оптимизатор ожидает на выходе 300 строк вместо одной. 300 строк – это 30%, обычная константа, которая зашита в оптимизатор.
5. Выборка из временной таблицы, но в предикате указываем нижнюю и верхнюю границы. Сначала приведу обычный план запроса, потом план запроса с опцией RECOMPILE.
В первом плане запросов ожидаемое количество строк равно единице. А вот для запроса с опцией RECOMPILE количество ожидаемых строк равно 90 или 9% от общего числа. Я поэкспериментировал с различным количеством строк в таблице и разными границами предиката, но результата один – 9%. Для запросов к табличным переменным с PRIMARY KEY результаты будут такие же, поэтому планы запросов не привожу.
Вывод.
Пришла пора подвести итог небольшого практического исследования и ответить на главный вопрос статьи: наличие PRIMARY KEY на табличной переменной никак не влияет на ожидаемое количество строк при выборке.
Но в процессе исследования случайно столкнулся с тем, что если использовать опцию RECOMPILE, то количество ожидаемых строк будет 30% от общего числа строк, если использовать в предикате ограничение только по одной границе интервала. А если использовать ограничение с двух сторон, то количество ожидаемых строк будет 9% от общего числа строк в табличной переменной.