Тесты методов передачи списковых переменных в хранимую процедуру 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

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

Выводы

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

Метод промежуточной таблицы и bulk неэффективен в виду своих минусов и по результатам теста. Даже несмотря на то, что в этом примере подход со строкой можно оптимизировать, он и остальные методы неэффективны.