この投稿をきっかけに。 著者が結論を拡大することに私に反対するものは何もないことを願っています。
内部には、伝送方法の説明（賛否両論）、表、比較表があります。 パラメーターを渡すための次のメソッドが考慮されます。
- Xml（Openxml）
- Xml（Xquery）
- ひも
- ブロブ
- 中間テーブル
- テーブルタイプ
読むのが面倒な人のために、結論と下のグラフをご覧ください。
テスト
テスト条件に従って、何らかの方法でリストをストアドプロシージャに転送し、そこで使用可能なt-sqlデータ構造（一時テーブル）に変換する必要があります。 境界条件は可能な限り簡素化されます。
- ストアドプロシージャはリストのみを変換します（アプリケーションロジックはなし）
- リストは空ではありません
- すべてのリストアイテムの長さとタイプは同じです。
GUID（一意識別子）がデータ型として使用されました。 テストデータは、長さが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）と同じですが、データ構造はxqueryを使用してtsql型で拡張されます。
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
長所：
- 文字列と比較して、データ転送が少ない
- より高速なデータ変換
短所：
中間テーブル
中間テーブルテーブル（setId、id）がデータベースに作成され、そこに要素の配列とその識別子（setId）がSqlBulkCopyを介して挿入されます。
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;長所：
- 一括操作は非常に高速です
- ストアドプロシージャのシンプルさ
短所：
- 中間テーブルとしてのグローバル変数のすべての短所
- テーブルが並列クエリでいっぱいになると、メソッドの速度が低下します
- クイック検索のためにインデックスのサポートとメンテナンスが必要
- 1つではなく2つのアクション：1回-中間に書き込み、2回-ストアを呼び出します。 これらのアクション間のギャップは制御されておらず、問題の原因となる可能性があります（並行性などを参照）
テーブルタイプ
ユーザー定義型は、目的のデータ型を持つテーブルの形式でデータベースで宣言されます。 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
長所：
- ストアドプロシージャにパラメーターを渡す最も簡単な方法
短所：
試験結果
デュアルコアXeonと8GBのRAMで実行されました。
テーブル形式（最初の列はリストの長さ、残りはミリ秒単位のストアドプロシージャの実行時間）。 特定のリストの他のメソッドの実行時間に対するメソッドの実行時間の増加が推定されます。
凡例：
- Xml-Openxml
- Xml-Xquery
- 文字列-文字列
- バイナリ-Blob
- bulk-中間テーブル
- テーブル-テーブルタイプ
数える
| バイナリ
| バルク
| openxml
| ひも
| テーブル
| 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
|
10,000
| 289
| 167
| 476
| 633
| 67
| 165
|
50,000
| 1928
| 727
| 2482
| 3170
| 398
| 698
|
100,000
| 3886
| 1630
| 4803
| 6388
| 891
| 1467
|
250000
| 9893
| 3757
| 12074
| 16649
| 2082
| 3698
|
500,000
| 19357
| 8427
| 24265
| 33398
| 4460
| 7099
|
1,000,000
| 38838
| 20832
| 53508
| 67825
| 9040
| 14444
|
グラフの形式で：
結論
MS SQL 2008を使用している場合、リストはテーブル変数の形式で渡す必要があります。 サーバーのバージョンが2008未満の場合は、xml \ xqueryアプローチを使用する必要があります。
中間テーブルとバルクのメソッドは、マイナスの点とテスト結果によると非効率的です。 この例では文字列アプローチを最適化できますが、それと他の方法は非効率的です。