Pull to refresh

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

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

Внутри описание (плюсы\минусы) методов передачи, таблица и график сравнения. Рассматриваются следующие методы передачи параметра:
  • 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 неэффективен в виду своих минусов и по результатам теста. Даже несмотря на то, что в этом примере подход со строкой можно оптимизировать, он и остальные методы неэффективны.
Tags:
Hubs:
+18
Comments 17
Comments Comments 17

Articles