1ループ毎にSELECTが発生する処理の代替策

こんにちは、
現在C#6.0 + Npgsql + PostgreSQLを使い
仕事で以下の様な処理をしています

1.住所などの個人情報が大量に入ったCSVファイルを読み込む
2.その内容を元にデータベースの色んなテーブルにSELECTをする
3.取得した情報とCSVの内容を使用して（計算等）他のテーブルにINSERTする

INSERT部はCSV形式で一気に書き出してcopyコマンドを使用しているのでINSERTに関しては問題ないと思っています

現在は一般的に良くないであろうCSV1行 = 1ループ毎にSELECTをしている状態です。
その為SELECTを大量に発行されプログラムの1ループ自体がかなり時間がかかってしまっています。
このような処理の場合はループ毎にSELECTするしかないのでしょうか
またプログラムの部分でもっと効率的な取得方法があれば教えていただけると幸いです

よろしくお願いいたします

lang
1var command = new NpgsqlCommand([SELECTクエリ], [NpgsqlConnection]);
2var reader = command.ExecuteReader();
3reader.Read();
4// reader["name"] 等取得 //
5reader.Close();

行動規範の内容に同意します

回答5件

PostgreSQL FDW を作ってSQLでログ検索してみた

私は PostgreSQL を使ったことがないので詳細は知りませんが、PostgreSQL は外部テーブルを使うことができるようです。つまり、CSV をあたかも自分のデータベースの一つのテーブルかのように扱うことができます。

投稿2016/07/26 10:01

Zuishin

総合スコア28660

MMashiro

2016/07/26 10:10

SQLに関してはあまり詳しくなくPostgreSQL FDW、初めて知りました。確かにこれを使用してクエリの中で完結できればかなり高速化出来そうな気がします有難うございます、導入できるか検討してみます

行動規範の内容に同意します

ベストアンサー

私はOracleしか使っていませんが、基本的には同じ部分も多いと思いますので、知っている範囲の話をします。

まずはレスポンス改善であれば計測しましょう。

私もレスポンス改善の際に予想していた問題点が当たるというのは半分もありません。そうして、ボトルネックを改善しないかぎり”ほんのわずか”の効果しかあげられません。

というわけで、以下の様な作業をすると良いと思います。

・ CSVの読み込みからparseの部分の時間を計測します
適切なロジックでParseしていますか？

・ Insert用のCSVの書き出しの部分の時間を計測します
ここは、あまり時間がかかる部分ではないはずなので、
この処理の何倍時間がかかっているかでどのくらい遅い処理なのか判別出来ます

・ Insertの時間を計測します
本当に時間がかかっていないのか確認する必要があります。
経験的には、Selectの何倍も時間がかかる処理で、インデックスの状態によっては、
やばいくらい時間がかかります。もちろん使ってないインデックスは削除しましょう。
また、すでにDBに登録されているで項目を登録していませんか？
項目が減らせればそれだけ処理時間が減ります。

・各SQLの発行からDBからのレスポンスにかかる時間を計測します
こっからが本題ですが、さっきも説明したとおりボトルネックをおさえる意味で前の項目は
必須です。

・これでIOにかかる時間がはっきりしたので、残りの時間はCPUを使っている時間となります。
この処理で、時間がかかっている場合は、メモリを使いすぎていないかログを取ってみて下さい

すごく勝手に予想しますが、ここまでやるとSelectの発行件数の問題ではなく、重いSQLがあることのほうが問題だと思えるようになっているはずです。

・重い順にSQLがインデックスを使って検索しているか確認してください
インデックスを適切に設定すれば、検索時間は格段に早くなります

・同じ検索キーで同じSelectを発行していませんか
同じキーの検索が頻発するなら、Dictionaryクラスなどに詰めて検索結果を再利用するようにして下さい。

・使わない項目を検索していませんか
SQLの発行より、レスポンスのIOのほうが時間がかかっているということも多いです。

・副問い合わせでDB側で処理をさせてIOを減らします。
この作業は最後にすべきです。上記の改善に悪影響を及ぼす場合があるからです。
とくに、複雑すぎるSQLは”技術的負債”の典型例と言って問題ないと思います。

・ところでDBコネクションは毎回破棄していませんか？
コネクションプールを利用できていれば問題ありませんが、DBコネクションはそれなりに重いです。
もし、レコード毎に破棄しているのであれば計測対象に含めましょう

書いていて長すぎて途中で後悔しました。読んでくださっているならもう一言だけ。

レスポンス改善は計測から始める地道な作業です。手間はかかりますが頑張ってください。

投稿2016/07/26 13:11

iwamoto_takaaki

総合スコア2883

MMashiro

2016/07/27 01:35

色んな改善案有難うございます、まだまだ初心者ですので参考になります。パフォーマンスプロファイラーを使用して各行にどれぐらいかかっているか、調査等を行ってみたのですが、・副問い合わせでDB側で処理をさせてIOを減らします。此方を行っていなかったため他の影響する部分を見ながら一度テストで実装してみたいと思います。回答有り難うございましたm(_ _)m