あるシステムの処理が遅いというので、追跡してみました。
元になるデータを順次読み取り、対応するデータが無ければ追加、有れば、更新という単純なものです。
最近のSQL Serverにも Oracle並の Merge文が可能になったので、便利になりました。このシステムでは、自力で処理していました。
遅い原因の一つが次のようなものでした。
while(!eof(元データ))
{
try
{
sql実行(update TABLE set xxx=@xxx , yyy=@yyy where ユニーク条件)
}
catch()
{
sql実行(insert into TABLE (xxx,yyy) values (@xxx,@yyy) )
}
}
業務要件の動作はしますが、駄目でしょう。
(*)これでもソースレビューは合格したらしいので、レビュワーな何をみているのでしょうね。
int cnt = sql実行(Select count(*) from TABLE where ユニーク条件)
if(cnt==0) sql実行( insert into TABLE (xxx,yyy) values (@xxx,@yyy))
else sql実行(update TABLE set xxx=@xxx , yyy=@yyy where ユニーク条件)
とすべきでしょう。(排他処理は省いてます)
駄目だと決めつけてますが、「行儀が悪い」というだけでは、根拠が薄いので、コスト確認してみました。
(例によって)郵便番号CSVデータを用いて、郵便番号CSVから都道府県表を作ります。
元になる郵便番号CSV
自治体コード5桁 都道府県名 市区名
01101,"北海道","札幌市中央区"
……………………
47382,"沖縄県","八重山郡与那国町"
このCSVデータは12万行あります。これを順次読み取り
都道府県表
コード 都道府県名
01 北海道
……………………
27 大阪府
……………………
47 沖縄県
の47行を作ります。
①事前に存在Checkした処理
while(true)
{
string text = sr.ReadLine(); if (text == null) break;
csv分解();
int cnt = sql実行(Select count(*) from TABLE where ユニーク条件)
if(cnt==0) sql実行( insert into TABLE (xxx,yyy) values (@xxx,@yyy))
else sql実行(update TABLE set xxx=@xxx , yyy=@yyy where ユニーク条件)
}
②例外を利用した処理
while(true)
{
string text = sr.ReadLine(); if (text == null) break;
csv分解();
try
{
sql実行(update TABLE set xxx=@xxx , yyy=@yyy where ユニーク条件)
}
catch()
{
sql実行(insert into TABLE (xxx,yyy) values (@xxx,@yyy) )
}
}
結果は
① 36秒
②4406秒
でした。100倍以上の開きがありました。例外が高コストな処理なのがよく判ります。
CSVデータは、[Provider=Microsoft.Jet.OLEDB.4.0;Data Source=xxx] で読み込めば ADO.NETとして処理できます。
そこで [Provider=Microsoft.Jet.OLEDB.4.0;Data Source=xxx] で接続し、
DataTable dt = (SQL実行)"select distinct left( right(str([F1] + 1000000),5),2) , F4 from KEN_ALL.CSV ";
foreach(DataRow dr in dt.Rows)
{
(SQL実行) ( insert into 都道府県表 (code ,名称) values (@code,@名称))
}
で実行しました。③
結果は
③3秒
でした。殆どがDistinct文の処理時間のようです。必要なデータを抽出時に絞ることが重要ですね。(Linqに通じる思想かな)
(*) CSV fileは DataTableに読み込めば、処理が単純になってスッキリ扱えるのでお気に入りなんですが、あまり知られてないのですよねぇ。
都道府県表を作るような処理の場合、重複チェックをDBに依存するのでなく自前で判定すればどうかも、試行しました。④
private List 自前で重複チェック_List = new List();
while(true)
{
string text = sr.ReadLine(); if (text == null) break;
csv分解();
if (!自前で重複チェック_List.Contains(名))
{
自前で重複チェック_List.Add(名);
sql文実行( insert into TABLE( xxx,yyy) values(@xxx,@yyy))
}
}
結果は
④1秒 (実値0.92秒)
でした。
まとめると(都道府県表をつくるという視点で)
① 36秒
②4406秒
③ 3秒
④ 1秒
最大格差は 4400倍!!。自分もビックリ。「事務データRDBで処理するのがベスト」との声もありますが、このケースのように、事前に対象データの絞り込みは言語で行うほうが良いケースもあります。
実装手段は複数あることが多いので、コスト比較して決定する必要があります。
商売の購入時に合見積もりを取るのと似てますね。