Рекомендации для разработчиков в среде SQL server .
Почему важно соблюдать правила кодирования
(цитата архитекторов SQL Server):
“you should use this server as we build it for”.
« Вы должны использовать этот сервер так , как мы его
разработали»
В данном документе рекомендации даны в сжатой форме . В более развернутой форме
рекомендации можно найти в документах Microsoft для ЦИТТРАНС .
1. Именуйте объекты
полностью (таблицы и процедуры).
Вместо SELECT <поле> FROM VagonArxOper
всегда используйте SELECT <поле> FROM dbo.VagonArxOper .
При вызове процедур так же в обязательном порядке необходимо указывать имя
владельца :
EXEC dbo.pSksMiInsertVagon , вместо EXEC pSksMiInsertVagon
Суть данной рекомендации заключается в том, что
оптимизатор запросов SQL Server работает только с
полностью именованными объектами, то есть с таблицами, которые объявлены как dbo.VagonArxOper (например). Если вы избегаете указывать
схему, которой принадлежит таблица, то построитель плана выполнения запроса
вынужден терять время на поиск полного имени, для чего вынужден просматривать
системные таблицы на предмет поиска этого полного имени. Используя
рекомендуемую практику, вы тем самым окажете помощь оптимизатору запросов в
процессе компиляции плана построения запроса, сократив время его “ненужной”
работы и избежав при этом ненужных перекомпиляций планов выполнения запросов.
Хорошей и более эффективной практикой является полное
название полей таблицы :
Select TOP 1 v.Nov_Vag From dbo.Vagon v where v.Vagid = @Vagid
2. Использование WITH NOLOCK или WITH READUNCOMMITTED
Использование хинтов WITH (NOLOCK) в выражениях SELECT statements позволяет избежать генерации нежелательных блокировок. Такой подход
обеспечивает значительное увеличение производительности решения, так как и сам SELECT работает быстрее, а самое главное - не блокируются параллельные задачи .
Рекомендуется вместо использования хинта NOLOCK просто понижать уровень изоляции транзакции
с помощью команды SET TRANSACTION ISOLATION LEVEL READUNCOMMITTED.
Преимущество данного подхода заключается в том, что данный уровень изоляции
будет распространяться на все объекты, используемые в запросе, тогда как
указание хинта NOLOCK на одной таблице и его отсутствие на
других таблицах, задействованных в запросе, ничего не даст.
TABLOCK
Использование хинта TABLOCK позволяет улучшить производительность операций вставки и
удаления данных в таблицах. В случае, если ваша
операция обновления затронет большое количество строк, имеет смысл с помощью
этого хинта сделать принудительное блокирование
таблицы на время выполнения операции с целью достижения максимальной
производительности.
3.Возвращайте из запроса только те поля, которые
необходимы.
Одной из наиболее часто встречающихся проблем производительности является
написание запросов в стиле SELECT *. Использование конструкции SELECT * не только возвратит вам ненужные данные, но также
может вызвать операцию ПОЛНОГО ПРОСМОТРА вместо ПОИСКА ПО ИНДЕКСУ вне
зависимости от наличия условия WHERE.
4. Используйте команду EXISTS .
Используйте команду COUNT(*) тогда и только тогда ,
когда вам надо знать количество записей . COUNT на время исполнения блокирует для просмотра всю таблицу.
Для проверки факта
наличия записи по какому-то условию В ОБЯЗАТЕЛЬНОМ
ПОРЯДКЕ следует использовать команду IF EXISTS. Например :
IF exists (SELECT *
FROM dbo.nvServerNastr WITH
(NOLOCK )WHERE NastrID = 5 AND Data = 1)
5. Необходимо писать корректные запросы.
-
Никогда автоматически не добавляйте ключевое слово DISTINCT в выражение SELECT.
Полная выборка отработает быстрее ,
чем выборка с ключевым словом DISTINCT
-
Если вы выбираете одну запись ,
то обязательно используйте опцию TOP 1 , это сокращает время выборки , т.к. поиск идет
до первой найденной записи , иначе идет просмотр всей таблицы .Например :
Select TOP 1 v.Nov_Vag From dbo.Vagon v where v.Vagid = @Vagid
-
все соединения между таблицами (JOIN) должны быть верные, а все ключи, используемые для
соединения таблиц, указаны в условиях ON.
6.Избегайте использования таких ресурсоемких операторов
как NOT LIKE.
Некоторые операторы SQL в результате выполнения считаются очень ресурсоемкими. Оператор LIKE в формате “%значение%” почти
всегда вызывает сканирование таблицы. Причиной сканирования является наличие %
в начале значения для поиска. Оператор LIKE в формате “значение%” всегда
будет использовать поиск по индексу, что значительно более эффективно.
Операторы отрицания, такие как NOT LIKE, почти всегда очень неэффективны, и
всегда, при возможности ,следует избегать их
использования. Если вам необходимо проверить наличие строк / условий,
необходимо использовать конструкции IF EXISTS или IF NOT EXISTS.
Если все же вам необходимо выполнить операцию NOT LIKE, рекомендуется использовать новые команды из синтаксиса SQL Server 2005, такие как EXCEPT и INTERSECT.
7.Получение
пересекающихся множеств методом NOT IN ()
Например
SELECT t1.* FROM dbo.trainarxoper t1
WHERE trnid NOT IN
(SELECT trnid
FROM train)
Намного эффективнее будет следующая
конструкция
SELECT t1.* FROM dbo.trainarxoper t1
LEFT JOIN dbo.train
t2 on t2.OperID = t1.OperID
WHERE
t2.OperID is null
8.Избегайте явного или
неявного использования функций в условии поиска WHERE.
Оптимизатор не всегда может выбрать индекс при использовании функции в
условии поиска where по той причине, что условие where будет рассматриваться как выражение, а не как индексированное поле. Общей
проблемой является работа с полями типа datetime.
Следующий запрос, который использует функцию над полем datetime, вызовет полное
сканирование таблицы, не смотря на то, что существует соответствующий индекс:
SELECT OperID
FROM dbo.VagonArxOper
WHERE DATEADD(day, 15, Date_Pop) = @Date
Однако, если мы изменим запрос следующим образом,
то будет использоваться индекс:
SELECT vo.OperID
FROM dbo.VagonArxOper vo
WHERE vo.Date_Pop = DATEADD(day, -15, @Date)
9.Использование
функций обработки даты в условиях поиска
например
select count (*) from trainarxoper where datediff(hour,date_sys,getdate()) < 12
При выполнении данного оператора при поиске
по индексному полю date_sys будет осуществлена операция INDEX_SCAN. Если переписать данный оператор
следующим образом
Declare @H datetime
SET @H = dateadd(hour,
-12, getdate())
select count (*) from trainarxoper where date_sys > @H
не смотря на то,
что потребовалось три строки кода, последний оператор будет выполнен на порядок
быстрее за счет того, что вместо INDEX_SCAN(полный просмотр) будет применяться операция INDEX_SEEK(поиск по индексу), которая на порядок
быстрее.
10.Не выполняйте
динамический код с использованием
команды EXEC() – это одна из самых ресурсоемких команд .
11.Используйте хранимые
процедуры и параметризованные запросы.
Весь выполняемый код следует создавать в виде выполняемых пакетов команд –
хранимых процедур или функций. Если, в крайнем случае, появляется необходимость
выполнить динамический код, то стоит отдать предпочтение использованию sp_executesql с параметрами вместо EXEC() без параметров.
12. Правильно оформляйте
тексты .
В
текстах процедур , триггеров , View
в обязательном порядке должны присутствовать следующие атрибуты :
-
автор
-
назначение
и описание входных / выходных параметров
-
дата
создания
-
дата
последней корректировки
-
комментарии к основным
функциональным блокам
Следует помнить ,
что жизнь программы состоит на 80 % из сопровождения
И самый дорогой вид экономии , это экономия на комментариях .
13.Минимизируйте
использование курсоров.
Курсоры принуждают ядро базы данных последовательно выбирать строки из
таблиц, при этом управляя блокировками, для получения некоего набора данных. По
возможности необходимо использовать forward_only и read_only курсоры (курсоры, открытые только для чтения и только для просмотра по
ходу движения курсора), так как они в наименьшей степени поглощают ресурсы
сервера. Все другие типы курсоров используют временную базу данных tempdb очень интенсивно, что
приводит к перерасходу ресурсов.
В любом случае код, написанный с использованием самого эффективного
курсора, выполняется на 25% медленнее, чем тот же код, написанный с помощью
обычной комбинации SELECT … с последующей обработкой.
Создание курсора на базе временной таблицы вдвойне неэффективно.
14.Чрезмерно
частое создание временных табличных вместо создания табличных переменных.
Повсеместно используемой практикой в процессе
написания хранимых процедур является использование локальных временных таблиц
(например, #temp). При этом данные таблицы содержат
маленькие наборы данных (не более 100 строк), не индексируются, и удаляются
перед окончанием процедуры.
Рекомендуется избегать такой практики работы
и начать использовать табличные переменные вместо временных таблиц.
В качестве выгоды при использовании такого подхода вы сможете найти:
-
план исполнения процедуры не будет
часто перекомпилироваться в зависимости от состояния базы tempdb,
-
временные данных буду храниться в
оперативной памяти (стеке выполнения) процедуры
o
на них не будут влиять блокировки,
возникающие в это время в tempdb
o
доступ к таким таблицам в памяти будет
осуществляться быстрее
Лишь в тех случаях, когда
-
вы можете предвидеть, что во временную
таблицу будет помещен большой набор данных (десятки тысяч строк и более),
-
вы планируете индексировать временную
таблицу для ускорения процесса обработки данных,
оправданно использование временных таблиц,
созданных оператором CREATE TABLE #table.
15.Минимизируйте
использование триггеров.
Следует
максимально сократить использование триггеров . а на
таблицах общего пользования вообще отказаться от их использования .
Триггер позволяет с минимальными усилиями
разработчика получить локальный результат .
Но
в последствии триггера превращаются в балласт, который навешивается на все
команды других задач , которые работают с этими
таблицами . Зачастую размер этого балласта намного превосходит сами операции по
модификации таблицы . И новые пользователи таблиц даже не подозревают об этом
. Вложенность триггеров в нашей системе
доходила до 6-7 уровня .
Заменой
триггеров должны стать прикладные решения
16.Минимизируйте
использование объектов SQL для контроля целостности .
На
данном этапе (этапе бурного развития системы , который
будем надеяться продлиться еще очень долго) следует ограничить использование FOREIGN
KEY . Эта
конструкция очень часто мешает сопровождению системы и установке новых версий .
17.Избегайте написания
универсальных процедур.
Стремление
написать одну процедуру на все случаи жизни практически всегда приводит к
появлению долгоработающих
, тяжелых в сопровождении и труднодоступных в понимании
произведений .
В
качестве альтернативы следует написать нескольких специализированных процедур . Этот вариант
свободен от указанных недостатков .
18.Соблюдайте простые
правила.
-
не
используйте лишних операций CONVERT и ISNULL
- используйте опции SET NOCOUNT ON в заголовке процедуры.
- В конкурентной
предпочтительнее использование SCOPE_IDENTITY вместо @@IDENTITY по той причине, что
SCOPE_IDENTITY гарантирует
получение значение IDENTITY из той таблицы, в
которую вставлялись данные. В случае наличия (и срабатывания) триггера на
таблице значение IDENTITY
может быть неверным (принадлежащим другой таблице).
19
. Не менять в процедурах
настройки базы
Согласно рекомендаций Microsoft необходимо
использовать если не стандартные, то однообразные настройки с
базе данных. Рекомендованные настройки базы данных
выглядят следующим образом:
ANSI_NULLS ON
ANSI_PADDING ON
ANSI_WARNINGS ON
ARITHABORT ON
CONCAT_NULL_YIELDS_NULL
ON
QUOTED_IDENTIFIER ON
Постоянное изменение настроек базы данных приводит к тому, что
сервер вынужден хранить разные планы выполнения одного и того же запроса в
зависимости от настроек базы данных, тем самым затрачивая дополнительные
ресурсы для хранения различных контекстов выполнения планов запроса.
20. Персональное объявление переменных вместо
групповой операции объявления.
Например, вместо
declare @PPOSFOR_DAT datetime
declare @PPOSFOR_IPR int
declare @PPOSOSV_DAT datetime
declare @PPOSOSV_IPR int
declare @PDL numeric(5,1)
declare @PDR numeric(5,1)
declare
@PGRUZ int
рекомендуется использовать
declare @PPOSFOR_DAT datetime, @PPOSFOR_IPR int, @PPOSOSV_DAT datetime,
@PPOSOSV_IPR int, @PDL numeric(5,1), @PDR numeric(5,1), @PGRUZ int
21. Построчное присвоения значений переменным
Например, вместо
set
@opToArenda = 7 -- код операции сдачи в аренду
set
@opFrArenda = 8 -- код операции приема из аренды
set
@opDelWag =
10 -- код операции исключения из инвентаря
set
@opNewWag =
11 -- код операции исключения из инвентаря
set
@opOpKsarm = 53 -- код общей операции отчетности в
базе КСАРМ
необходимо использовать
select
@opToArenda = 7, -- код операции сдачи в аренду
@opFrArenda = 8, -- код операции приема из аренды
@opDelWag = 10, --
код операции исключения из инвентаря
@opNewWag = 11, --
код операции исключения из инвентаря
@opOpKsarm = 53 -- код общей операции отчетности в базе
КСАРМ
Выполнение пакета присвоений одним оператором
будет эффективнее.
Но вместо select @uslovie=NULL рекоммендуется
использовать set @uslovie=NULL.
22. Создание таблиц методом SELECT INTO.
Например, вместо
select
convert(int,0) as idW,B.wagId ,B.wagn
,B.catg ,B.kind ,B.own ,B.state
,B.syst
,B.flLoad ,B.opId ,B.owner
into #acWkPere1
from #acWkPere A LEFT JOIN acDu8St B on( A.idW = B.wagId )where 1=0
рекомендуется использовать явное объявление
таблиц с помощью оператора CREATE TABLE #temp или
использовать табличную переменную (что более эффективно), нежели использовать
оператор SELECT, если заранее известно, что этот оператор не вернет данных, как
в приведенном примере.
23.Выполнение
оператора INSERT INTO без указания списка полей.
Например
insert into #SX
select A.stationEsr,P.value
from #S A left join
( select S.stationEsr,ISNULL( (select P.value
from nvacPaspN P where [id]=23 and P.stationEsr=S.stationEsr)
,(select P.value from nvacPaspN P where [id]=23 and P.stationEsr=0) ) as sx
from #S S ) as B
on A.stationEsr=B.stationEsr
Необходимо
insert into #SX (stationEsr, sx) select A.stationEsr,P.value
from #S A left join
…………..
Приведенная в этом примере техника
программирования также создает совершенно реальные проблемы масштабирования
системы в будущем, когда появится необходимость добавление новых полей в одну
или несколько таблиц. С этой проблемой пришлось столкнуться в результате
попытки добавления нового поля в таблицу VagonArxOper с целью улучшить
механизм сортировки индекса.
