9 способов ускорить ваши SQL-запросы

SQL — ведущий язык для разработки и запроса баз данных , но у него есть несколько особенностей.
9 лучших практик для более быстрых SQL-запросов:

Получите только те столбцы, которые вам нужны
Используйте CASE вместо UPDATE для условного обновления столбцов.
Сведите к минимуму запросы к большим таблицам
Предварительная подготовка ваших данных
Выполняйте удаления и обновления в пакетном режиме
Используйте временные таблицы для повышения производительности курсора
Используйте функции с табличным значением вместо скалярных функций.
Используйте секционирование, чтобы избежать перемещения больших объемов данных.
Используйте хранимые процедуры для повышения производительности, используйте ORM для удобства.
Получите только те столбцы, которые вам нужны

Распространенной привычкой SQL является использование SELECT  в запросе, поскольку перечислять все необходимые столбцы утомительно. Кроме того, иногда эти столбцы могут со временем меняться, так почему бы просто не сделать все проще?
Но что произойдет, если вы запросите все столбцы таблицы, содержащей сотню или более столбцов? Такие чудовища появляются в дикой природе с удручающей регулярностью, и переработать их под более вменяемую схему не всегда возможно. Иногда единственный способ приручить этого зверя — выбрать подмножество столбцов, которое не позволит другим запросам испытывать нехватку ресурсов.
Его можно использовать SELECT при создании прототипа запроса, но все, что направляется в производство, должно запрашивать только те столбцы, которые действительно используются.
Используйте CASE вместо UPDATE для условного обновления столбцов.
Разработчики часто используют еще кое-что UPDATE ... WHERE, чтобы установить значение одного столбца на основе значения другого столбца, например UPDATE Users SET Users.Status="Legacy" WHERE Users.ID<1000. Этот подход прост и интуитивно понятен, но иногда добавляет ненужный шаг.
Например, если вы вставляете данные в таблицу, а затем используете UPDATE их для изменения, как я только что показал, это две отдельные транзакции. Если у вас миллионы строк, дополнительные транзакции могут создать множество ненужных операций.
Лучшим решением для такой масштабной операции является использование встроенного CASE оператора в запросе для установки значения столбца во время самой операции вставки . Таким образом, вы обрабатываете как первоначальную вставку, так и измененные данные за один проход.
Сведите к минимуму запросы к большим таблицам.
Запросы к таблицам любого размера не бесплатны. Запросы к таблицам с сотнями миллионов или миллиардами строк совершенно не бесплатны.
По возможности объединяйте запросы к большим таблицам с наименьшим количеством отдельных операций. Например, если у вас есть таблица, в которой вы хотите выполнить запрос сначала по одному столбцу, а затем по другому, сначала объедините ее в один запрос , а затем убедитесь, что столбцы, к которым вы запрашиваете, имеют покрывающий индекс.
Если вы обнаружите, что берете одно и то же подмножество данных из большой таблицы и выполняете к нему запросы меньшего размера, вы можете ускорить работу для себя и других, сохранив подмножество в другом месте и выполняя запросы к нему. Это подводит нас к следующему совету.
Предварительная подготовка ваших данных.
Допустим, вы или другие сотрудники вашей организации регулярно запускаете отчеты или хранимые процедуры, требующие агрегирования большого количества данных путем объединения нескольких больших таблиц. Вместо того, чтобы каждый раз заново запускать соединение, вы можете сэкономить себе (и всем остальным) много работы, «предварительно поместив» его в таблицу специально для этой цели. Затем отчеты или процедуры могут выполняться с этой таблицей, поэтому общую для них работу необходимо выполнить только один раз. Если у вас есть для этого ресурсы и ваша база данных поддерживает это, вы можете использовать таблицу в памяти, чтобы еще больше ускорить процесс.
Выполняйте удаления и обновления в пакетном режиме.
Представьте себе таблицу с миллиардами строк, из которых нужно очистить миллионы. Наивный подход — просто запустить DELETE транзакцию. Но тогда вся таблица будет заблокирована до завершения транзакции.
Более сложный подход заключается в выполнении операции удаления (или обновления) в пакетном режиме, который можно чередовать с другими операциями. Каждая транзакция становится меньше и ею легче управлять, а рядом с ней и во время нее можно выполнять другую работу.
Со стороны приложения это хороший вариант использования очереди задач, которая может отслеживать ход операций между сеансами и позволяет выполнять их как фоновые операции с низким приоритетом.
Используйте временные таблицы для повышения производительности курсора.
По большей части курсоров следует избегать — они медленные, блокируют другие операции, и все, что они выполняют, почти всегда можно сделать каким-то другим способом. Тем не менее, если по какой-либо причине вы застряли при использовании курсора, временная таблица может уменьшить связанные с ней проблемы с производительностью.
Например, если вам нужно пройтись по таблице и изменить столбец на основе некоторых вычислений, вы можете взять данные-кандидаты, которые хотите обновить, поместить их во временную таблицу, пройти по ней с помощью курсора , а затем применить все обновления за одну операцию. Таким же образом вы также можете разбить обработку курсора на пакеты.
Используйте функции с табличным значением вместо скалярных функций.
Скалярные функции позволяют инкапсулировать вычисления в фрагмент SQL, подобный хранимой процедуре. Обычной практикой является возврат результатов скалярной функции в виде столбца запроса SELECT.
Если вы часто делаете это в Microsoft SQL Server, вы можете повысить производительность, используя вместо этого функцию с табличным значением и используя ее  CROSS APPLY в запросе. Подробнее о малообсуждаемом APPLYоператоре смотрите в этом обучающем модуле от Microsoft Virtual Academy .
Используйте секционирование, чтобы избежать перемещения больших объемов данных.
SQL Server Enterprise предлагает «разделение», которое позволяет разделить таблицы базы данных на несколько разделов. Если у вас есть таблица, которую вы постоянно архивируете в другую таблицу, вы можете не использовать ее INSERT/DELETE для перемещения данных, а использовать SWITCH вместо нее.
Например, если у вас есть таблица, которая ежедневно очищается в архивную таблицу, вы можете выполнить эту операцию очистки и копирования, используя , SWITCHчтобы просто назначить страницы в ежедневной таблице архивной таблице. Процесс переключения занимает на порядки меньше времени, чем копирование и удаление вручную. У Катрин Вильгельмсен есть отличное руководство о том, как использовать секционирование таким образом .
Используйте хранимые процедуры для повышения производительности, используйте ORM для удобства.
ORMS — объектно-реляционные преобразователи — представляют собой наборы программных инструментов, которые создают программно генерируемый код SQL. Они позволяют вам использовать язык программирования вашего приложения и его метафоры для разработки и поддержки ваших запросов.
Многие разработчики баз данных принципиально не любят ORM. Они печально известны тем, что создают неэффективный, а иногда и неоптимизируемый код, и они дают разработчикам меньше стимулов изучать SQL и понимать, что делают их запросы. Когда разработчику нужно вручную написать запрос, чтобы добиться максимально возможной производительности, он не знает, как это сделать.
С другой стороны, ORM значительно упрощают написание и поддержку кода базы данных. Часть приложения, связанная с базой данных, не находится где-то в другом домене, и она написана таким образом, чтобы более слабо связана с логикой приложения.
Наиболее целесообразно использовать хранимые процедуры для запросов, которые вызываются постоянно, требуют хорошей производительности, вряд ли будут часто меняться (если вообще когда-либо) и требуют проверки производительности с помощью инструментов профилирования базы данных. Большинство баз данных упрощают получение такой статистики в совокупности для хранимой процедуры, чем для специального запроса. Хранимые процедуры также легче оптимизировать с помощью планировщика запросов к базе данных.
Обратной стороной перемещения большей части логики базы данных в хранимые процедуры является то, что ваша логика гораздо более тесно связана с базой данных. Хранимые процедуры могут превратиться из преимущества в производительности в огромный технический долг. Если позже вы решите перейти на другую технологию баз данных, проще изменить цель ORM, чем переписывать все хранимые процедуры. Кроме того, код, сгенерированный ORM, можно проверить на предмет оптимизации, а кэширование запросов часто позволяет повторно использовать наиболее часто генерируемые запросы.
Если важна возможность сопровождения на стороне приложения, используйте ORM. Если речь идет о производительности на стороне базы данных, используйте хранимые процедуры.