SQLServerでDataTableの内容を一度のSQLでデータベースに更新する

DataTable の内容をデータベースに登録、または更新したい場合がある。
しかし、DataTable の行をループしながら行毎に INSERT 文や UPDATE 文を発行すると IO 負荷が高くなり、パフォーマンスが悪化する。
そこで、SQL Server のユーザー定義テーブル型を使用することにより一括での更新が可能となる。

前提条件:SQL Server 2008 以降

 

IO負荷が高い例

まずは、悪例としてデータテーブルのレコードを 1 件ずつ INSERT していく方法。
上記の方法だと件数が少なければ問題が無いように感じるが、1万件を超えてくるとそのパフォーマンスの悪さが目立ってくる。
1万件のデータをループして INSERT すると1万回の IO 処理となり、オーバーヘッドが大きくなる。
しかし、次の方法で INSERT すると1回の IO 処理で済む為、更新するデータ量は変わらないがかなりのパフォーマンス改善が見込まれる。

 

DataTableをパラメータとして渡す

DataTable をパラメータとして渡す為には、SQL Server 側にユーザー定義テーブル型を作成しておく必要がある。

テーブルの定義と同様に括弧の中にフィールド定義を記述する。
テーブルで定義できるフィールドはTableTypeでも定義可能。

続いて、プログラム側の処理。

ユーザー定義テーブル型を使用することで DataTable をテーブルのように渡すことができる為、一括で SELECT して INSERT または UPDATE が可能となる。

<注意事項>

  • DataTable のフィールドはユーザー定義テーブル型のフィールドと一致している必要がある。
  • ユーザー定義テーブル型を渡す場合は、SqlDbType.Structured で渡す。

 

ユーザー定義テーブル型を作成しておく必要はあるが、こちらの方法であれば実行速度が圧倒的に速く、パフォーマンス改善が期待できる。
デメリットとしては、ループせずに一括で更新する為、プログレスバーなどで進捗を表示することができない。

 

おすすめ

コメントを残す

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください