• 戻る
  • データアクセスのいろいろ
  • 大量データの処理方式（1、3）

ADO.NET のデータプロバイダを使用する .NET プログラムでは、
大量データを処理するバッチ開発に適合しないケースがある。

• ADO.NET のデータプロバイダでは以下の機能がサポートされていないケースがある。
  ＃ JDBC などのデータプロバイダには標準で実装されている機能

  • フェッチ・サイズ指定（フェッチ）
  • 配列バインド（バッチ更新）

• Oracle、HiRDB のデータプロバイダについてはサポートされているものもある。

  • ODP.NET
    • フェッチ・サイズ指定（フェッチ）
    • 配列バインド（バッチ更新）
  • HiRDB.NET
    • 配列バインド（バッチ更新）

補足（この指摘は現在も概ね正しい）: 「ADO.NET には
フェッチ サイズ指定と配列バインドがない」——
この診断は本ページの出発点であり、現在も概ね当てはまる。

【フェッチ サイズ（配列フェッチ）】
   サーバから【何行まとめて取ってくるか】
     JDBC:    stmt.setFetchSize(1000)
     ODP.NET: cmd.FetchSize = 1024 * 1024（バイト単位）★
     SqlClient: 【指定できない】
       → SQL Server は TDS のパケット単位で自動的にまとめて送る
       → 「指定できないが、実質的に問題にならない」

【配列バインド（バッチ更新）】
   1 回の往復で【複数行を更新する】
     JDBC:    addBatch() / executeBatch()
     ODP.NET: cmd.ArrayBindCount = 1000 ★
     SqlClient: 【ない】
       → 代替は SqlBulkCopy / テーブル値パラメータ（後述）

なぜこの差が生まれたのか:

・ADO.NET は【DataSet / DataAdapter を中心】に設計された
   → 「取ってきて、メモリで加工して、書き戻す」モデル
   → 【行単位のループ】が前提ではなかった
・JDBC は【カーソルとバッチ】を中心に設計された
   → 大量データのバッチ処理が想定内

 → 設計思想の違いであり、優劣ではない
 → ただし【バッチ処理では JDBC 型の方が素直】★

移行メモ（HiRDB.NET / ODP.NET の現況）:

・ODP.NET は【Managed / Core 版】があり、.NET 8 等に対応
   → Oracle.ManagedDataAccess.Core
   → ArrayBindCount は現在も利用可能 ★
・HiRDB の .NET プロバイダも継続提供されている

DB によるが、INSERT、UPDATE などの SQL を連続して記述することは可能。

• 以下は、SQL Server の例

INSERT INTO XXXX(xxx, yyy, zzz) VALUES(xxx, yyy, zzz);
INSERT INTO XXXX(xxx, yyy, zzz) VALUES(xxx, yyy, zzz);
INSERT INTO XXXX(xxx, yyy, zzz) VALUES(xxx, yyy, zzz);
・・・ 

UPDATE SET xxx=xxx, yyy=yyy, zzz=zzz, WHERE id = 1;
UPDATE SET xxx=xxx, yyy=yyy, zzz=zzz, WHERE id = 2;
UPDATE SET xxx=xxx, yyy=yyy, zzz=zzz, WHERE id = 3;
・・・ 

• ただし、バインド変数の数に制約があるため、
  パラメタライズド・クエリでの「パラメタ」を設定し難い。

補足（原文の指摘は的確。制約の数値を示す）: 「バインド変数の数に制約が
あるため、パラメタを設定し難い」——これが決定的な制約である。

【SQL Server のパラメータ数の上限】
   ストアド プロシージャ: 2,100
   【1 バッチのパラメータ: 2,100】★

   → 1 行 10 列なら【210 行が上限】
   → 「1 万件を一括で」は不可能

【Oracle】
   バインド変数の上限は 65,535（実質的には別の制約が先に来る）

【パラメータ化しないと SQL インジェクション】★
   値を文字列連結で埋め込めばパラメータ数の制約は回避できるが、
   【絶対にやってはならない】
     → SQL インジェクション
     → 実行プランのキャッシュが効かない（プラン キャッシュの汚染）
       → [SQL Serverの実行プラン](MS_SQLExecutionPlan) 参照

移行メモ（サンプルの SQL 構文）: UPDATE 文が
UPDATE SET ... となっており、テーブル名が欠けている
（正しくは UPDATE XXXX SET ...）。
また、SET 句の末尾に余分なカンマがある。
疑似コードとしての記載と読めるが、注記しておく。

• SQL Server 2005 からサポートされた機能。

• .NET では SQL CLR と言う機構も用意されているが、

  • インプロセスで動作するものの
  • カーソル操作をサポートしない

  ため、バッチ処理の用途では、あまり魅力的なものでは無い。

• また、同様にサポートされているデータプロバイダが限られる。

  • SQL Server（SQL CLR）
  • ODP.NET（.NET ストアド・プロシージャ）

（後述の CLR ストアド プロシージャ を参照）

補足（SQL CLR の現況 ── 原文の判断は正しかった）: 「あまり魅力的な
ものでは無い」という原文の評価は、その後の経緯からも妥当だった。

【SQL CLR の現況】
   ・SQL Server では【今も使える】（オンプレミス）
   ・【Azure SQL Database では使えない】★
      → クラウド移行の障害になる
   ・SQL Server 2017 以降、既定で【CLR strict security】が有効
      → 【アセンブリに署名が必要】になり、導入の手間が増えた
   ・採用例は原文の時代からさらに減っている

【SQL CLR が今も有効な用途】
   ・正規表現（T-SQL には貧弱なパターン マッチしかない）
   ・複雑な文字列処理・独自の集計関数
   ・[文字のチェック方式](MS_CharacterValidation) のような文字コード判定
     → いずれも【行単位の変換】であり、カーソル操作は不要

【向かない用途】
   ・バッチ処理そのもの（原文の指摘の通り）
   ・外部リソースへのアクセス（ファイル、HTTP）
      → UNSAFE アセンブリが必要。DB の安定性を損なう ★

• INSERT で複数の値を指定することが可能となった（UPDATE は非対応）。

• 同様に、バインド変数の数に制約があるため、
  パラメタライズド・クエリでの「パラメタ」を設定し難い。

• 参考

  • INSERT ステートメントで複数の値を指定 - 松本崇博 Blog （SQL Server Tips）
    https://d.hatena.ne.jp/matu_tak/20100113/1263856222

補足（行コンストラクタの上限）: INSERT ... VALUES (…),(…),(…) は
1 文あたり 1,000 行が上限である。
パラメータ数の 2,100 制約と合わせると、
実用的には数百行が限度になる。

【UPDATE の複数行対応】
   原文の「UPDATE は非対応」は正しいが、
   【MERGE 文】または【FROM 句付き UPDATE】で
   複数行をまとめて更新できる ★

-- テーブル値パラメータ + UPDATE ... FROM（現在の定石）
UPDATE t
   SET t.name = s.name, t.price = s.price
  FROM 商品 AS t
  JOIN @tvp AS s ON t.id = s.id;

• SQL Server 2008 から、テーブル値パラメタというパラメタを設定可能になっている。

• テーブル値パラメタは、以下の型として、プログラム側から指定できる。

  • .NET

    • DataTable
    • DbDataReader
    • IEnumerable<T>

  • SQL CLR

    • SqlDataRecord

• 参考

  • SQL Server 2008 のテーブル値パラメータ (ADO.NET)
    https://learn.microsoft.com/ja-jp/dotnet/framework/data/adonet/sql/table-valued-parameters

  • SQL ServerにおけるTable-Valuedパラメータ
    https://www.infoq.com/jp/news/2008/08/table-valued-param

  • Microsoft Docs

    • テーブル値パラメーター
      https://learn.microsoft.com/ja-jp/dotnet/framework/data/adonet/sql/table-valued-parameters
    • テーブル値パラメーターの使用 (データベース エンジン)
      https://learn.microsoft.com/ja-jp/sql/relational-databases/tables/use-table-valued-parameters-database-engine

補足（テーブル値パラメータが、原文の問題への解になる）: 本ページの
「パラメータ数の制約でバッチ更新ができない」という課題に対し、
テーブル値パラメータ（TVP）が正面から答える。

-- ① ユーザー定義テーブル型を作る（1 回だけ）
CREATE TYPE dbo.OrderTvp AS TABLE (
    Id    INT           NOT NULL PRIMARY KEY,
    Name  NVARCHAR(50)  NOT NULL,
    Price DECIMAL(18,2) NOT NULL
);

// ② .NET から渡す（パラメータは【1 個】で済む）★
var p = cmd.Parameters.AddWithValue("@tvp", table);   // DataTable
p.SqlDbType = SqlDbType.Structured;
p.TypeName  = "dbo.OrderTvp";

-- ③ ストアド側で【集合として】扱える
INSERT INTO 注文 (Id, Name, Price)
SELECT Id, Name, Price FROM @tvp;

-- 更新も可能（原文の「UPDATE は非対応」への回答）★
UPDATE t SET t.Price = s.Price FROM 注文 t JOIN @tvp s ON t.Id = s.Id;

-- MERGE で INSERT/UPDATE/DELETE を一度に
MERGE 注文 AS t USING @tvp AS s ON t.Id = s.Id
WHEN MATCHED THEN UPDATE SET t.Price = s.Price
WHEN NOT MATCHED THEN INSERT (Id, Name, Price) VALUES (s.Id, s.Name, s.Price);

【TVP の利点】
   ・【パラメータ数の制約を回避できる】★
   ・型付き（列の型が定義される）
   ・実行プランが再利用される
   ・SQL インジェクションの余地がない
   ・【集合演算として書ける】＝ 行ループが消える

【TVP の注意点】
   ・読み取り専用（@tvp を UPDATE できない）
   ・統計情報を持たないため、【行数の見積りが 1 行固定】になる
      → 大量データではプランが悪化しうる
      → OPTION (RECOMPILE) を検討する
   ・数万行を超えるなら【SqlBulkCopy】の方が速い ★

IEnumerable<SqlDataRecord> を使うとメモリを節約できる。

// DataTable と違い、【全件をメモリに載せずに】ストリームで渡せる ★
static IEnumerable<SqlDataRecord> ToRecords(IEnumerable<Order> orders)
{
    var meta = new[] {
        new SqlMetaData("Id", SqlDbType.Int),
        new SqlMetaData("Name", SqlDbType.NVarChar, 50),
    };
    var rec = new SqlDataRecord(meta);
    foreach (var o in orders)
    {
        rec.SetInt32(0, o.Id);
        rec.SetString(1, o.Name);
        yield return rec;          // ← 同じインスタンスを使い回す
    }
}

「フェッチ機能の代替」処理方式の検証

• SQL Server のデータプロバイダを使用。

• フェッチ機能の代替

  • フェッチ機能を代替するために、はじめに主キー・セットだけを取得し、
    コミット・インターバル分、IN 句に主キーを指定して結果セットを分割取得
    することでフェッチ機能の代替とする方式もある。

  • ただし、この際の検索処理性能を考慮すると、
    参照元テーブルにインデックスが貼られている必要があるなど、
    本処理方式（疑似フェッチ方式）を採用する上での制約もある。

補足（この「疑似フェッチ方式」の評価）: 実務的な工夫だが、
現在はより素直な方法があるので併記しておく。

【疑似フェッチ方式（原文）】
   ① 主キーだけを全件取得
   ② コミット単位（例：1000 件）ずつ IN 句で本体を取得
   ③ 処理してコミット

 【利点】
   ・コミット単位が明確（途中で失敗しても再開しやすい）★
   ・接続を長時間保持しない
 【欠点】
   ・主キーの全件取得が必要（件数が多いとメモリを食う）
   ・IN 句のパラメータ数制約（2,100）
   ・テーブルを【2 回読む】ことになる
   ・インデックスが必須（原文の指摘の通り）

現在の代替:

-- ① キーセット ページング（推奨）★
SELECT TOP (1000) * FROM 明細
 WHERE Id > @lastId          -- ← 前回の最後のキーから続ける
 ORDER BY Id;

--   ・IN 句が不要、パラメータは 1 個
--   ・OFFSET/FETCH より速い（深いページで劣化しない）
--   ・中断・再開が容易（@lastId を保存するだけ）

// ② そもそも DataReader で【ストリーム読み】する
using var reader = await cmd.ExecuteReaderAsync(
    CommandBehavior.SequentialAccess);
while (await reader.ReadAsync())
{
    // 1 行ずつ処理する。【全件をメモリに載せない】★
}

【DataReader が使えるなら、それが最も素直】
   ・SqlClient は内部でパケット単位にまとめて受信している
      → 明示的なフェッチ サイズ指定がなくても十分に速い
   ・原文の「フェッチ機能がない」という課題は、
     【SQL Server に限れば実質的な問題にならない】★

【ただし】
   ・読みながら同じ接続で更新はできない（別接続が要る）
   ・長時間の読み取りは【ロック・バージョンストア】に影響する
      → スナップショット分離、または分割読みを検討

• 100 万件のデータの SELECT → INSERT 処理を上記の疑似フェッチ方式で記述した場合、
  Transact-SQL では 15 分程度であった処理が、.NET でもほぼ ≒ の時間で処理が完了した。

• まずまずの性能であり、この方式が採用できる条件下であれば
  処理データ量が中規模のバッチであっても .NET で実装可能と考える。

• また上記は、.NET のバッチプログラムをネットワーク上ではなく、
  DB サーバ上に直接配置した場合の性能情報である。

• ネットワーク経由の場合は、特に更新ラウンド・トリップのため低速になり、
  ネットワークの使用状況によっては非常に低速になる事もあるため注意が必要である。

補足（この検証の価値と、現在の数字感）: 実測して比較している点が
本ページの価値であり、結論も妥当である。
ラウンドトリップが支配的という指摘は、現在も変わらない最重要点である。

【なぜラウンドトリップが効くのか】
   100 万件 × 1 件ずつ INSERT
     = 100 万回の往復

   同一サーバ内（共有メモリ / Named Pipes）: 往復 ≒ 0.05ms
     → 100 万 × 0.05ms = 【約 50 秒】

   LAN 経由（TCP、RTT 0.5ms）:
     → 100 万 × 0.5ms = 【約 8 分】★

   クラウド越し（RTT 5ms）:
     → 100 万 × 5ms = 【約 83 分】★★

 → 【1 件ずつ処理する設計は、距離が離れると破綻する】

【したがって】
   ・原文の「DB サーバ上に直接配置」は、
     当時としては【正しい回避策】★
   ・現在のクラウド環境では物理的に同居できないため、
     【往復回数そのものを減らす】設計が必須になる
       → SqlBulkCopy、TVP、MERGE、バッチ更新

現在の数字感（参考）:

100 万行の INSERT（SQL Server、同一リージョン）
   1 件ずつ ExecuteNonQuery      … 数十分
   TVP（1000 件ずつ）             … 数分
   【SqlBulkCopy】                … 【数十秒】★
   BULK INSERT / bcp（ファイル） … 数十秒

.NET プログラムでも、大量データを処理可能
（ただし、ネットワーク経由はオーバーヘッドが大きいので注意）。

SQL CLR の採用も考えられるが、採用例が少ないので、

• 基本的には、

  • SQL Server であれば Transact-SQL
  • Oracle であれば PL/SQL

  が良いと考える（処理方式統一の標準化の意味も含めて）。

• 文字列処理等にアドバンテージがあると言われているが、
  カーソル操作をサポートしていない点が大きな欠点となっている。

.NET ではステージング・フェーズを実装して大量データ登録は DB 機能に任せるなど。

補足（この結論が現在の定石そのもの）: 「ステージング フェーズを実装して
大量データ登録は DB 機能に任せる」——
これが現在の ELT（Extract-Load-Transform）の考え方であり、
20 年近く経っても正しい。

【ステージング方式】★

  ① .NET     … データを取得・整形し、【一括で流し込む】
                  SqlBulkCopy / BULK INSERT / bcp
                       ↓
  ② ステージング テーブル（一時的な受け皿）
                       ↓
  ③ T-SQL   … 【集合演算で】本テーブルへ反映
                  MERGE / INSERT ... SELECT / UPDATE ... FROM

 → 【行ループが完全に消える】
 → 往復回数が【数回】になる
 → 変換ロジックは DB エンジンが最適化してくれる

// ① SqlBulkCopy（.NET から最速で流し込む手段）★
using var bulk = new SqlBulkCopy(conn, SqlBulkCopyOptions.TableLock, tran)
{
    DestinationTableName = "dbo.Staging_注文",
    BatchSize = 10000,
    BulkCopyTimeout = 0,          // 無制限
    EnableStreaming = true,       // ← IDataReader からストリームで
};
await bulk.WriteToServerAsync(reader);

【SqlBulkCopy を速くする要点】
   ・TableLock オプション（他の更新がないなら）★
   ・【インデックスを落としてから流し、後で貼り直す】
   ・復旧モデルを一括ログにする（可能なら）
   ・BatchSize を調整する（大きすぎるとログが膨らむ）
   → [SQL Serverの大量データ処理性能](MS_SQLServerBulkDataPerformance) 参照

原文の「処理方式統一の標準化の意味も含めて」という理由付けも重要である。

【技術的な優劣より、統一の価値が勝ることがある】
   ・SQL CLR を 1 箇所だけ使うと、
     【その 1 箇所のために CLR の知識・配置手順・署名が要る】
   ・障害時に「どこに処理があるか」が分散する
   → [Windowsの外字](MS_WindowsGaiji) の「顧客要件に因る」と同様、
     技術選定は【運用のコスト】まで含めて判断する ★

• COBOL（昔から使われており実績が多い）
• 各種ストアド（速度重視、大量データ）

Java や .NET での実績は上記に比べると多くは無いと思いますが、
最近は、Spring Batch などの Batch Framework の登場で、
Java でもバッチが書かれることが増えてきてています。

• Java

  • 多重化

    • 基本マルチスレッド化で多重化。
    • メモリ使用量（制限）の関係で、マルチスレッド化ではなく
      プロセスの多重起動で対応することもあるようです。

  • Batch Framework
    ただ、Batch Framework が、少々オーバースペックの様で
    これをを理解して使いこなすのが難しいらしいです。

• .NET

  • 多重化
    • マルチスレッド化、EXE 多重起動の、両方が可能です。
    • .NET だと、EXE 多重起動の方が一般的だと思います。
      マルチスレッド化が必要となるケースは、あまり思いつきません。

補足（.NET でのバッチ実装の現在）: 「.NET には Batch Framework がない」
という当時の状況は、現在は変わっている。

手段	内容
Worker Service	.NET 標準のテンプレート。DI・設定・ログ・graceful shutdown 込み ★
IHostedService / BackgroundService	常駐処理の標準的な形
自作CUI（CLI）の話	引数を受けて 1 回実行するバッチ
Hangfire / Quartz.NET	ジョブのスケジュール・再実行・可視化
Azure Functions（タイマー）	サーバーレスのバッチ（FaaS config）
Azure Batch / Container Apps Jobs	大規模な並列実行

// Worker Service（現在の標準形）
var builder = Host.CreateApplicationBuilder(args);
builder.Services.AddHostedService<ImportWorker>();
builder.Services.AddDbContext<AppDbContext>(/* ... */);
var host = builder.Build();
await host.RunAsync();

【Worker Service の利点】
   ・[.NET Core における DI](MS_DotNetCoreDI) がそのまま使える
   ・[.NETのログ](MS_DotNetLogging) の ILogger が使える
   ・[FaaS config](MS_FaaSConfig) と同じ構成の仕組み
   ・【SIGTERM を受けて綺麗に止まる】★
      → コンテナ運用で必須
      → [プロセス間通信](MS_InterProcessCommunication) の
        PosixSignalRegistration 参照

「EXE 多重起動の方が一般的」という原文の指摘は今も妥当である。

【なぜ多重プロセスが好まれるか】
   ・1 つが落ちても他に影響しない（障害の分離）★
   ・OS / ジョブ スケジューラで制御できる
   ・メモリの上限を個別に持てる
   ・実装が単純（共有状態がない）

【現在のコンテナ運用でも同じ発想】
   ・1 コンテナ 1 プロセス
   ・Kubernetes の Job / CronJob で並列度を指定する
   ・キューから取る設計にすれば、【自然にスケールする】★

バッチ設計で押さえるべき点（言語を問わない）:

① 【再実行できるか】（冪等性）★
     → 途中で落ちて再実行しても、二重登録にならないか
     → 処理済みフラグ、または MERGE で担保する
② 【中断・再開できるか】
     → キーセット ページング（前述）と相性が良い
③ 【コミット単位】
     → 大きすぎるとログが膨らみ、ロールバックも重い
     → 小さすぎると往復が増える
④ 【多重起動の防止】
     → 名前付き Mutex、DB のロック テーブル、分散ロック
⑤ 【進捗と結果を記録する】
     → 何件処理して何件失敗したか（[.NETのログ](MS_DotNetLogging)）
⑥ 【異常時の通知】
     → 終了コードを返し、監視基盤で拾う

Tags: 移行, データアクセス, ADO.NET, Entity Framework, 性能