SQL. Оптимизация запросов SQL. MS SQL Медленно работают запросы

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

Оптимизация SQL Server

Рекомендации при разработке приложения:

  • Для обработки данных используйте хранимые SQL процедуры. Избегайте действий над данными в презентационной и бизнес - логике.
  • Не храните данные (из базы данных) в бизнес - логике. Храните данные в базе данных
  • Не создавайте сложные и/или глубокие объектные иерархии. Создание и использование сложных классов или большого количества объектов, используемых для моделирования сложных правил или ситуаций поглощает много ресурсов и снижает производительность и гибкость приложения (прим переводчика: по-моему, объектные иерархии как раз предназначены для повышения гибкости приложения). Так происходит из-за того, что выделение и освобождение памяти при работе с объектами является ресурсоемким процессом.
  • Пересмотрите проект так, чтобы использовать кеширование соединения с БД и кеширование объектов с помощью Microsoft Transaction Server (MTS). MTS кеширует и соединение с БД, и объекты, что сильно повышает общую производительность и масштабируемость приложения.
  • Если ваше приложение использует SQL запросы, которые по своей природе выполняются долго, постарайтесь сделать так, чтобы ваше приложение выполняло такие запросы в асинхронном режиме. Запросы не должны дожидаться окончания окончания предыдущих запросов, чтобы начать выполняться самим. Один из способов обеспечить это в вашем n - звенном приложении - использование Microsoft Message Queue Server (MSMQ)
  • разбивайте сложные таблицы на несколько, помните, чем больше в вашей таблице столбцов и тяжелых типов (nvarchar(max)), тем тяжелее по ней проход. Если некоторые данные не всегда используются в select с ней, выносите их отдельно в таблицу и связывайте через FK

Как оптимизировать ваш прикладной код для SQL сервера.

Используйте OLE DB, для обращений к SQL серверу.

Инкапсулируйте ваш DML (Data Manipulation Language) в хранимых процедурах (с компиляцией).

Как оптимизировать ваш код Transact-SQL.

Выберите правильные типы данных.

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

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

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

ВНИМАНИЕ! Обычно мы используем везде NVARCHAR. Использование других строковых типов обязательно согласовывайте с техлидом на проекте. 

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

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

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

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

Не читайте больше данных, чем надо.

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

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

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

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

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

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

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

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

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

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

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

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

Инкапсулируйте ваш код в хранимых процедурах

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

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

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

  • Всегда получайте доступ к объектам в одном и том же порядке.
  • Не допускайте пользовательского ввода во время транзакций. Получите все необходимые данные перед началом транзакции.
  • Старайтесь делать транзакции короткими и заключайте их в один пакет (batch)
  • Старайтесь использовать максимально низкий уровень изоляции для пользовательского соединения, которое работает с транзакцией.

Другие правила оптимизации SQL

AND. Очевидно, что в серии из нескольких операторов AND условия должны располагаться в порядке возрастания вероятности истинности данного условия.

  1. Ситуация с данным оператором прямо противоположна ситуации с AND. Условия должны располагаться в порядке убывания вероятности истинности.

NOT. Эту операцию всегда следует приводить к более "читабельному" виду (в разумных пределах, конечно). Так, запрос"...WHERE NOT (column1 > 5)" преобразуется в "...WHERE column1 <= 5". 

  1. Запрос "... WHERE column1 = 5 OR column1 = 6" медленнее чем "...WHERE column1 IN (5, 6)". Операция IN работает гораздо быстрее, чем серия OR. 

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

Сортировка

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

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

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

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

По возможности лучше использовать Where вместо Having

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

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

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

Хранение изображений в БД нежелательно

Как выбирать индексы для оптимальной производительности БД.

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

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

Советы по созданию кластерных индексов.

  • Первичный ключ не всегда должен быть кластерным индексом. Если Вы создаете первичный ключ, тогда SQL сервер автоматически делает первичный ключ кластерным индексом. Первичный ключ должен быть кластерным индексом, только если он отвечает одной из нижеследующих рекомендаций.
  • Кластерные индексы идеальны для запросов, где есть выбор по диапазону или вы нуждаетесь в сортированных результатах. Так происходит потому, что данные в кластерном индексе физически отсортированы по какому-то столбцу. Запросы, получающие выгоду от кластерных индексов, обычно включают в себя операторы BETWEEN, <, >, GROUP BY, ORDER BY, и агрегативные операторы типа MAX, MIN, и COUNT.
  • Кластерные индексы хороши для запросов, которые ищут запись с уникальным значением (типа номера служащего) и когда Вы должны вернуть большую часть данных из записи или всю запись. Так происходит потому, что запрос покрывается индексом.
  • Кластерные индексы хороши для запросов, которые обращаются к столбцам с ограниченным числом значений, например столбцы, содержащие данные о странах или штатах. Но если данные столбца мало отличаются, например, значения типа "да/нет", "мужчина/женщина", то такие столбцы вообще не должны индексироваться.
  • Кластерные индексы хороши для запросов, которые используют операторы GROUP BY или JOIN.
  • Кластерные индексы хороши для запросов, которые возвращают много записей, потому что данные находятся в индексе, и нет необходимости искать их где-то еще.
  • Избегайте помещать кластерный индекс в столбцы, в которых содержатся постоянно возрастающие величины, например, даты, подверженные частым вставкам в таблицу (INSERT). Так как данные в кластерном индексе должны быть отсортированы, кластерный индекс на инкрементирующемся столбце вынуждает новые данные быть вставленным в ту же самую страницу в таблице, что создает "горячую зону в таблице" и приводит к большому объему дискового ввода-вывода. Постарайтесь найти другой столбец, который мог бы стать кластерным индексом.

Советы по выбору некластерных индексов

  • Некластерные индексы лучше подходят для запросов, которые возвращают немного записей (включая только одну запись) и где индекс имеет хорошую селективность (более чем 95 %).
  • Если столбец в таблице не содержит по крайней мере 95% уникальных значений, тогда очень вероятно, что Оптимизатор Запроса SQL сервера не будет использовать некластерный индекс, основанный на этом столбце. Поэтому не добавляйте некластерные индексы к столбцам, которые не имеют хотя бы 95% уникальных записей. Например, столбец с "Да" или "Нет" не имеет 95% уникальных записей.
  • Постарайтесь сделать ваши индексы как можно меньшего размера (особенно для многостолбцовых индексов). Это уменьшает размер индекса и уменьшает число чтений, необходимых, чтобы прочитать индекс, что увеличивает производительность.
  • Если возможно, создавайте индексы на столбцах, которые имеют целочисленные значения вместо символов. Целочисленные значения имеют меньше потерь производительности, чем символьные значения.
  • Если Вы знаете, что ваше приложение будет выполнять один и тот же запрос много раз на той же самой таблице, рассмотрите создание покрывающего индекса на таблице. Покрывающий индекс включает все столбцы, упомянутые в запросе. Из-за этого индекс содержит все данные, которые Вы ищете, и SQL сервер не должен искать фактические данные в таблице, что сокращает логический и/или физический ввод - вывод. С другой стороны, если индекс становится слишком большим (слишком много столбцов), это может увеличить объем ввода - вывода и ухудшить производительность.
  • Индекс полезен для запроса только в том случае, если оператор WHERE запроса соответствует столбцу (столбцам), которые являются крайними левыми в индексе. Так, если Вы создаете составной индекс, типа "City, State", тогда запрос " WHERE City = 'Хьюстон' " будет использовать индекс, но запрос " WHERE State = 'TX' " не будет использовать индекс.
  • Любая операция над полем в предикате поиска, которое лежит под индексом, сводит на нет его использование. where isnull(field,’’) = ‘’ здесь индекс не используется, where field = ‘’ and field is not null - здесь используется.

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

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

  • Временная таблица храниться физически в tempdb, табличная переменная хранится в памяти SQL
  • SQL может сам решить сохранить табличную переменную физически, если там будет много данных, это потеря ресурсов, учтите это
  • Временная таблица для большого объема данных (полноценная выборка), табличная переменная для малого (справочники или набор ID для чего-то)
  • Временная таблица доступна из любой процедуры SQL, табличная переменная только в рамках запроса. Не забывайте очищать временные таблицы после их использования
Примечание