Как ускорить выполнение запроса sql

Введение

В данном руководстве мы изложили некоторые рекомендации по оптимизации запросов SQL.

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

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

Общая оптимизация

Каждая SQL-операция имеет так называемый «коэффициент полезности» – уровень эффективности данной операции. Чем больше балл, тем «полезней» операция, а значит, SQL-запрос выполняется быстрее.

Практически любое условие состоит из двух операндов и знака операции между ними.

Чтобы лучше понять таблицы, рассмотрим пример расчета рейтинга запроса.

5 баллов за поле слева (smallint_column), 2 балла за точный цифровой операнд(smallint_column), 10 баллов за операцию сравнения (=) и 10 баллов за значение справа (12345). Итого получили 27 баллов. Теперь рассмотрим более сложный пример:

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

И эти приемы оптимизации работают практически всегда и везде.

Данная процедура позволяет найти различные проблемные запросы в базе данных.

Для работы запросы нужны права на Server viewstate. По умолчанию в базе Falcon Space она уже есть и вызывается через exec as_perf

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

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

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

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

Оптимизация структуры таблиц SQL Server

Разбивайте сложные таблицы на несколько, помните, чем больше в вашей таблице столбцов и тяжелых типов (nvarchar(max)), тем тяжелее по ней проход. Если некоторые данные не всегда используются в select с ней, выносите большие столбцы в отдельные таблицы и связывайте через FK

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

Если текстовые данные в столбце имеют разную длину, используйте тип  данных NVARCHAR вместо NCHAR.

Не используйте NVARCHAR или NCHAR типы данных, если Вы не должны сохранить 16-разрядные символьные данные (UNICODE). Они требуют в два раза больше места, чем CHAR и VARCHAR, что повышает расходы времени на ввод-вывод (но если у вас кириллица, то без NVARCHAR не обойтись).

Если Вы должны хранить большие строки данных и их длина меньше чем 8,000 символов, используют тип данных NVARCHAR вместо TEXT. Текстовые поля требуют больше ресурсов для обработки и снижают производительность.

Любое поле, в котором должны быть только отличные от нуля значения, нужно объявлять как NOT NULL

Для любого поля, которое должно содержать уникальные значения, стоит указать модификатор UNIQUE

Хранение изображений в БД нежелательно. Храните в таблице путь к файлу (локальный путь или URL), а сам файл помещайте в файловую систему сервера.

Группирование

Операция GROUP BY используется для определения подмножества в результате запроса, а также для применения к этому подмножеству агрегатных функций. Рассмотрим несколько наиболее эффективных методов оптимизации операции группирования.

Первое, что следует помнить, — нужно использовать как можно меньше колонок для группировки. Также следует избегать лишних условий. Например, в запросе SELECT secondary_key_column, primary_key_column, COUNT(*) FROM Table1 GROUP BY secondary_key_column, primary_key_column колонка secondary_key_column совершенно не нужна. Причина простая: secondary_key_column является уникальным полем, оно может не иметь значений NULL, а значит, некоторые данные могут просто потеряться. Но если убрать secondary_key_column из секции GROUP BY, некоторые БД могут выдать ошибку о том, что невозможно указывать это поле, если оно не объявлено в секции GROUP BY. Для решения этой проблемы можно написать запрос в таком виде: SELECT MIN(secondary_key_column), primary_key_column, COUNT(*) FROM Table1 GROUP BY primary_key_column . Этот запрос быстрее и «правильнее» с точки зрения конструирования запросов.

В большинстве БД операции WHERE и HAVING не равноценны и выполняются не одинаково. Это значит, что следующие два запроса логически одинаковы, но выполняются с разной скоростью:

Если требуется группирование, но без использования агрегатных функций ( COUNT(), MIN(), MAX и т.д.), разумно использовать DISTINCT. Так, вместо SELECT column1 FROM Table1 GROUP BY column1 лучше использовать SELECT DISTINCT column1 FROM Table1 .

При использовании MIN() и MAX() учитываем, что эти функции лучше работают по отдельности. Это значит, что их лучше использовать в раздельных запросах или в запросах с использованием UNION.

При использовании функции SUM() большей производительности можно добиться используя SUM(x + y) , а не SUM(x) + SUM(y) . Для вычитания лучше противоположное: SUM(x) – SUM(y) быстрее, чем SUM(x – y).

IN против UNION ALL

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

Подзапросы (SUBQUERIES)

Раньше далеко не все БД могли похвастаться поддержкой подзапросов, а сейчас практически любая современная БД это умеет. Даже MySQL, которая несколько лет воплощала подзапросы в жизнь, наконец разжилась их поддержкой. Основная проблема при оптимизации подзапросов — не оптимизация непосредственно самого кода запроса, а выбор правильного способа для реализации запроса. Задачи, для которых используются подзапросы, также могут решаться с помощью вложенных циклов или JOIN’ов. Когда используешь JOIN, даешь возможность БД выбрать механизм, которым будет производиться соединение таблиц. Если же используешь подзапросы, то явно указываешь на использование вложенных циклов.

Ниже аргументы в пользу того или иного способа. Выбирай сам в зависимости от ситуации.

Основное преимущество JOIN’ов в том, что не надо указывать БД то, каким именно способом производить операцию. А основное преимущество подзапросов в том, что цикл подзапроса может иметь несколько итераций (повторений), что, в свою очередь, может существенно увеличить производительность.

Соединения таблиц (JOINS)

Вот где сложно что-то сказать про оптимизацию, так это при использовании JOIN . Дело в том, что скорость выполнения таких операций во многом зависит от организации самой таблицы: использование foreign-key, primary-key, количество вложенных соединений и т.д. Иногда лучшей производительности можно добиться используя вложенные циклы непосредственно в программе. Иногда быстрее работают JOINs. Однозначного совета по тому, как использовать разные способы соединения таблиц, не существует. Все зависит от конкретного случая и архитектуры БД.

Создание UDF

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

Еще несколько советов по оптимизации SQL запросов (Февраль 2023)

Проблема с лишними индексами состоит в том, что SQL сервер должен изменять их при любых изменениях таблицы (INSERT, UPDATE, DELETE).

Лучшим решением ставить сомнительный индекс или нет, будет подождать и собрать статистику по работе индексов.

Лучшие кандидаты на установку индекса

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

Устранение UDF

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

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

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

Оптимизация запросов SELECT

Если ваше приложение позволяет пользователям выполнять запросы, но вы не можете отсечь лишние тысячи возвращаемых строк, используйте оператор TOP внутри инструкции SELECT.

Не возвращайте клиенту большее количество столбцов или строк, чем действительно необходимо (Не используй * в Select).

Как можно раньше отфильтруйте данные. Не нужно выполнять большой тяжелый подзапрос для  всех строк таблицы. Сначала отфильтруйте нужные строки.

Корректно используйте JOIN

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

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

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

Если Вы должны регулярно объединять четыре или более таблиц, для получения recordset’а, попробуйте денормализовать таблицы так, чтобы число таблиц, участвующих в объединении уменьшилось. Часто, при добавлении одного или двух столбцов из одной таблицы в другую, объединения могут быть уменьшены.

Если вам нужно постоянно получать некоторые данные на лету (например, расчет бонусов клиента), попробуйте это поле хранить в отдельной колонке и обновлять по необходимости. В этом случае не нужно будет делать лишние join и подзапросы.

Тип JOIN используйте только тот, который вернет вам НЕОБХОДИМЫЕ данные без каких-либо дублей или лишней информации (или совсем отказаться от join). Т.е. не нужно получать всех пользователей таким образом:

В этом случае вы получите много повторов пользователей

Сортировка в SELECT

Самой ресурсоемкой сортировкой является сортировка строк.

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

Если сортируете по дате создания, то попробуйте сортировать просто по id (первичный ключ с identity(1,1)).

Группирование в SELECT

Используйте как можно меньше колонок для группировки.

По возможности лучше использовать Where вместо Having, т.к. это уменьшает количество строк для группировки на ранней стадии.

Если требуется группирование, но без использования агрегатных функций (COUNT(), MIN(), MAX и т.д.), разумно использовать DISTINCT.

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

Ограничить использование DISTINCT

Эта команда исключает повторяющиеся строки в результате. Команда требует повышенного времени обработки. Лучше всего комбинировать с LIMIT.

Ограничить использование SELECT для постоянно изменяющихся таблиц.

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

Оптимизация WHERE в запросе SELECT

Если where состоит из условий, объединенных AND,  они должны располагаться в порядке возрастания вероятности истинности данного условия. Чем быстрее мы получим false  в одном из условий — тем меньше условий будет обработано и тем быстрее выполняется запрос.

Если where состоит из условий, объединенных OR,  они должны располагаться в порядке уменьшения вероятности истинности данного условия. Чем быстрее мы получим true  в одном из условий — тем меньше условий будет обработано и тем быстрее выполняется запрос.

LIKE. Эту операцию следует использовать только при крайней необходимости, потому что лучше и быстрее использовать поиск, основанный на full-text индексах.

ORDER BY используется для сортировки, которая, как известно, занимает время. Чем больше объем данных, тем больше времени займет сортировка, поэтому нужно обязательно ее оптимизировать. На скорость сортировки в запросах влияет три фактора:

Самой ресурсоемкой сортировкой является сортировка строк. Несмотря на то, что текстовые поля имеют фиксированную длину, длина содержимого этих полей может быть различной (в пределах размера поля). Поэтому неудивительно, что сортировка колонки VARCHAR(100) будет медленней, чем сортировка колонки (даже если данные будут одинаковые). А происходит это из-за того, что при сортировке сама база данных выделяет память для своих операций в соответствии с максимальным размером поля независимо от содержимого. Поэтому при объявлении полей всегда следует использовать размер, который нужен, и не выделять лишние байты про запас.

На компьютерах с ОС Windows поля типа INTEGER занимают 32 бита, а поля типа SMALLINT – 16 бит. Логично предположить, что сортировка полей типа SMALLINT должна происходить быстрее. На самом деле сортировка INTEGER происходит быстрее, чем SMALLINT. Также сортировка INTEGER происходит быстрее, чем CHAR.

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

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

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

Советы по оптимизации хранимых процедур и SQL пакетов

Для обработки данных используйте хранимые SQL процедуры.

Когда хранимая процедура выполняется в первый раз (и у нее не определена опция WITH RECOMPILE), она оптимизируется, для нее создается план выполнения запроса, который кешируется SQL сервером. Если та же самая хранимая процедура вызывается снова, она будет использовать кешированный план выполнения запроса, что экономит время и увеличивает производительность.

Всегда включайте в ваши хранимые процедуры инструкцию «SET NOCOUNT ON». Если Вы не включите эту инструкцию, тогда каждый раз при выполнении запроса SQL сервер отправит ответ клиенту, указывающему число строк, на которые воздействует запрос.

Избегайте использования курсоров

По возможности выбирайте быстрый forward-only курсор

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

Когда Вы закончили использовать курсор, как можно раньше не только ЗАКРОЙТЕ (CLOSE) его, но и ОСВОБОДИТЕ (DEALLOCATE).

Используйте триггеры c осторожностью

Триггеры — это усложнение логики работы приложения, неявное неожиданное выполнение дополнительных действий.

Триггеры усложняют интерфейс хранимых процедур. Поместите все необходимые проверки и действия в рамки хранимых процедур.

Временные таблицы для больших таблиц, табличные переменные — для малых (меньше 1000)

При определении временной таблицы имеет смысл проверить ее на существование:

IF OBJECT_ID(‘tempdb.#eventIDs’) IS NOT NULL begin
DROP TABLE #eventIDs
end
CREATE TABLE #eventIDs ( id int primary key,instanceID int )

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

Как уменьшить вероятность дедлоков на базе

Дедлок — это взаимная блокировка 2 выполняющихся пакетов sql. Это самым негативным образом сказывается на быстродействии запросов.

Чтобы избежать deadlocks, пытайтесь разрабатывать ваше приложение с учетом следующих рекомендаций:

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

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

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

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

Советы по использованию временных таблиц и табличных переменных

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

Если вы SQL-разработчик или администратор MS SQL Server, и вы хотели бы разрабатывать веб-решения на SQL, то веб-платформа Falcon Space — это то, что вам нужно.

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

Вводная статья по Falcon Space для SQL специалиста

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

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

Оптимизируем условия

Теперь настало время произвести оптимизацию самих условных операторов SQL. Большинство запросов используют директиву SQL WHERE, поэтому, оптимизируя условия, можно добиться значительной производительности запросов. При этом почему-то лишь небольшая часть приложений для БД используют оптимизацию условий.

AND

Очевидно, что в серии из нескольких операторов AND условия должны располагаться в порядке возрастания вероятности истинности данного условия. Это делается для того, чтобы при проверке условий БД не проверяла остальную часть условия. Эти рекомендации не относится к БД Oracle, где условия начинают проверяться с конца. Соответственно, их порядок должен быть обратным – по убыванию вероятности истинности.

Ситуация с данным оператором прямо противоположна ситуации с AND. Условия должны располагаться в порядке убывания вероятности истинности. Фирма Microsoft настойчиво рекомендует использовать данный метод при построении запросов, хотя многие даже не знают об этом или, по крайней мере, не обращают на него внимание. Но опять-таки это не относится к БД Oracle, где условия должны располагаться по возрастанию вероятности истинности.

Еще одним условием для оптимизации можно считать тот факт, что если одинаковые колонки располагаются рядом, запрос выполняется быстрее. Например, запрос «. W HERE column1 = 1 OR column2 = 3 OR column1 = 2» будет выполняться медленней, чем запрос «WHERE column1 = 1 OR column1 = 2 OR column2 = 3» . Даже если вероятность истинности условия column2 = 3 выше, чем column1 = 2.

AND + OR

NOT

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

CASE

WHEN 3 THEN 1

WHEN 5 THEN 1

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

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

И еще

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

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

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

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

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

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

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

Заключение

В этой статье показаны самые распространенные способы увеличения производительности SQL-запросов. Тем не менее, чтобы оптимизировать запросы, есть еще очень много разных уловок и трюков. Оптимизация запросов больше похожа на искусство, чем на науку. У каждой базы данных свои встроенные оптимизаторы, которые могут помочь в этом нелегком деле, но всю работу за тебя никто не сделает. Как говорил старенький преподаватель по физике: «Чтобы решать задачи, их нужно решать».

Не рекомендуется использовать ORDER BY в связке с такими операциями, как или Y, потому что данные операторы могут создавать побочные эффекты для сортировки. Как следствие, ты можешь получить неправильно отсортированный набор данных, который может оказаться критическим в некоторых ситуациях. Такое следствие не относится к оптимизации, но забывать о нем не стоит.

Прежде чем повышать производительность сети и наращивать аппаратные средства сервера, попробуй сделать оптимизацию.

У любой SQL-операции есть «коэффициент полезности». Чем выше коэффициент, тем «полезней» операция: запрос выполняется быстрее.

В отличие от компиляторов, не все БД умеют упрощать выражения типа x=1+1-1-1 до x=0. Следовательно, они тратят драгоценное время на выполнение пустых операций. Оптимизируй их заранее.

При использовании функции SUM() можно добиться большей производительности с помощью SUM(x + y) , а не SUM(x) + SUM(y) .

Но если функции SUM() требуются для вычитания, используй противоположное: SUM(x) – SUM(y). S UM(x – y) работает медленнее.

У каждой БД есть свои встроенные оптимизаторы, но они далеки от совершенства. Поэтому оптимизируй заранее.