Использование SQLCLR для увеличения производительности

    Начиная c MS SQL Server 2005 в распоряжение разработчиков баз данных была добавлена очень мощная технология SQL CLR.

    Эта технология позволяет расширять функциональность SQL сервера с помощью .NET языков, например C# или VB.NET.

    Используя SQL CLR можно создавать написанные на высокопроизводительных языках свои хранимые процедуры, триггеры, пользовательские типы и функции, а также агрегаты. Это позволяет серьезно повысить производительность и расширить функциональность сервера до немыслимых границ.

    Рассмотрим простой пример: напишем пользовательскую функцию разрезания строки по разделителю используя SQL синтаксис и SQL CLR на базе C# и сравним результаты.

    Пользовательская функция, возвращающая таблицу


    CREATE FUNCTION SplitString (@text NVARCHAR(max), @delimiter nchar(1))
    RETURNS @Tbl TABLE (part nvarchar(max), ID_ORDER integer) AS
    BEGIN
      declare @index integer
      declare @part  nvarchar(max)
      declare @i   integer
      set @index = -1
      set @i=1
      while (LEN(@text) > 0) begin
        set @index = CHARINDEX(@delimiter, @text)
        if (@index = 0) AND (LEN(@text) > 0) BEGIN
          set @part = @text
          set @text = ''
        end else if (@index > 1) begin
          set @part = LEFT(@text, @index - 1)
          set @text = RIGHT(@text, (LEN(@text) - @index))
        end else begin
          set @text = RIGHT(@text, (LEN(@text) - @index))
        end
        insert into @Tbl(part, ID_ORDER) values(@part, @i)
        set @i=@i+1
      end
      RETURN
    END
    go


    Эта функция разрезает входную строку используя разделитель и возвращает таблицу. Применять такую функцию очень удобно, например, для быстрого заполнения временной таблицы записями.
    select part into #tmpIDs from SplitString('11,22,33,44', ',')

    В результате таблица #tmpIDs будет содержать
    11
    22
    33
    44

    Модуль CLR написанный на C#


    Создадим файл SplitString.cs со следующим содержимым:
    using System;
    using System.Collections;
    using System.Collections.Generic;
    using System.Data.SqlTypes;
    using Microsoft.SqlServer.Server;

    public class UserDefinedFunctions {
      [SqlFunction(FillRowMethodName = "SplitStringFillRow", TableDefinition = "part NVARCHAR(MAX), ID_ORDER INT")]

      static public IEnumerator SplitString(SqlString text, char[] delimiter)
      {
        if(text.IsNull) yield break;

        int valueIndex = 1;
        foreach(string s in text.Value.Split(delimiter, StringSplitOptions.RemoveEmptyEntries)) {
          yield return new KeyValuePair<int, string>(valueIndex++, s.Trim());
        }
      }

      static public void SplitStringFillRow(object oKeyValuePair, out SqlString value, out SqlInt32 valueIndex)
      {
        KeyValuePair<int, string> keyValuePair = (KeyValuePair<int, string>) oKeyValuePair;
        
        valueIndex = keyValuePair.Key;
        value = keyValuePair.Value;
      }
    }

    Скомпилируем модуль:
    %SYSTEMROOT%\Microsoft.NET\Framework\v2.0.50727\csc.exe /target:library c:\SplitString.cs

    На выходе получаем SplitString.dll

    Теперь, необходимо разрешить использование CLR в SQL Server.
    sp_configure 'clr enabled', 1
    go
    reconfigure
    go

    Все, можно подключать модуль.

    CREATE ASSEMBLY CLRFunctions FROM 'C:\SplitString.dll'
    go

    И создавать пользовательскую функцию.
    CREATE FUNCTION [dbo].SplitStringCLR(@text [nvarchar](max), @delimiter [nchar](1))
    RETURNS TABLE (
    part nvarchar(max),
    ID_ODER int
    ) WITH EXECUTE AS CALLER
    AS
    EXTERNAL NAME CLRFunctions.UserDefinedFunctions.SplitString


    Дополнительно о CLR


    1. Сборка загружается на сервер и хранится там. Функции, которые ссылаются на сборку уже хранятся в базе. Поэтому нужно, чтобы на сервере, куда переносится база, сборка была загружена.

    2. При создании сборки, если нужно, указывается аргумент PERMISSION_SET, который определяет разрешения для сборки. Советую посмотреть MSDN. Вкратце: SAFE — разрешает работать только с базой; EXTERNAL_ACCESS — разрешает работать с другими серверами, файловой системой и сетевыми ресурсами; UNSAFE — все что угодно, включая WinAPI.

    3. Есть особенности при отладке, какие именно, указано в MSDN.

    Результаты


    Для сравнения скорости работы обычной SplitString и SplitStringCLR я вызвал эти функции 1000 раз с входной строкой, состоящей из 100 разделенных запятой чисел.

    Среднее значение времени работы для SplitString получилось 6.152 мс, а для SplitStringCLR 1.936 мс.

    Разница более чем в 3 раза.

    Надеюсь, это будет кому-нибудь полезно.
    Share post

    Similar posts

    AdBlock has stolen the banner, but banners are not teeth — they will be back

    More
    Ads

    Comments 19

      +3
      Такое соотношение далеко не всегда справедливо, и лучше все же использовать T-SQL.
      For classic SQL tasks, the good old TSQL is recommended. On the other hand, CLR works best for calculations, parsing, image processing and other tasks that deal with a very limited amount of data.
        0
        Согласен, всему есть свое применение.
          +2
          Конечно. Я только хочу подчеркнуть, что использование CRL в SQL Server'e скорее исключение, чем правило. И этой возможностью нужно пользоваться осмотрительно.
            +2
            еще очень полезно бывает использовать CLR в SQL Server'е, когда пишешь нечто похожее на бизнес-логику в реализации сервиса в Service Broker'е.
              +1
              Да вот написание бизнес-логики в SQL Server'е само по себе не полезно :)
                0
                Все зависит:
                1) от того, что вы считаете бизнес-логикой
                2) от требований: иногда (например, из-за требований к безопасности) часть логики (например, валидацию) приходится размещать на SQL-сервере (другой пример: чтобы в зависимости от доменной роли прикрывать доступ к пользовательским объектам напрямую Kerberos'ом — это принимается в госструктурах, например, как единственная удовлетворительная модель безопасности) — в таком случае «CLR спешит на помощь»
                  0
                  Привяжусь опять к пресловутому Service Broker'у.
                  Представим, что у меня есть несложная бизнес-логика. Есть две разделенные на разные физические машины компоненты, которые обмениваются сообщениями. Получение и отправка сообщений инициируют некоторые изменения в бд. Также представим, что нам хочется иметь распределенную транзакцию, в которую входит отправка сообщения, изменение на стороне сервиса, изменение на стороне клиента.
                  Распространение таких транзакций на инстанс SQL-сервера и на транспорт достаточно ресурсоемки. А если и оправка сообщений, и бизнес-логика, и изменения в бд будут обрабатываться только SQL-сервером, то это, имхо, есть хорошо.
                  Не очень загнул? :-[
          0
          Я думаю стоит рассмотреть все стороны технологии, например:
          1. Проблемы/сложности при переносе/бекапе данных.
          2. Что с безопасностью.
          3. Особенности написания модулей CLR в VS.
            +2
            1. Сборка загружается на сервер и хранится там. Функции, которые ссылаются на сборку уже хранятся в базе. Поэтому нужно, чтобы на сервере, куда переносится база, сборка была загружена.

            2. При создании сборки, если нужно, указывается аргумент PERMISSION_SET, который определяет разрешения для сборки. Советую посмотреть MSDN. Вкратце: SAFE — разрешает работать только с базой; EXTERNAL_ACCESS — разрешает работать с другими серверами, файловой системой и сетевыми ресурсами; UNSAFE — все что угодно, включая WinAPI.

            3. Есть особенности при отладке, какие именно, указано в MSDN.
              +1
              А закиньте это прямо в пост :)
              0
              1000 за и 1000 раз против. Всё относительно чего то, я думаю вместе со статьями по оптимизации необходимо уточнять в каком месте лучше использовать одного а в каком месте другое.
                +1
                Кто-нибудь пробовал применять CLR для построения деревьев (рекурсивно)? Есть смысл?
                  0
                  Когда использовал столкнулся с двумя проблемами.
                  Первая была связана с «утечками памяти» (до сих пор не могу понять откуда она взялась в .NET). Нужны были регулярные выражения. Хранимая процедура делала обработку большого количества данных и в ней постоянно использовалась пользовательская функция. Во время работы ОП постепенно съедалась и в итоге все заканчивалось ошибкой. Если процедура успевала сделать все что нужно, память потом освобождалась. Так что для единовременной обработки больших объемов данных пользовательские функции не подходят.

                  Вторая проблема выявилась после создания агрегирующей функции. В SQL Server нет функций агрегации строк, но их можно написать самому. И опять при работе с большим количеством данных надежды не оправдались. Агрегирующая функция (мне нужна была функция сцепления строк) очень сильно тормозила, с ней SELECT выполняется в несколько раз дольше. К тому же в SQL Server 2005 итоговая строка не может быть больше 8000 байт, но это вроде как исправили в 2008-ой и там теперь можно работать с NVARCHAR(MAX).

                  В общем эту технологию лучше использовать только когда вам совсем без этого не обойтись и с небольшим количеством данных.
                    +1
                    По первой проблеме — может ты каждый раз создавал новый объект Regex? В реализации регулярных выражений в .net используется динамическая компиляция. И возможно там были проблемы с вычищением сгенерированного кода из памяти. В общем нужно либо один раз создавать регэкспы и запоминать в статические поля, либо юзать статический метод Regex.Match() — у него есть встроенный кэш.

                    По второй проблеме — ты ведь через StringBuilder делал и через "+" строки не складывал, да? :)
                      0
                      Спасибо конечно за подсказки, но я использовал Regex.Match() и StringBuilder.
                      И я буду очень рад, если вы мне поясните какие такие проблемы могли бы возникнуть если бы я каждый раз создавал новый объект Regex?
                        0
                        Вот что нагуглил:
                        connect.microsoft.com/VisualStudio/feedback/details/88545/regex-memory-leak

                        Может быть ты с этим столкнулся?
                          +1
                          Если кому-то еще интересно.
                          Посмотрите год сообщения, котрое вы нагуглили. На сервере ставятся все возможные обновления, так что если фикс вышел (а с 2004го много времени прошло как бы) он стоит.
                          Я гуглил по ошибке, которая выскакивала, и максимум что нашел, это вот такие древние посты в которых предлагалось поставить патч, по-моему на SQL Server 2005 SP1, у меня же установлен SP3 и соответственно этот патч тоже уже стоит.

                          А вот про агрегирующую функцию вы во многом правы. Чтобы строка не вылазила за пределы 4000х тысяч символов перед каждым «склеиванием» мне нужно было проверять длину строки которая получится и ничего умнее вот такого
                          private StringBuilder builder;

                          public void Accumulate(SqlString value)//
                          {
                          if (builder.ToString().Length + value.Value.Length < 4000)
                          builder.Append(value.Value);
                          }
                          я не придумал
                      0
                      По работе приходится хранить в базе сканы документов для архива. Раньше пользовался самописной утилитой для компрессии jpeg'ов внутри БД до минимально читаемого на глаз уровня. Спасибо автору статьи — переписал утилиту под SQL CLR и навесил триггер для автокомпрессии загружаемых в БД картинок. Так вот столкнулся с той же проблеммой утечки памяти, вываливалось в ошибку примерно на 10-15 изображении и не работало до отключения-подключения сборки к БД. Решил проблемму принудительным вызовом GC.Collect(); в коде функции. 12000 картинок пожалось за 4 минуты без ошибок.
                      +1
                      SQLCLR вообще приятственная штука. нужно было возвращать данные в JSONе, которые до этого возвращались в XMLе (xml генерится средствами t-sql в 2005). Пишем на c# сторед который конвертит XML в JSON и возвращаем как text — минимум усилий, скорость удовлетворительная, жизнь прекрасна :)

                      Only users with full accounts can post comments. Log in, please.