Нашел очень интересные рекомендации по работе со скулом.

Рекомендации для разработчиков в среде 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 с целью улучшить механизм сортировки индекса.