13 советов по повышению эффективности

Gwynn Group - 24 мая 2019 г.

За последние 18 лет работы над программными проектами, которым требовалась СУБД в качестве внутреннего хранилища, у нас была изрядная доля проблем с производительностью, связанных с SQL, из-за того, как мы писали наши запросы. Ниже приведены несколько советов, которые мы получили на собственном опыте и в книгах и журналах по SQL, чтобы узнать, как написать более эффективный запрос.

ТЕОРИЯ ЗА ВРЕМЯ ВЫПОЛНЕНИЯ ЗАПРОСА

MS SQL - это просто еще одна часть программного обеспечения, которое запускается на компьютере, поэтому на него распространяются те же ограничения, что и на все программное обеспечение: оно может обрабатывать столько информации, сколько способно обработать его оборудование. Есть много способов оптимизировать СУБД для повышения производительности. В этом коротком занятии мы сконцентрируемся на советах по производительности при написании наших запросов. В конечном итоге мы хотим уменьшить количество вычислений, которые СУБД должна выполнять, чтобы сократить время, необходимое для выполнения нашего запроса.

При написании запросов мы должны помнить о следующих вещах:

  1. Размер таблицы. Чем больше таблица, тем больше времени требуется для чтения данных.
  2. Объединения: если ваши объединения существенно увеличивают количество строк в наборе результатов, ваш запрос, скорее всего, будет медленным.
  3. Агрегации: объединение нескольких строк для получения результата требует больше вычислений, чем простое извлечение этих строк.

Уменьшение размера таблицы

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

Упрощение соединений

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

В плеере 26 298 строк. Это означает, что 26 298 строк необходимо оценить на предмет совпадений в другой таблице. Однако, если таблица player была предварительно агрегирована, вы можете уменьшить количество строк, которые необходимо оценить в объединении. Во-первых, давайте посмотрим на агрегирование:

Вышеупомянутый запрос возвращает 252 результата.

Удаление агрегированного запроса в подзапросе с последующим присоединением к нему во внешнем запросе существенно снизит стоимость соединения:

В этом конкретном случае вы не заметите огромной разницы, потому что 30 000 строк не так уж сложно для обработки базы данных. Однако, если вы говорите о сотнях миллионов строк или более, вы заметите заметное улучшение за счет агрегирования перед объединением. При этом убедитесь, что то, что вы делаете, логически согласовано - вам следует беспокоиться о точности своей работы, прежде чем беспокоиться о скорости бега.

13 СОВЕТОВ ПО ЭКСПЛУАТАЦИИ

1. Имя владельца / схемы

Не забудьте добавить префикс к именам объектов (например, таблицы, хранимой процедуры, представления) с их владельцем / именем схемы.

Причина. Если имя владельца / схемы не указано, механизм SQL Server пытается найти его во всех схемах, пока объект не найдет его. Механизм SQL Server не будет искать таблицу вне ее владельца / схемы, если указано имя владельца / схемы.

2. Оператор *

Не используйте оператор * в операторах SELECT. Вместо этого используйте имена столбцов.

Причина: SQL Server сканирует все имена столбцов и заменяет * на все имена столбцов таблиц в операторе SQL SELECT. Указание имен столбцов позволяет избежать этого поиска и замены и повышает производительность.

3. Столбцы, допускающие значение NULL

Избегайте использования NOT IN при сравнении со столбцами, допускающими значение NULL. Вместо этого используйте NOT EXISTS.

Причина. Когда в запросе используется NOT IN (даже если запрос не возвращает строки с нулевыми значениями), SQL Server проверяет каждый результат, чтобы определить, является ли он нулевым или нет. Использование NOT EXISTS не приведет к сравнению с нулевыми значениями. Кроме того, NOT EXISTS возвращает только два состояния (true или false), тогда как NOT IN может возвращать до трех состояний (true, false, NULL) и может не дать вам ожидаемого результата.

4. Табличные переменные и объединения

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

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

5. Имена хранимых процедур

Не начинайте имя хранимой процедуры с sp_.

Причина. Когда хранимая процедура называется sp_ или SP_, SQL Server всегда проверяет системную / главную базу данных, даже если указано имя владельца / схемы. Предоставление имени хранимой процедуре без SP_ позволяет избежать этой ненужной проверки в базе данных system / master в SQL Server.

6. Используйте SET NOCOUNT ON

Используйте SET NOCOUNT ON с операциями DML.

Причина: при выполнении операций DML (т. е. INSERT, DELETE, SELECT и UPDATE) SQL Server всегда возвращает количество затронутых строк. В сложных запросах с большим количеством объединений это становится огромной проблемой производительности. Использование SET NOCOUNT ON повысит производительность, поскольку не будет отслеживать количество затронутых строк.

7. Избегайте использования GROUP BY, ORDER BY и DISTINCT.

По возможности избегайте использования GROUP BY, ORDER BY и DISTINCT.

Причина: при использовании GROUP BY, ORDER BY или DISTINCT механизм SQL Server создает рабочую таблицу и помещает данные в рабочую таблицу. После этого он организует эти данные в рабочей таблице в соответствии с запросом, а затем возвращает окончательный результат.

8. Проверьте индексы.

Должны быть индексы для всех полей, используемых в частях WHERE и JOIN оператора SQL.

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

9. Используйте один и тот же тип данных в предложениях JOIN и WHERE.

Это легче сказать, чем сделать, в зависимости от ваших разрешений на внесение изменений в схему.

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

10. Избегайте использования вычисляемых полей в предложениях JOIN и WHERE

Это можно сделать, создав поле с вычисленными значениями, используемыми в объединении в таблице. См. ниже.

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

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

11. Избегайте множественных объединений в одном запросе

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

Причина. Когда объединения сложные, количество возможных планов выполнения увеличивается в геометрической прогрессии. Это, в свою очередь, уменьшит выбор оптимизатора запросов при выборе порядка и типа соединения. Иногда оптимизатор запросов вынужден использовать объединения вложенных циклов, независимо от последствий для производительности для запросов с чрезмерно сложным перекрестным применением или подзапросами. Кроме того, из-за ограничений по времени и производительности SQL Engine будет пытаться сгенерировать «достаточно хороший» план выполнения, а не пытаться сгенерировать наилучший возможный план выполнения.

12. Избегайте функций с табличным значением с несколькими операторами (TVF)

TVF с несколькими операторами более затратны, чем встроенные TVF.

Причина: SQL Server расширяет встроенные TVF в основной запрос, как расширяет представления, но оценивает TVF с несколькими операторами в отдельном контексте от основного запроса и материализует результаты нескольких операторов во временные рабочие таблицы. Отдельный контекст и рабочая таблица делают TVF с несколькими операторами дорогостоящими.

13. По возможности используйте сжатие данных

Если ваши SQL-серверы привязаны к вводу-выводу, а не к процессору, включение сжатия данных фактически повысит производительность в вашей среде.

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

РАСПРОСТРАНЕННЫЕ ЗАПРОСЫ

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

Если вы посмотрите на план выполнения, запустив SET STATISTICS PROFILE ON, вы увидите, что для выполнения распределенного запроса SQL1 отправляет запрос в SQL2 для отправки статистической информации для таблицы emp в тесте базы данных.

Еще одним недостатком распределенного запроса является то, что, хотя у вас есть предложение WHERE, вы можете заметить, что когда запрос отправляется для получения строк таблицы на удаленном сервере, SQL Server просто отправит SELECT * FROM удаленной таблице, а затем локально отфильтровать необходимые данные после применения предикатов.

ОТКРЫТЫЙ ЗАПРОС

Выполнить указанный сквозной запрос на указанном связанном сервере. SQL Server отправляет сквозные запросы в виде неинтерпретируемых строк запроса в источник данных OLE DB. То есть SQL не будет применять какую-либо логику к запросу и не будет пытаться оценить, что этот запрос будет делать. Вместо этого он просто передаст указанный запрос на целевой связанный сервер. Открытые запросы полезны, когда вы не ссылаетесь на несколько серверов в одном запросе. Обычно это происходит быстро, поскольку SQL не разбивает его на несколько операций и не выполняет никаких локальных действий с полученным выводом.

В OPENQUERY SQL Server отправляет полный запрос на удаленный сервер SQL2, и ресурсы SQL2 расходуются на обработку запроса, такую ​​как синтаксический анализ операторов SQL, создание плана и фильтрация строк в соответствии с предикатами. Затем окончательный набор результатов отправляется в SQL1 для дальнейшей обработки.

Нравится то, что вы читаете? Обязательно хлопайте и прокомментируйте ниже, как наши советы по производительности SQL сработали для вас!