Тесты методов передачи списковых переменных в хранимую процедуру MS SQL 2008

    По следам этого поста. Надеюсь, автор не будет иметь ничего против того, чтобы я расширил его умозаключения.

    Внутри описание (плюсы\минусы) методов передачи, таблица и график сравнения. Рассматриваются следующие методы передачи параметра:
    • Xml (Openxml)
    • Xml (Xquery)
    • Строка
    • Блоб
    • Промежуточная таблица
    • Табличный тип


    Для тех кому лень читать, вывод и результирующий график внизу.

    Тест


    По условиям теста требуется передать список каким-то методом в хранимую процедуру, где следует преобразовать его в пригодную для использования t-sql структуру данных (временная таблица). Краевые условия максимально упрощаются:
    1. Хранимая процедура только преобразовывает список (никакой прикладной логики)
    2. Список не пуст
    3. Все элементы списка имеют одинаковую длину и тип


    В качестве типа данных был взят Guid (uniqueidentifier). Тестовыми данными являются списки (массивы гуидов) длинами в 1, 10, 50, 100, 1000, 5000, 10000, 50000, 100000, 250000, 500000, 1000000. Результатом теста является время, затраченное на исполнение хранимой процедуры, функционала формирования управляющих объектов ADO.NET и преобразования списка в подходящий для передачи в процедуру вид. Результат первого массива (длиной 1) отбрасывается в качестве прогоночного.

    Описание методов



    Xml (Openxml)

    Массив преобразуется в xml структуру и передается в хранимую процедуру как xml тип данных. Внутри хранимой процедуры делается вставка во временную таблицу из функции openxml:

    declare @idoc int
    exec sp_xml_preparedocument @idoc OUTPUT, @xml

    insert into @table(id)
    select value
    from openxml(@idoc, '/list/id', 1) with (value uniqueidentifier)


    Плюсы:
    • Возможность каста к нужному виду данных непосредственно в хранимой процедуре.
    • Гибкость в запросах к параметру, т.е. присутствует возможность передать данные, связанные через иерархию элементов xml (со всеми вытекающими)

    Минусы:
    • Избыточность данных в виде хмль-тегов


    Xml (XQuery)

    Все то же самое, что и Xml (Openxml), однако структура данных разворачивается в tsql типы через использование xquery:

    declare @t table(id uniqueidentifier)
    insert into @t(id)
    select list.Id.value('@val','nvarchar(255)') from @xml.nodes('/list/id[@val]') as list(Id)


    Минусы и плюсы такие же, как и в предыдущем методе. Следует заметить, что программист должен обладать некоторыми навыками построения запросов XPath\XQuery.

    Строка

    «Классический» способ передачи данных через их склейку в строку с каким-то разделителем:

    -- не будет работать, если массив пустой
    declare @next int = 1;
    while @next > 0
    begin
    if (@next > 1) set @next = @next + 1; -- корректная отработка входа в цикл
    insert into @table(id) values(cast(substring(@data, @next, @elementLength) as uniqueidentifier));
    set @next = CHARINDEX(@delimiter, @data, @next + 1); -- todo: чегоб просто @elementLength не прибавить?
    end


    Плюсы:
    • Самый легкий в плане имплементирования алгоритм
    • Разнообразное количество подходов к разработке

    Минусы:
    • Классика жанра: «Он передает списки в хранимую процедуру через строку?!?! Фу!!! Ламер!»
    • Поэлементный обход данных
    • Больший объем передаваемых данных


    Блоб

    То же самое, что строка, но вместо строки элемент кодируется последовательностью байтов. Байты объединяются во множество и передаются в хранимую процедуру как varbinary(max):

    while @cnt < @len
    begin
    insert into @table(id) values(CAST(SUBSTRING(@data, @cnt, @elementLen) as uniqueidentifier))
    set @cnt = @cnt + @elementLen;
    end


    Плюсы:
    • По сравнению со строкой меньший объем передаваемых данных
    • Более быстрое преобразование данных

    Минусы:
    • Сложность в имплементации


    Промежуточная таблица

    В БД создается промежуточная таблица table(setId, id), в которую через SqlBulkCopy вставляется массив элементов и его идентификатор (setId).

    DataTable data = new DataTable();
    data.Columns.Add("SetId", typeof(Guid));
    data.Columns.Add("Id", typeof(Guid));

    Guid setId = Guid.NewGuid();
    foreach (Guid x in _array)
    {
    var row = data.NewRow();
    row["SetId"] = setId;
    row["Id"] = x;
    data.Rows.Add(row);
    }

    using (SqlBulkCopy bulk = new SqlBulkCopy(Settings.Default.ConnectionString, SqlBulkCopyOptions.CheckConstraints))
    {
    bulk.BulkCopyTimeout = 60000;
    bulk.DestinationTableName = "dbo.bulktable";
    bulk.WriteToServer(data);
    }


    По идентификатору setId из промежуточной таблицы «вытягивается» нужный список:

    insert into @table
    select Id
    from dbo.BulkTable
    where SetId = @setId;


    Плюсы:
    • Bulk операции очень быстрые
    • «Простота» хранимой процедуры

    Минусы:
    • Все минусы глобальной переменной в виде промежуточной таблицы
    • Метод будет тормозить по мере заполнения таблицы параллельными запросами
    • Необходимость поддержки и обслуживания индекса для быстроты поиска
    • Два действия вместо одного: раз — записываем в промежуточную, два — вызываем хранимку. Промежуток между этими действиями не контролируется и может послужить источником проблем (см. concurrency, etc)


    Табличный тип

    В БД объявляется user defined type в виде таблицы с нужным типом данных. Его можно передавать в хранимую процедуру через объект System.Data.DataTable. В хранимой процедуре этот тип будет виден как t-sql таблица, к которой можно делать запросы.

    CREATE TYPE [dbo].[guidlist] AS TABLE([id] [uniqueidentifier] NULL)
    CREATE procedure [dbo].[PassTableParam]
    @data guidlist readonly
    as
    begin
    set nocount on;
    declare @table table(id uniqueidentifier)
    insert into @table(id)
    select id
    from @data
    end


    Плюсы:
    • Самый простой способ передать параметр в хранимую процедуру

    Минусы:
    • Работает только начиная с версии MS SQL 2008


    Результаты теста



    Выполнялось на двухядерном Xeon и 8гб оперативы.

    В виде таблицы (в первой колонке длина списка, в остальных время исполнения хранимой процедуры в миллисекундах). Оценивается выигрыш по времени исполнения метода относительно времени исполнения других методов для заданного списка.

    Легенда:
    • Xml — Openxml
    • Xml — Xquery
    • string — Строка
    • binary — Блоб
    • bulk — Промежуточная таблица
    • table — Табличный тип


    count
    binary
    bulk
    openxml
    string
    table
    xquery
    10
    1
    5
    2
    1
    1
    1
    50
    2
    5
    2
    3
    1
    1
    100
    4
    6
    5
    5
    2
    2
    1000
    30
    24
    46
    44
    65
    16
    5000
    144
    85
    228
    216
    35
    78
    10000
    289
    167
    476
    633
    67
    165
    50000
    1928
    727
    2482
    3170
    398
    698
    100000
    3886
    1630
    4803
    6388
    891
    1467
    250000
    9893
    3757
    12074
    16649
    2082
    3698
    500000
    19357
    8427
    24265
    33398
    4460
    7099
    1000000
    38838
    20832
    53508
    67825
    9040
    14444


    В виде графика:
    image

    Выводы


    Если у вас MS SQL 2008, то списки следует передавать в виде табличных переменных. Если версия сервера меньше, чем 2008, то следует использовать подход xml\xquery.

    Метод промежуточной таблицы и bulk неэффективен в виду своих минусов и по результатам теста. Даже несмотря на то, что в этом примере подход со строкой можно оптимизировать, он и остальные методы неэффективны.
    Ads
    AdBlock has stolen the banner, but banners are not teeth — they will be back

    More

    Comments 17

      0
      А нельзя отдельно представить только начало графика?
      Сейчас можно судить только о 2й половине тестов, т.к. 1-ая на графиках не проявилась.
        +1
        спасибо за обзор. сам недавно столкнулся с такой задачей, но проводить сравнение методов не было времени
          0
          Спасибо, отлично! А в процедуре происходило какое-нибудь действие? Ведь это тоже важно как обработать потом параметры.
            0
            ну да, но если учесть относительность тестов друг друга, то этот функционал можно вынести за скобки и сократить. тестируется лишь адекватный переход в t-sql, то бишь параметр преобразуется в список и заполняет временную переменную.
              +1
              Смотрите о чем я. Когда используется SqlBulkCopy или table-valued parameters — то у вас уже на входе в процедуру есть таблица, с которой можно работать, когда varbinary или xml — вам еще нужно создать эту таблицу (или не нужно создавать, а сразу использовать что есть). То есть в тестах SqlBulkCopy (.net app -> #table -> @table -> процедура) сравнивался с xml (.net app -> xml -> @table -> процедура), так? Хотя может приведение к @table — это уже окончательная операция. Интереснее, наверное, было бы что-то вроде «insert into @table (id) select t1.id from @temptable t1 left join @table t2 on t1.id = t2.id where t2.id is null» (то есть вставка значений, которых еще не было), ну и это само собой нужно бы сделать несколько раз и взять среднее. Тут мне кажется уже будут другие результаты, хотя нужно пробовать.
                0
                вы правы в том, что в случаях с балккопи и табличным типом у меня нет необходимости создавать временную переменную, можно использовать уже пришедший параметр.

                если следовать вашей логике и создавать прикладной функционал в тестируемых хранимых процедурах, то получается, что сервер в любом случае будет делать index seek при подстановке в join \ select in. стало быть если откинуть этот функционал, то «лишними» действиями относительно балккопи\табличный тип является выделение памяти.

                значит, если сделать в балкопи \ табличном типе такое же выделение памяти, как и в других методах, то тесты подводятся к общему знаменателю и прикладным функционалом тестируемых хранимых процедур можно пренебречь.
                  0
                  Это теория, интересна практика. В общем, спасибо, а то что у table стоит 65 при 1000 — действительно странно — видимо звезды… Нужны средние.
            0
            У вас в таблице нет опечатки?
            По данным в таблице получается, что для 1000 элементов table передача самая медленная ( со значением 65).
            По логике больше похоже на 6.5.
              0
              опечатки нет. наводка, может?:) по хорошему, нужно прогнать эти тесты раз 100 на своих выборках, потом посчитать среднее. но все же, не думаю, что картина будет кардинально отличаться.
                0
                Если нет, то график не верный.
                Для 1000 элементов, табличный метод самый медленный.
                  0
                  Посмотрите внимательнее, там точно опечатка.
                +2
                А за статью больше спасибо. Этот вопрос встает у многих, если не у каждого разработчика.
                  +1
                  Вопрос актуальный, особенно для начинающих разработчиков, спасибо.
                    +1
                    Очень познавательно и интересно. Еще хотелось бы увидеть на сколько медленней работает тривиальный insert в цикле?

                    И еще хочу добавить один минус table метода — хотя по перфомансу и удобству он лучше остальных но к сожалению драйвер для него есть только под .Net.
                      0
                      За чистый инсерт не скажу, но многократный вызов хранимки, инсертящий по одной записи за раз заставил нас как то уйти курить на пол часа при 400к строк)
                        0
                        Ну так транзакции, ничего удивительного. Банальная внешня транзакция ускорила бы процесс радикально.
                    • UFO just landed and posted this here

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