SQL - Практики программирования
Старайтесь избегать серверных курсоров, насколько это возможно.
Старайтесь избегать использовать специальные символы в начале слова при поиске при помощи оператора LIKE , так как это приводит к сканированию индекса, что противоречит цели создания этого индекса. Следующее выражение приводит к сканированию индекса ( index scan ), тогда как второе выражение приводит к поиску в индексе ( index seek ):
SELECT LocationID FROM Locations WHERE Specialities LIKE '%pples'
SELECT LocationID FROM Locations WHERE Specialities LIKE 'A%s'
Также избегайте использовать в поиске операторы неравенства <> и NOT , т.к. это тоже приводит к сканированию индекса.
Используйте вторичные таблицы ( derived tables ) когда только возможно, так как они выполняются быстрее. Обратите внимание на следующий запрос, который выдает вторую наибольшую зарплату из таблицы Employees :
SELECT MIN(Salary)
FROM Employees
WHERE EmpID IN
(
SELECT TOP 2 EmpID
FROM Employees
ORDER BY Salary Desc
)
Тот же запрос может быть переписан с использованием вторичной таблицы, как показано ниже, и будет выполняется в 2 раза быстрее, чем предыдущий запрос:
SELECT MIN(Salary)
FROM
(
SELECT TOP 2 Salary
FROM Employees
ORDER BY Salary DESC
) AS A
Сканирование таблицы или индекса - это очень плохо и должно избегаться везде, где это возможно. Создавайте нужные индексы для нужных столбцов.
Добавляйте SET NOCOUNT ON в начало ваших SQL скриптов, хранимых процедур и триггеров при их выполнении в рабочем режиме, т.к. это убирает сообщения типа '(1 row ( s ) affected )' после выполнения команд INSERT , UPDATE , DELETE и SELECT . Это увеличивает производительность хранимых процедур, снижая трафик.
Не позволяйте Вашим клиентским приложениям запрашивать данные или манипулировать данными напрямую, используя команды SELECT или INSERT / UPDATE / DELETE . Вместо этого создайте хранимые процедуры и дайте Вашим приложениям доступ к этим процедурам. Это делает политику доступа к данным прозрачной и единой для всех модулей Вашего приложения, и одновременно сосредотачивает бизнес-логику внутри базы данных.
По возможности избегайте динамических SQL запросов. Динамический SQL медленнее, чем статический SQL , т.к. SQL Server вынужден создавать план исполнения каждый раз во время работы. Можно воспользоваться операторами IF и CASE , чтобы избежать использования динамического SQL . Другим большим недостатком использования динамического SQL является то, что он требует от пользователей иметь прямые права доступа ко всем объектам в динамическом запросе, например, к таблицам и представлениям. Обычно, пользователи имеют доступ к хранимым процедурам, которые ссылаются на таблицы, но не напрямую к этим таблицам. В этом случае динамический SQL не работает.
Минимизируйте использование NULL , т.к. это часто приводит к проблемам в приложениях, если только эти приложения специально не убирают NULL или не выводят NULL в какой-либо другой форме. Любое выражение, используемое с NULL , дает результат NULL . Функции ISNULL и COALESCE могут помочь в обработке значений NULL .
Всегда используйте список столбцов в Ваших командах INSERT . Это помогает избежать проблем, когда меняется структура таблицы (например, когда добавляется или удаляется столбец). Ниже приведен пример, иллюстрирующий проблему.
Всегда обращайтесь к таблицам в одном и том же порядке во всех Ваших хранимых процедурах и триггерах. Это помогает избежать мертвых блокировок ( deadlocks ). Также существуют следующие способы избежать мертвых блокировок: Делайте Ваши транзакции максимально короткими. Затрагивайте как можно меньше данных во время транзакций. Никогда не ждите ввода данных пользователем посреди транзакции. Не используйте хинты блокировки данных на высоком уровне или ограничивающие уровни изоляции данных, если только они не являются абсолютно необходимыми.
Выведите такие задачи, как манипуляции со строками, сложения строк, подсчет количества записей, изменения регистра, изменения типов, и т.д., в приложения, если эти операции потребляют больше ресурсов ЦП на сервере баз данных. Также старайтесь делать первичную проверку данных в приложениях во время ввода данных. Это уменьшает сетевой трафик.
Если Вашей системе предстоит работать с разными системами управления базами данных, то избегайте работать с битами в T - SQL , т.к. такие функции очень специфичны в каждой системе управления базами данных. Более того, использование битовых масок для хранения различных состояний определенного объекта противоречит правилам нормализации.
Не используйте порядковые номера столбцов в выражении ORDER BY
Не используйте комментарии вида /* */. Используйте --
Не используйте выражение SELECT * в Ваших запросах. Всегда указывайте названия столбцов после оператора SELECT
Используйте выражение SELECT * для функции EXISTS
Не используйте имя базы данных в именовании объектов
Добавляйте имя владельца перед именем таблицы, т.к. это повышает читабельность и позволяет избежать путаницы. Microsoft SQL Server Books Online указывает на то, что добавление имени владельца в имя таблицы даже помогает при повторном использовании плана исполнения, увеличивая производительность.
Работа с хранимыми процедурами
Не указывайте перед именами Ваших хранимых процедур " sp _". Приставка sp _ зарезервирована для системных хранимых процедур, которые поставляются вместе с SQL Server .
Не используйте флаги во входных аргументах хранимой процедуры для изменения ее функциональности. Предпочтительнее разбить ее на две отдельные процедуры.
Не повторяйте вызовы функций в Ваших хранимых процедурах, триггерах, функциях и скриптах. Например, Вам может понадобиться длина строковой переменной во многих местах Вашей процедуры, но не вызывайте функцию LEN каждый раз, а вызовите функцию LEN один раз и сохраните результат в переменную для последующего использования.
Убедитесь, что Ваши хранимые процедуры всегда возвращают значение, показывающее их статус. Стандартизируйте возвращаемые хранимыми процедурами значения для успешного и неуспешного завершения работы процедуры. RETURN должен возвращать только статус выполнения, но не данные. Если Вам нужно возвратить данные, используйте параметры OUTPUT .
Если Ваша хранимая процедура всегда возвращает результат в виде однострочного набора данных, то лучше возвращайте данные, используя параметры OUTPUT вместо SELECT , т.к. ADO обрабатывает возвращаемые параметры быстрее, чем наборы данных, возвращаемые командой SELECT .
Уменьшение рекомпиляций хранимой процедуры
Можно уменьшить COMPILE блокировки , если в коде хранимой процедуры обращаться к объектам БД с указанием владельца этого объекта, например [dbo].[User]
Если было изменено достатчно большое число строк таблицы, на которую ссылается ХП, то произойдет рекомпиляция ХП. Для того, чтобы избежать рекомпиляции такого типа, можно использовать хинт KEEPFIXED PLAN: SELECT COUNT(*) FROM #Temp OPTION (KEEPFIXED PLAN)
Если в коде хранимой процедуры чередуются DDL и DML операции, то ХП будет рекомпилироваться. Если схема какого-то объекта в БД была изменена (в т.ч для временных таблиц), то ХП будет рекомпилирована, при первом обращении к этому объекту. Таким образом, правильно сгруппировав DDL и DML операции можно уменьшить количество рекомпиляций.
Работа с временными таблицами
По возможности избегайте создавать временные таблицы при обработке данных, потому что создание временных таблиц означает большее количество дисковых операций. Вместо временных таблиц лучше максимально используйте дополнительные возможности SQL: представления, табличные переменные и вторичные таблицы.