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 で渡す。

 

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

 

SQLServerで断片化の状態に応じてIndexを再構築する

SQLServerにおいて、インデックスが断片化してしまうとクエリのパフォーマンスが低下してくる。

インデックスの再構成(Reorganize)や再構築(Rebuild)を行うと断片化が解消される為、定期的にインデックスの再構成(Reorganize)や再構築(Rebuild)をすることでこれを解決することができる。

ただし、やみくもにデータベース内の全てのインデックスを再構成(Reorganize)や再構築(Rebuild)するとデータベースの規模が大きいほど、実行に多くの時間がかかってしまったり、ログファイルが大きくなりすぎてしまう。

そこで、断片化の状態を判断して再構成(Reorganize)または再構築(Rebuild)をする。

まず、インデックスの断片化の状態は sys.dm_db_index_physical_stats を使って調べることができる。

sys.dm_db_index_physical_stats が返す avg_fragmentation_in_percent は論理的な断片化(インデックス内で順序が乱れたページ)の割合を示していて、再構成(Reorganize)または再構築(Rebuild)をするべきかどうかの判断の指標とすることができる。

例えば、以下のようなクエリで再構成(Reorganize)のクエリ、再構築(Rebuild)のクエリ、スキーマ名、テーブル名、インデックス名、インデックスID、オブジェクト内のパーティション番号、断片化の割合、インデックスページまたはデータページの合計数を取得することができる。

avg_fragmentation_in_percent > 10 を指定することで、断片化率が 10 % 以上のインデックスを取得している。

インデックスの断片化の状態がわかったら、それによってインデックスを再構成(Reorganize)するか、または再構築(Rebuild)するか決定する。

例えば、10% 以上 30% 未満であれば再構成(Reorganize)、 30% 以上であれば再構築(Rebuild)するとすれば、上記のクエリで再構成のクエリ、または再構築のクエリを生成してそれぞれ実行する。

.net(VB、C#)でLINQを利用してCSVファイルを読み込む

LINQでCSVファイルを読み込む

.NETには、CSVファイルを読み取る際に便利なクラス(TextFieldParser)が「VB.NET」のライブラリに存在する。

このクラスを利用してCSVファイルをLINQで簡潔に扱えるようにする。

まず、CSVファイルへのコンテキストを生成する為のTextFieldクラスを作成する。

このクラスのContextメソッドでコンテキストを生成することでロジックを意識することなくCSVファイルの読み込みが可能になる。

 

 

次は、実際にTextFieldクラスを利用してコンテキストを生成し、CSVファイルへアクセスしてLINQにより入力した条件に一致するレコードデータを表示するコードを記述する。

尚、AsParallel()メソッドを呼び出すことで条件判定をマルチスレッド化できるので、データ量が多い場合は、並列化による高速化が期待できる。

 

 

複数ファイルを跨いで検索する場合は以下のように記述することができる。

※全てのファイルレイアウトが同じ場合

 

 

 

SQLServerで1つのクエリにより複数行をinsertする

1つのクエリで、複数行をinsertする

SQLServer2008以降では、INSERT文で追加行をカンマでつなげることで実現できる。

なお、一度に追加できる最大行数は、1000行となる。

 

ちなみに、SQLServer2005以前では複数行のINSERTは動作しないので、以下のような方法をとることになる。

 

 

SQLServerで日付範囲から日(または月)ごとのデータを作成する

日付範囲から日ごと、または月ごとに年月日(月ごとの場合、日は1日)を作成する

”2017年1月1日から2017年1月31日まで”のようなFrom/Toの日付範囲があり、そこからカレンダーのように日ごとのデータを取得したい場合、以下のような共通テーブル式(CTE:Common Table Expression)を利用した再帰クエリで作成することができる。

 

また、月ごと(日は1日)にデータを取得したい場合は以下のクエリで作成できる。