SQL-Ex blog

Зачем переписывать запросы

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

• Базами данных поставщиков.


• "Хрупкими" системами.


• Недостаточным местом на диске.


• Ограниченным инструментарием/непосредственным анализом.


• Возможностями, ограниченными системой безопасности.

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

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

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

1. Оконные функции против GROUP BY

Иногда оконные функции несколько злоупотребляют использованием tempdb и блокирующими операторами, чтобы выполнить свою работу. Я всегда предпочитаю их из-за простого синтаксиса. Но если страдает производительность, вы обычно можете переписать их в старомодной манере с GROUP BY, чтобы улучшить производительность.

2. Коррелирующие подзапросы против производных таблиц

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

3. IN против UNION ALL

При фильтрации строк данных по множеству значений в таблицах с перекошенными распределениями и непокрывающими индексами запись вашей логики через множество операторов, объединяемых с помощью UNION ALL, иногда производит более эффективный план выполнения, чем простое использование IN или OR.

4. Временные промежуточные таблицы

Иногда оптимизатор запросов мучается в попытках построить эффективный план выполнения сложных запросов. Разбиение сложного запроса на множество шагов, использующих временные промежуточные таблицы, может предоставить SQL Server больше информации о ваших данных. Это также вынуждают вас писать более простые запросы, которые позволяют оптимизатору строить более эффективные планы выполнения, а также повторно использовать результирующие наборы.

5. Форсирование порядка соединения таблиц

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

6. DISTINCT с небольшим числом уникальных значений

Использование оператора DISTINCT не всегда является самым быстрым способом вернуть уникальные значения в наборе данных. В частности, Paul White использует рекурсивные CTE, чтобы вернуть отличные значения на больших наборах данных при относительно небольшом числе уникальных значений. Это отличный пример решения проблемы с помощью очень креативного решения.

7. Устранение UDF

UDF зачастую провоцирует плохую производительность запросов, благодаря навязыванию последовательных планов и приводя к неточным оценкам. Одним из способов возможного улучшения производительности запросов, которые вызывают UDF, попытаться встроить логику UDF непосредственно в основной запрос. С SQL Server 2019 это будет иногда делаться автоматически во многих случаях, однако Брент Озар показал, что вы можете иногда вручную встроить функциональность UDF, чтобы получить наилучшую производительность.

8. Создание UDF

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

9. Сжатие данных

Сжатие данных не только экономит место, но при определенной рабочей нагрузке может фактически улучшить производительность. Поскольку сжатые данные могут быть записаны на меньшем числе страниц, увеличивается скорость чтения с диска, но, что может быть более важно, сжатые данные позволяют сохранить их больший объем в буферном пуле SQL Server, увеличивая вероятность нахождения в памяти повторно используемых данных.

10. Индексные представления

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

11. Переключение оценщиков кардинального числа

Недавно появившийся в SQL Server 2014 оценщик кардинального числа улучшает производительность многих запросов. Однако в некоторых конкретных случаях это может сделать запросы более медленными. В таких случаях простой хинт запроса - это все, что вам нужно, чтобы заставить SQL server вернуться к прежнему оценщику кардинального числа.

12. Копирование данных

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

...И еще

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

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