Оптимизация запросов Sql-сервера

Я хочу написать запрос в хранимой процедуре со многими фильтрами, но я хочу избежать динамического SQL.

Скажем, мои параметры могут принимать значение null (@filter1, @filter2, @filter3...). Один из способов, которым я мог бы решить это:

SELECT col1, col2, col3
FROM table
WHERE col1 = ISNULL(@filter1, col1)
AND col2 = ISNULL(@filter2, col2)
AND col3 = ISNULL(@filter3, col3)

Результат этого будет отфильтрован соответствующими фильтрами, если не равен нулю. Вопрос: 1) Это хорошая практика? 2) Оптимизирует ли оптимизатор col1 = col1 или это повлияет на производительность запроса?


sql sql-server query-optimization
person according2me    schedule 23.06.2010    source источник
comment
Производительность будет отстойной, потому что существует так много возможных комбинаций, что план запроса вряд ли будет кэширован. Динамический SQL — гораздо лучший вариант.   -  person OMG Ponies    schedule 24.06.2010
comment
Эрланд Соммарског составил отличную статью по этому типу проблем. Настоятельно советую ее прочитать.   -  person Tom H    schedule 24.06.2010

Ответы (5)


arrow_upward
1
arrow_downward

Об оптимизации условий: вы должны понимать, что скомпилированный план должен удовлетворять любому значению переменной. Поэтому при создании плана SQL Server должен создать план доступа, который работает, когда @filter1 имеет значение NULL, а также работает, когда @filter1 не равен NULL. Результатом почти всегда является сканирование.

Статьи, на которые ссылается Том Х., подробно рассматривают это.

person Remus Rusanu    schedule 23.06.2010

arrow_upward
0
arrow_downward

ISNULL может повредить использованию индекса, поэтому я бы не сказал, что это идеально, но если вам нужна функциональность, описанная выше, я не уверен, что есть способ обойти это.

Можете ли вы взглянуть на свой план выполнения, чтобы увидеть, используется ли индекс, который вы ожидаете использовать?

person Abe Miessler    schedule 23.06.2010
comment
Эта конструкция часто работает нормально. COALESCE может быть убийцей из-за того, как он обрабатывает типы данных. - person gbn; 24.06.2010
comment
Интересно, какие-нибудь рекомендуемые чтения для ISNULL против COALESCE? - person Abe Miessler; 24.06.2010

arrow_upward
0
arrow_downward

1) Это хорошая практика? 2) Оптимизирует ли оптимизатор col1 = col1 или это повлияет на производительность запроса?

Да, это хорошая практика.

Некоторые СУБД оптимизируют его, некоторые нет. Ничего не будет, если вы вызываете его как подготовленный оператор.

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

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

person tpdi    schedule 23.06.2010
comment
Я большой сторонник того, чтобы не пытаться преждевременно оптимизировать, но в этом случае влияние на производительность часто может быть огромным, и индексы вряд ли помогут, потому что запрос потенциально каждый раз делает что-то другое. Этот тип функциональности обычно общесистемный (т. е. вам нужно выполнять динамический поиск по множеству различных таблиц), поэтому хорошо знать свой общий подход, прежде чем вы напишете 50 SP, которые затем вам придется переписать. - person Tom H; 24.06.2010
comment
Это не преждевременная оптимизация, это разработка для повышения производительности, которую следует выполнять для каждой базы данных. Когда известны различия в производительности между методами, то с самого начала следует выбирать наиболее эффективный. Общеизвестно, что базы данных сложно рефакторить. Слишком поздно, когда производительность становится проблемой. Преждевременная оптимизация не означает отсутствия оптимизации. - person HLGEM; 24.06.2010

arrow_upward
0
arrow_downward

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

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

person Donnie    schedule 23.06.2010

arrow_upward
0
arrow_downward

По моему опыту (запустив некоторые тесты на больших таблицах) следующее:

(col1 = @filter or @filter IS NULL)

намного быстрее, чем:

col1 = ISNULL(@filter1, col1)
person Jani    schedule 23.06.2010