SQL Serverで複合キーレコードをテーブル変数を使って一括処理する

前提

以下のようなテーブルがあるとする。

CREATE TABLE 受注 (
  受注日 date NOT NULL,
  得意先コード char(8) NOT NULL,
  枝番 tinyint NOT NULL,
  -- 略 --
  PRIMARY KEY(受注日, 得意先コード, 枝番)
);

CREATE TABLE 受注明細 (
  受注日 date NOT NULL,
  得意先コード char(8) NOT NULL,
  枝番 tinyint NOT NULL,
  商品コード char(8) NOT NULL,
  数量 int NOT NULL,
  -- 略 --
  PRIMARY KEY(受注日, 得意先コード, 枝番, 商品コード)
);

賢明なエンジニアなら「サロゲートキー用意しろや」と思うだろうが、残念ながら世の中そんな賢明エンジニアだけではない。
既存のシステムの保守であったり、新規案件であったとしても参画が中途からであったりすると既に手遅れ状態になっていたりと上記のような設計に遭遇することがある。

このような作りになっている場合、複数の受注をまとめて処理する場合に1件ずつ処理(SQLを発行)して性能面で問題が発生することがある。
現在のコンピューティング環境であれば数十件レベルでは問題にならないであろうが、数百件単位での処理になるとさすがに目に見えてくるところが出てくることがあるのではないだろうか。

解決方法

解決方法1: ANDとORで指定

一番単純な解決方法であるが、条件件数が増えるとWHERE句の条件が多くなってしまいパフォーマンスが落ちる場合が出てくる。

WHERE
  (受注日 = '2023-1-1' AND 得意先コード = 'XXXXXXXX' AND 枝番 = 1)
  OR (受注日 = '2023-1-1' AND 得意先コード = 'XXXXXXXX' AND 枝番 = 2)
  ...

解決方法2: IN句で指定

SQL Serverではできないが、IN句で複合キーをタプルのように指定可能なRDBMSも存在する。

WHERE (受注日, 得意先コード, 枝番) IN (('2023-1-1', 'XXXXXXXX', 1), ('2023-1-1', 'XXXXXXXX', 2), ...)

※サロゲートキーが存在していればSQL ServerでもIN句でまとめて指定できる。

解決方法3: テーブル変数を使ってJOINする(今回のテーマ)

SQL Serverにはテーブル変数という機能がある。
テーブル変数にキー情報を格納して、テーブル変数と対象テーブルを結合して処理する手段である。
具体的なSQLは以下の通りとなる(受注明細テーブルから複数の受注の明細を取得する)。

BEGIN
  -- テーブル変数の定義
  DECLARE @keys TABLE(受注日 date, 得意先コード char(8), 枝番 tinyint);
　-- テーブル変数へキー情報を追加
  INSERT INTO @keys VALUES ('2023-1-1', 'XXXXXXXX', 1), ('2023-1-1', 'XXXXXXXX', 2), ...;
  -- JOINして抽出
  SELECT * FROM 受注明細 D INNER JOIN @keys K ON D.受注日 = K.受注日 AND D.得意先コード = K.得意先コード AND D.枝番 = K.枝番;
END

実装サンプル(C#)

抽出の実装をサンプルコードにしてみた。

// 抽出対象のキー
var keys = new List<(DateTime 受注日, string 得意先コード, byte 枝番)>()
{
    (new(2022, 1, 1), "XXXXXXXX", 0),
    (new(2022, 2, 1), "YYYYYYYY", 1),
    (new(2022, 3, 1), "ZZZZZZZZ", 2),
};

using var connection = new SqlConnection(new SqlConnectionStringBuilder() { /* 略 */ }.ToString());
connection.Open();

using var command = connection.CreateCommand();
// VALUESの中は名前付きパラメータで指定
command.CommandText = $@"BEGIN
    DECLARE @keys TABLE(受注日 date, 得意先コード char(8), 枝番 tinyint);
    INSERT INTO @keys VALUES {string.Join(", ", keys.Select((k, i) => $"(@d_{i}, @c_{i}, @b_{i})"))};
    SELECT * FROM 受注明細 D INNER JOIN @keys K ON D.受注日 = K.受注日 AND D.得意先コード = K.得意先コード AND D.枝番 = K.枝番;
END";
command.Parameters.AddRange(
    keys.Select((k, i) => new SqlParameter[]
    {
        new() { ParameterName = $"@d_{i}", SqlDbType = SqlDbType.Date, Value = k.受注日 },
        new() { ParameterName = $"@c_{i}", SqlDbType = SqlDbType.Char, Value = k.得意先コード },
        new() { ParameterName = $"@b_{i}", SqlDbType = SqlDbType.TinyInt, Value = k.枝番 }
    })
    .SelectMany(e => e)
    .ToArray());

using var reader = command.ExecuteReader()
{
  // 略
}

注意点

• 一括処理すると一度に処理するデータも増えるのでコマンドタイムアウトになる可能性も。
• SQL Serverの1ステートメントあたりのパラメータ数の上限は2100個までである(WHEREでもINでも同じ)。
  キー項目が3つであれば700件まで一括取得できそうに見えるが、テーブル変数もパラメータ数にカウントされるらしく700件で実行するとSQLエラーになる。
  上述のコマンドタイムアウトも踏まえて適切な件数で分割した方が良い。
• 必ず速くなるわけではない。
  単純なテーブル抽出であればオーバーヘッドが少なくなる分の高速化は見込めるが、ビューで演算をしている場合などは1件ずつ取得した方が速い場合もあるので必ず効果を確認してください。
• あくまで苦し紛れの手法なのでテーブル設計が変えられるのであればそちらで対応した方が良い。