Оптимизация SQL — план выполнения изменяется в зависимости от значения ограничения — почему?

У меня есть таблица ItemValue, полная данных на сервере SQL 2005, работающем в режиме совместимости с 2000, которая выглядит примерно так (это таблица пользовательских значений):

ID    ItemCode     FieldID   Value
--    ----------   -------   ------
 1    abc123             1   D
 2    abc123             2   287.23
 4    xyz789             1   A
 5    xyz789             2   3782.23
 6    xyz789             3   23
 7    mno456             1   W
 9    mno456             3   45
                                 ... and so on.

FieldID берется из таблицы ItemField:

ID   FieldNumber   DataFormatID   Description   ...
--   -----------   ------------   -----------
 1             1              1   Weight class
 2             2              4   Cost
 3             3              3   Another made up description
 .             .              x   xxx
 .             .              x   xxx
 .             .              x   xxx
 x             91  (we have 91 user-defined fields)

Поскольку я не могу PIVOT в режиме 2000, мы застряли в построении уродливого запроса с использованием CASE и GROUP BY, чтобы данные выглядели так, как они должны выглядеть для некоторых устаревших приложений, а именно:

ItemNumber   Field1   Field2    Field3 .... Field51
----------   ------   -------   ------
    abc123   D        287.23    NULL
    xyz789   A        3782.23   23
    mno456   W        NULL      45

Как видите, нам нужна эта таблица только для отображения значений до 51-го UDF. Вот запрос:

SELECT
    iv.ItemNumber,
    ,MAX(CASE WHEN f.FieldNumber = 1 THEN iv.[Value] ELSE NULL END) [Field1]
    ,MAX(CASE WHEN f.FieldNumber = 2 THEN iv.[Value] ELSE NULL END) [Field2]
    ,MAX(CASE WHEN f.FieldNumber = 3 THEN iv.[Value] ELSE NULL END) [Field3]
        ...
    ,MAX(CASE WHEN f.FieldNumber = 51 THEN iv.[Value] ELSE NULL END) [Field51]
FROM ItemField f
LEFT JOIN ItemValue iv ON f.ID = iv.FieldID
WHERE f.FieldNumber <= 51
GROUP BY iv.ItemNumber

Когда ограничение FieldNumber равно ‹= 51, план выполнения выглядит примерно так:

SELECT <== Computer Scalar <== Stream Aggregate <== Sort (Cost: 70%) <== Hash Match <== (Clustered Index Seek && Table Scan)

и это быстро! Я могу извлечь более 100 000 записей примерно за секунду, что соответствует нашим потребностям.

Однако, если у нас было больше пользовательских функций и я изменил ограничение на что-то выше 66 (да, я тестировал их по одному) или если я полностью удалю его, я потеряю Сортировку в плане выполнения и он заменяется целой кучей блоков Parallelism, которые собирают, перераспределяют и распределяют потоки, и все это работает медленно (30 секунд даже для одной записи).

FieldNumber имеет кластеризованный уникальный индекс и является частью составного первичного ключа со столбцом ID (некластеризованный индекс) в таблице ItemField. . Столбцы ID и ItemNumber таблицы ItemValue составляют PK, а для ItemNumber колонка.

Какова причина этого? Почему изменение моего простого целочисленного ограничения меняет весь план выполнения?

И если вы согласны... Что бы вы сделали по-другому? Через пару месяцев запланировано обновление SQL, но мне нужно решить эту проблему до этого.


sql sql-server query-optimization sql-execution-plan
person Cᴏʀʏ    schedule 05.03.2010    source источник
comment
Что бы я сделал по-другому, так это не использовать эту структуру таблицы. Гораздо лучше использовать структуру с определенными полями для 99% потребностей в данных, которые можно вычислить заранее, чем использовать таблицу EAV. Между прочим, режим совместимости не запрещает вам использовать новые функции, он позволяет использовать функции, которые больше не разрешены. Однако, если вы разрабатываете базу данных 2005 года с рабочей базой данных 2000 года, лучше избегать новых функций.   -  person HLGEM    schedule 08.06.2011

Ответы (3)


arrow_upward
4
arrow_downward

SQL Server достаточно умен, чтобы учитывать CHECK ограничения при оптимизации запросов.

Ваш f.FieldNumber <= 51 оптимизирован, и оптимизатор видит, что все две таблицы должны быть объединены (что лучше всего сделать с HASH JOIN).

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

Не могли бы вы опубликовать все планы для запросов? Просто запустите SET SHOWPLAN_TEXT ON, а затем запросы.

Обновление:

Что стоит за этим? Почему изменение моего простого целочисленного ограничения меняет весь план выполнения?

Если под ограничением вы подразумеваете условие WHERE, это, вероятно, другое дело.

Операции над множествами (это то, что делает SQL) не имеют единого наиболее эффективного алгоритма: эффективность каждого алгоритма сильно зависит от распределения данных в множествах.

Скажем, для получения подмножества (это то, что делает предложение WHERE) вы можете либо найти диапазон записей в индексе и использовать указатели записей индекса для поиска строк данных в таблице, либо просто просмотреть все записи в таблице и отфильтровать их с помощью условия WHERE.

Эффективность первой операции равна m × const, второй — n, где m — количество записей, удовлетворяющих условию, n — общее количество записей в таблице и const > 1.

Это означает, что при больших значениях m полное сканирование более эффективно.

SQL Server знает об этом и меняет планы выполнения в соответствии с константами, влияющими на распределение данных в заданных операциях.

ДЛЯ этого SQL Server ведет статистику: агрегированные гистограммы распределения данных в каждом проиндексированном столбце и использует их для построения планов запросов.

Таким образом, изменение целого числа в условии WHERE фактически влияет на размер и распределение данных базовых наборов и заставляет SQL Server пересмотреть алгоритмы, которые лучше всего подходят для работы с наборами такого размера и макета.

person Quassnoi    schedule 05.03.2010
comment
Я опубликую планы, но мне нужно внести пару изменений, чтобы мои настоящие таблицы не давали никакой идентифицирующей информации (мой пример подделан). - person Cᴏʀʏ; 05.03.2010
comment
@Remus: Когда я прочитал это, я полагал, что когда ограничение FieldNumber равно <= 51, было связано с ограничением CHECK, но вы, вероятно, правы, @op во многом означало условие WHERE. - person Quassnoi; 06.03.2010
comment
Я принимаю этот ответ для вашего описания и для ответа на мой вопрос, хотя я приму во внимание некоторые другие ответы, поскольку они также дают некоторое представление. Спасибо! - person Cᴏʀʏ; 06.03.2010

arrow_upward
0
arrow_downward

он заменяется целой кучей блоков Parallelism

Попробуй это:

SELECT
    iv.ItemNumber,
    ,MAX(CASE WHEN f.FieldNumber = 1 THEN iv.[Value] ELSE NULL END) [Field1]
    ,MAX(CASE WHEN f.FieldNumber = 2 THEN iv.[Value] ELSE NULL END) [Field2]
    ,MAX(CASE WHEN f.FieldNumber = 3 THEN iv.[Value] ELSE NULL END) [Field3]
        ...
    ,MAX(CASE WHEN f.FieldNumber = 51 THEN iv.[Value] ELSE NULL END) [Field51]
FROM ItemField f
LEFT JOIN ItemValue iv ON f.ID = iv.FieldID
WHERE f.FieldNumber <= 51
GROUP BY iv.ItemNumber
OPTION (Maxdop 1)

Использование Option(Maxdop 1) должно предотвратить параллелизм в плане выполнения.

person George Mastros    schedule 05.03.2010
comment
Так что это на самом деле работает, но вы не можете поместить OPTION в представление, которое мне нужно. - person Cᴏʀʏ; 05.03.2010

arrow_upward
0
arrow_downward

В 66 лет вы достигаете некоторого порога внутренней оценки затрат, который решает, что лучше использовать один план, а не другой. Что это за порог и почему он возникает, на самом деле не важно. Обратите внимание, что ваш запрос отличается для каждого значения FieldNumber, поскольку вы не только меняете WHERE: вы также изменяете проецируемые поля псевдо-'pivot'.

Теперь я не знаю всех деталей вашей таблицы и ваших запросов и вставки/обновления/удаления/шаблона, но для конкретного запроса, который вы опубликовали, правильная структура кластеризованного индекса для таблицы ItemValue такова:

CREATE CLUSTERED INDEX  [cdxItemValue] ON ItemValue (FieldID, ItemNumber);

Эта структура устраняет необходимость в промежуточной сортировке результатов для этого «основного» запроса.

person Remus Rusanu    schedule 05.03.2010