Оптимизация хранимых процедур в SQL Server

    Доброго дня, хабрачеловек. Сегодня я бы хотел обсудить с вами тему хранимых процедур в SQL Server 2000-2005. В последнее время их написание занимало львиную долю моего времени на работе и чего уж тут скрывать – по окончанию работы с этим делом осталось достаточно информации, которой с удовольствием поделюсь с тобой %пользовательимя%.
    Знания, которыми я собираюсь поделиться, к сожалению,(или к счастью) не добыты мной эмперически, а являются, в большей степени, вольным переводом некоторых статей из буржуйских интернетов.
    Итак, как можно понять из названия речь пойдет об оптимизации. Сразу оговорюсь, что все действия, которые я сейчас буду описывать, действительно дают существенный(некоторые больший, некоторые меньший) прирост производительности.
    Данная статья не претендует на полное раскрытие темы оптимизации, скорее это собрание практик, которые я применяю в своей работе и могу ручаться за их эффективность. Поехали!


    1. Включай в свои процедуры строку — SET NOCOUNT ON: С каждым DML выражением, SQL server заботливо возвращает нам сообщение содержащее колличество обработанных записей. Данная информация может быть нам полезна во время отладки кода, но после будет совершенно бесполезной. Прописывая SET NOCOUNT ON, мы отключаем эту функцию. Для хранимых процедур содержащих несколько выражений или\и циклы данное действие может дать значительный прирост производительности, потому как колличество трафика будет значительно снижено.

    CREATE PROC dbo.ProcName
    AS
    SET NOCOUNT ON;
    --Здесь код процедуры
    SELECT column1 FROM dbo.TblTable1
    --Перключение SET NOCOUNT в исходное состояние
    SET NOCOUNT OFF;
    GO


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

    SELECT * FROM dbo.MyTable --Вот так делать хорошо
    -- Вместо
    SELECT * FROM MyTable --А так делать плохо
    --Вызов процедуры
    EXEC dbo.MyProc --Опять же хорошо
    --Вместо
    EXEC MyProc --Плохо!

    3. Не используй префикс «sp_» в имени своих хранимых процедур: Если имя нашей процедуры начинается с «sp_», SQL Server в первую очередь будет искать в своей главной базе данных. Дело в том, что данный префикс используется для личных внутренних хранимых процедур сервера. Поэтому его использование может привести к дополнительным расходам и даже неверному результату, если процедура с таким же имененем как у вас будет найдена в его базе.

    4. Используй IF EXISTS (SELECT 1) вместо IF EXISTS (SELECT *): Чтобы проверить наличие записи в другой таблице, мы используем выражение IF EXISTS. Данное выражение возвращает true если из внутреннего выражения возвращается хоть одно изначение, не важно «1», все колонки или таблица. Возращаемые данные, в принципе никак не используются. Таким образом для сжатия трафика во время передачи данных логичнее использовать «1», как показано ниже:

    IF EXISTS (SELECT 1 FROM sysobjects
    WHERE name = 'MyTable' AND type = 'U')


    5. Используй TRY-Catch для отлова ошибок: До 2005 сервера после каждого запроса в процедуре писалось огромное колличество проверок на ошибки. Больше кода всегда потребляет больше ресурсов и больше времени. С 2005 SQL Server'ом появился более правильный и удобный способ решения этой проблемы:

    BEGIN TRY
    --код
    END TRY
    BEGIN CATCH
    --код отлова ошибки
    END CATCH


    Заключение
    В принципе на сегодня у меня всё. Еще раз повторюсь, что здесь лишь те приёмы, которые использовал лично я в своей практике, и могу ручаться за их эффективность.

    P.S.
    Мой первый пост, не судите строго.
    AdBlock похитил этот баннер, но баннеры не зубы — отрастут

    Подробнее
    Реклама

    Комментарии 21

      +3
      Ну вдогонку — не используйте «Select *» а явно перечисляйте колонки.
      Получаем сразу множество преимуществ:
      1. В случае изменения таблицы — сразу будет виден косяк в ХП (я надеюсь вы используете для разработки Visual Studio Database Edition и выше? :) ).
      2. Вы не потащите лишние данные если вдруг добрый товарищ добавит колонку с каким-либо блобом =)
      3. SQL Server не будет делать запрос к системным таблицам для получения списка столбцов, вероятность использования индекса выше, если есть подходящий индекс с включенными данными — за страничками с данными сиквел вообще не полезет, возьмет из индекса и все такое
        +1
        Ну просто здесь речь шла конкретно о хранимках, если о SQL вобщем то да еще много можно написать.
          +1
          >> 1. Включай в свои процедуры строку — SET NOCOUNT ON:
          ЮЮ Прописывая SET NOCOUNT ON, мы отключаем эту функцию. Для хранимых процедур содержащих несколько выражений или\и циклы данное действие может дать значительный прирост производительности, потому как колличество трафика будет значительно снижено.

          COUNT это INT. 4 байта сэкономили?

          Потом, прикладной код может успешность операции проверять как раз по COUNT, получим весёлую отладку выражения

          UPDATE users SET password = @newpassword WHERE id=@id AND password = @oldpassword

          >> Используй IF EXISTS (SELECT 1) вместо IF EXISTS (SELECT *):
          >> Таким образом для сжатия трафика во время передачи данных логичнее использовать «1», как показано ниже:

          Между какими узлами трафик?

          >> 5. Используй TRY-Catch для отлова ошибок:
          bit.ly/aNTGoq
            0
            Почему же 4. 4 это если у вас в запросе всего 1 выражение и нет лупов. А если хранимка огромная, ну или прсото большая, считайте с каждого выражения и лупа возвращается, COUNT который в большинстве случаем абсолютно вам не нужен. Допустим мне нравится чтоб мой код возвращал только те данные, что мне нужно без лишней лапши так помоему правильно. НО я не претендую на истину в последней инстанции

            Я работаю с удаленным серваком. И вообще логически полдумайте, вы запрашиваете БД вернуть все данные таблицы только для того что бы проверить что они есть. Помоему не разумно.

            А кто говорит о бизнес логике? Разве это может использоваться только дли бизнес логики?
              0
              >> Почему же 4. 4 это если у вас в запросе всего 1 выражение и нет лупов. А если хранимка огромная, ну или прсото большая, считайте с каждого выражения и лупа возвращается,

              И мы переходим к пункту 5.

              >> А кто говорит о бизнес логике? Разве это может использоваться только дли бизнес логики?

              А для чего ещё могут использоваться циклы, которые вы назвали лупами, в огромной хранимой процедуре?
              0
              по п.5 — угу, только БЛ БЛу рознь, а транзакционность вы обеспечиваете наверное костылем по имени MSDTC?
                0
                Транзакционность где? SQL соединение, SQL сервер, SQL сервер на уровне сервера приложения, вообще не SQL?
                  0
                  Уровня SQL соединения. Все вынести в код и использовать SQL сервер только как тупой CRUD — реально только в достаточно примитивных задачах. Как только возникает что-либо посложней, где нужны транзакции — «усьо», пишем групповое сохранение в транзакции в ХП — по сути бизнес-логика. А по-другому никак (представьте себе транзакция прерывается уборщицей на уровне электропитания :) ). И никакие внешние фреймворки и навороченная бл в коде не спасет.
                    0
                    connection.CreateTransaction()? Я транзакции внутри SQL использую только для сохранения структурной целостности данных, например при денормализации. Логическая целостность тоже требует транзакций, но инициируются они вне хранимой процедуры.
                      +1
                      BeginTransaction()*
                      Могут получаться длинные транзакции, с ненужными блокировками индексов по куче таблиц — зачем это все? Если после вызова BeginTransaction() у вас возникает длительная задержка — серьезный кусок бд будет блокирован по таймауту (а он большой, т.к. есть достаточно большие объемы данных). И зачем мы создаем себе гемморой с блокировками? Только ради идеи что транзакции должны быть именно коде. Может сама идея не очень умна и гонимся за непонятной и ненужной гибкостью?

                      Да, можно конечно сначала накопить все данные для сохранения, потом открыть транзакцию, кинуть туда все скопом и закрыть ее. но в таком случае — зачем транзакция именно в коде — формируется простой XML, туда засовываются данные любой разнородности и передается в ХП, которая внутри прекрасно все разбирает и записывает. Транзакция в коде ради самой идеи что это в коде а не в SQL SP — имхо идолопоклонничество.
                        0
                        Может стоит прочитать про UnitOfWork?
                          0
                          Опять идолопоклонничество?

                          Расскажите пожалуйста, какое именно отношение к вопросу о предпочтениях Code.vs.SQL транзакции имеет Unit Of Work, который представляет из себя последовательные вызовы (с накоплением данных или без) сервисов\методов?

                          А теперь представьте что DAL уровень у вас — изолированный отдельный сервис с публичным контрактом (чем он, по-хорошему, и должен быть).
                          И вы выставите в публичный контракт тупой CRUD только потому что вам лень описывать именно сценарии использования, типа «вот вам алфавит, поэму сами пишите»? Имхо это глупо, кстати также считает и MS (если мне не верите =)) — Anti-Pattern 1: CRUDy Interface.

                          Дело в том, что сейчас в относительно крупном проекте мы уже благополучно наелись от CRUDy интерфейса к БД. Для нас это вылилось в пару десятков методов которые никогда не используются, но их надо поддерживать в связи с изменением базы и в «цепочку фрикаделек», когда частые бизнес-действия состоят из 5-7-10 вызовов этого гребанного CRUD интерфейса. И изменять все это очень говенно (особенно когда круд еще не просто круд, а автогенерируемый, обобщенный и с иерархией наследования).

                          Простота до такой степени не всегда является преимуществом ( та же идея про алфавит, и в общем YAGNI — оставляй только то, чем пользуешься.).
                            0
                            Не идолополконничество, а совет. Вы описали UoW не назвав его, из чего я предположил, что вы с ним не знакомы.

                            Отношение имеет то, что SQL транзакции совмещённые с накоплением данных короче, а это преимущество. Можно оспаривать ценность этого преимущества в конкретных ситуациях, но то что короткие SQL транзакции лучше длинных, очевидно.

                            Что касается ссылки на Microsoft, она нелепа. Мейнстрим в данным момент это Linq и подобные ему методы, где слой DAL вполне себе CRUD. Разные ORM предлагают аналогичную структуру. А SoA далеко не мейнстрим и имеет вполне чёткую область применения.

                            На уровне DAL не пишется только CRUD, я к этому и не призываю. Но любые атомарные операции, будь это CRUD или что-то другое приходится объединять в транзакции. Прежде всего в бизнес-транзакции, а не SQL транзакции. Ваш подход приведёт к появлению процедур выполняющих одни и те же действия в рамках разных операций. Операции DAL перестанут быть атомарными и станут нести элементы бизнес логики. Соответственно рефакторинг удорожается.

                            В конечном итоге вы окончательно отобразите бизнес-транзакции на SQL транзакции. Хорошо если вы не пишете крупных приложений. А то знаете, бизнес-транзакция может длится несколько дней и существовать далеко за пределами SQL. Иногда надо в рамках одного из шагов бизнес-транзакции послать письмо, пластиковую карту проверить, чек напечатать или ещё что-нибудь. И вся бизнес-логика в SQL будет этому отчаянно сопротивляться.
                              0
                              Мэйнстрим — звучит как «мода». Знаете, у нас все тоже хорошо начиналось, CRUD казался таким простым и удобным, но постоянная борьба с самоизобретенными проблемами утомила. Можно много и долго ссылаться на мэйнстрим и рассказывать что миллионы мух не могут ошибаться, но в большинстве задач с которыми я сталкивался — круд работал не очень. Вернее вокруг него надо было доворачивать доп инфраструктуру, всегда помнить про различные нюансы «Тут транзакцию верти, там не верти, тут рыба завернута а там заглушка».
                              Новый человек в проекте наступал на ровно те же самые грабли что и исходные разработчики. Какой рефакторинг если в руках «алфавит»…

                              Linq сама по себе идея хорошая. Linq2SQL ковыряли — вы видели какие он запросы генерит на 2-3 джойна с условиями? Там не то что о точном контроле за происходящим, там о грубом понимании речь идет с трудом. К тому же постоянно лазит в таблицы за данными о структуре. Вроде мелочи но при больших объемах…

                              По поводу бизнес-транзакций — да с несколькодневными транзакциями не работал, ничего не могу сказать. Вот только не очень понимаю причем тут транзакция в сиквеле, все равно на каждом этапе внутри бизнес транзакции вы будет открывать (или держать открытой все дни 8-0 ??!!) SQL транзакцию, скидывать консистентный блок данных для определенного шага бизнес-транзакции и закрывать ее. Как будет происходить откат такой бизнес-транзакции, если например письмо и карту уже выслали и бумажную почту уже отнесли в отделение?

                              Тут механизмы, насколько я понимаю, настолько другие, что речь о поведении похожем на SQL Transaction не идет.
                                0
                                Ту работает Unit of Work :-) Ну и, ради единообразия, транзакции делающие что-то больше, чем сохранение структурной ценности — моветон.

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

                                Например, надо знать о частичном исполнении вида: заказ на складе собран, но ещё не отгружен. Это тоже эдакая бизнес-транзакция, история зря собранных заказах никому не нужна.
                                  0
                                  Так, мухи отдельно, котлеты отдельно. Судя по вашему пояснению — понятие бизнес-транзакции не имеет отношения ни к бизнесу, ни к транзакциям. Ибо видеть промежуточные данные в транзакции — dirty read =(.
                                  Для бизнеса же все перечисленное — просто этапы выполнения заказа. Зачем это называть транзакцией. Или просто для красивого словца — «ну как же, чем больше мы придумаем сложных и непонятных слов, тем быстрее запутаем собеседника и он может поверить, что правда в »? И знаете, я бы не рискнул прикручивать всякие UoW к таким этапам, а лучше бы завершение каждого этапа -явно фиксировал в БД, включая все данные необходимые для продолжения обработки ( и если надо — для отката). Итого мы приходим снова к коротким групповым записям в БД с транзакциями в бд.

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

                                  «И ради единообразия..» — в армии есть поговорка — «пусть безобразно, но единообразно». Вот я так системы не люблю писать, поэтому упреки в моветонах — оставьте, каждому решению — свое место и время.
                                    0
                                    Вы правильно сказали про dirty read. Например, мне не надо видеть промежуточное состояние заказа на другом складе или вообще другого оператора.

                                    Транзакция это одно или несколько действий которые либо все вместе исполняются, либоне исполняется ни одно их них. Бизнес-транзакция это тоже самое, но для бизнес-операций. То есть собранный заказ, если его не оплатили, надо разложить обратно по полкам и вернуть остатки складов.
                                      0
                                      Далеко не все бизнес-действия можно отменить и в таком случае состояние будет не возвращено а изменено, т.е. не одиночный переход А->В, а попытка перехода А->В и из некоторого промежуточного состояния А1->C. C!=B :).

                                      В любом случае мне кажется неправильно складывать в одну кучу идеологию и бизнес (БТ) и SQL транзакций (ДБ-Т ).
                                      К тому же вы, размышляя о БТ как о ДБ-Т, можете попасть в ловушку некорректных аналогий — кажется что с БТ надо работать также как привыкли с ДБ-Т, но правильнее работать по-другому.

                                      «стандартизация позволяет удешевлять поддержку» — ну только начали за здравие и снова за упокой. Стандартно говенный код — поддерживать не менее дорого чем нестандартно говенный. ИМХО соотношения между однообразием и простотой понимания кода очень очень мало. Ну только за исключением что однообразное говно чуть легче понять, потому что везде одни и те же грабли.

                                      Существует целый класс проблем описываемых понятием овердизайн — когда в погоне за паттернами (стандартизация архитектурного решения) и стандартизацией подхода (повсеместная генерация однообразного кода, например массовая генерация того же CRUD слоя, где 90% функций не будет использовано, но генерятся — на всякий случай) получается такая каша, что, несмотря на ее стандартизацию, она слабоюзабельна и плохо поддерживаема.
                                        0
                                        >> Далеко не все бизнес-действия можно отменить

                                        Согласен, например при отмене заказа упаковочную бумагу обратно не вернёшь.

                                        С другой стороны надо стремится с дешёвой отменяемости операций. Это уже задача не оптимизации, а менеджмента. Собственно дословная автоматизация, без осознания новых возможностей, вредна. Мне приходилось сталкиваться с ситуациями, когда через год после начала проекта требования клиента были отрывочны и противоречивы, так как основывались на операционных реалиях, а не генеральном плане развития. Конечно же правильно выделить бизнес-процессы не получалось и в результате автоматизации хаоса получился автоматизированный хаос.

                                        >> Стандартно говенный код — поддерживать не менее дорого чем нестандартно говенный.

                                        Да, но полезности стандартизации это не отменяет. Есть стили кодирования, правила именования файлов, и.т.п очевидные стандарты. Теперь есть в части проекта вы работаете через Hibernate, в другой читаете и пишете ручками, а в третьей пихаете в процедуру заранее сформированный XML, то даже у вас проблема. Даже если всё это на своём месте оптимально, собранные в один проект разнородные куски проект удорожают. Уже нельзя перекинуть разработчика с одного модуля на другой, он там не разберётся. Количество людей разбирающихся в конкретной строке исходного кода резко падает до активных разработчиков. Единообразие позволяет читать другой код. Если код write-only, а при таком зоопарке иначе и не будет, разработка превратится в кошмар.

                                        Так что да, транзакции внутри хранимых процедур хорошо только до тех пор, пока описываемая бизнес-транзакция не вылезает из рамок SQL транзакции. Как только начинает вылезать, появляются проблемы. Чтобы их решить, надо инициировать SQL транзакции самостоятельно. Но коль скоро мы начали это делать, лучше так делать везде, иначе какое-то поведение очевидно для Васи, будет неочевидным для сидящего рядом Пети. Вася уверен что транзакция внутри хранимой процедуры, Петя уверен что транзакции внутри хранимой процедуры нет. В итоге гарантированно получим race condition. Если стандартизация спасёт от race condition, я готов её внедрять. А реальной пользы существенно больше, чем спасение от race condition, это просто пример.
                                      0
                                      И по поводу «ради единообразия». Единообразия, говоря другими словами, стандартизация позволяет удешевлять поддержку.
                0
                4 пункт чушь. Читаем внимательно справку msdn.microsoft.com/en-us/library/ms189259.aspx и понимаем что все что написано в select в подзапросе exists не вычисляется и не отбирается.
                Какого только бреда я не видел в попытках ускорить конструкцию exists.
                Пишут и exists(select count(1) from tbl) или exists(select top 1 1 from tbl) или exists(select top 1 count(1) from tbl).

                Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

                Самое читаемое