郵便番号簿のBulk Merge 取り込みと、 都道府県コード表作成のまとめ

数回に渡って郵便番号CSV　Fileから47レコードの 都道府県コード表を作成するコストを書きました。
その都度、テストに使ったDBサーバーやクライアント端末が異なっていて、結果に一貫性がなかったのと、発端になった非推奨ソースがイビツだったことによりスッキリしない部分がありました。
今回の纏めでは、郵便番号CSV全件(郵便番号の重複を解消した自前の補正後データ: 118822件)を入力とします。

①全件作成
A:Bulk Merge で全件作成
MERGE INTO z_郵便番号 A USING OPENROWSET( BULK '補正郵便番号.CSV' ,FORMATFILE = '郵便番号.fmt' ) B
ON A.郵便番号 = B.郵便番号
WHEN MATCHED THEN UPDATE SET A.全国地方公共団体コード = B.全国地方公共団体コード,.....
WHEN NOT MATCHED THEN INSERT VALUES ( B.郵便番号 ,..........);


B: 言語でLoopで回す
StreamReader sr = new StreamReader(CSV-file)
while (true)
{
string text = sr.ReadLine(); if (text == null) break;
項目移送()
Insert文実行()
}
sr.Close();





② なければ挿入式で 47行の都道府県表を作る


結果: Debugモード Releaseモード
①
A: 5秒 5秒
B: 91秒 89秒

改めて、Bulkモードの早さに驚いてます。約18倍の差。
言語で回せば 91秒/118822件= 0.76 mSecなのでそこそこの速度。
Bulkだと 5秒/118822件= 0.042mSec...これは早い。　挿入更新をバッチ化する価値は高そう。(トランザクションの関係で別も問題は発生しますが)
DebugモードとReleaseモードの差はないようです。


②-A
発端となったソースロジックでなく、テスト用に書き換えました。
StreamReader sr = new StreamReader(CSV-file)
while (true)
{
string text = sr.ReadLine(); if (text == null) break;
try
{
Insert文実行()
}
catch{} ;/重複例外握りつぶし
}
sr.Close();

Debugモード Releaseモード
5492秒 395秒

以前と同様に高コストでしたが、 Relaseモードはまだましですね。Debugモードでの例外扱いが、如何に高く付くかが判りました。

②-B Select文で自前制御
StreamReader sr = new StreamReader(CSV-file)
while (true)
{
string text = sr.ReadLine(); if (text == null) break;
try
{
int rc=(select Count(*) where ユニーク条件)
if(rc==0)
{
insert文実行()
}
}
}
sr.Close();

Debugモード Releaseモード
77秒 70秒

なぜか、DebugモードとReleaseモードに差がでました。
②-D のストアード利用より、低コストの結果がでたので以外でした。



②-C 自前でチェック用Hashで制御
if (!自前で重複チェック_DICT.ContainsKey(名))
{
自前で重複チェック_DICT.Add(名, 名);
Insert文発行()
}

Debugモード Releaseモード
2秒 2秒



②-D ストアードでノーマル処理:(挿入のみで更新はしないので Select文で判断)
StreamReader sr = new StreamReader(CSV-file)
while (true)
{
string text = sr.ReadLine(); if (text == null) break;
try
{
declare @cnt int;
select @cnt = count(*) from J01_都道府県表 where 都道府県コード=@cd and 都道府県名称=@名称;
if @cnt=0
begin
insert into J01_都道府県表(都道府県コード,都道府県名称) values (@cd,@名称)
end
}
}
sr.Close();

Debugモード Releaseモード
131秒 129秒

②-E ストアードで例外期待処理:
StreamReader sr = new StreamReader(CSV-file)
while (true)
{
string text = sr.ReadLine(); if (text == null) break;
try
{
BEGIN TRY
insert into J01_都道府県表(都道府県コード,都道府県名称) values (@cd,@名称)
END TRY
BEGIN CATCH
NOP
End CATCH
}
}
sr.Close();

Debugモード Releaseモード
175秒 172秒

ストアードの例外コストは低そうです。


こうやって見ると、データの特性によって、手法の在り方が変わるので、教科書的正解って見つからないのかも知れませんね。
RDBに任すのが正解とは限りませんね。ロジックで最速化を図った上で、DB操作するのが良いようです。
可読性が悪くなるという意見も聞こえて来そうですが。