質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

88.09%

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

解決済

回答 5

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 6,160

score 2041

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

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

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

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

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

var command = new NpgsqlCommand([SELECTクエリ], [NpgsqlConnection]);
var reader = command.ExecuteReader();
reader.Read();
// reader["name"] 等取得 //
reader.Close();
  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 5

+3

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

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2016/07/26 19:10

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

    キャンセル

checkベストアンサー

+1

私は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/27 10:35

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

    キャンセル

  • 2016/07/27 11:09

    変更する前後に必ず計測するようにしてください。うまくいくことをお祈りします。

    キャンセル

+1

例えば、必要最小限のデータをDBからメモリに展開しておく
という手もありますが、まあ、件数しだいですね。

SELECTするしか手がないのだったら、そうするしかないでしょう。
それでもなんとかしたいんだったら、1ループ当たりの
詳細な処理時間を計測してみるのかなあ。
意外なところに原因があるかもしれませんよ。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2016/07/26 19:07

    SELECTが重い部分というのはやはりディスクI/O的な問題なのですね…メモリ展開も視野に入れます
    パフォーマンスプロファイラーを使用し1行1行最適化してみましたが、
    やはりいちばんネックな部分はDBアクセスする部分のようでした。
    回答ありがとうございましたm(_ _)m

    キャンセル

+1

csvとDBのボリューム次第で実現の可否が決まってしまいますが、ご提案です。

1.住所などの個人情報が大量に入ったtempテーブルにロード(copy)する。
ここで容量的に問題で有れば忘れてください。

2.その内容を元にデータベースの色んなテーブルにSELECTをする 
必要で有ればいろんなテーブルとJOINする為にcreate indexする。

3.取得した情報とCSVの内容を使用して(計算等)他のテーブルにINSERTする
上記のselect文からcopyデータを作成してINSERTする。

大まかですが以上です。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2016/07/26 20:06

    余りにも色んな形式(カラムの順番や種類が異なる)CSVファイルが大量に存在してそれを全て処理する必要があったので今回は使用できなさそうです、
    取り込むファイルが少ない時やカラムが統一されている場合はそちらの方法を使わせていただきたいと思います、ありがとうございました

    キャンセル

  • 2016/07/26 20:41

    読んでみないと処理が決まらない場合、SQLにて一括処理は難しいですね。ロジックパターンが決まればwhereを使ってcopyファイルの作成が出来そうなのですが、お役に立てず失礼しました。

    キャンセル

+1

3.取得した情報とCSVの内容を使用して(計算等)他のテーブルにINSERTする 

この、計算等はC#側でないとできないのでしょうか?
もしSQLで書けるのであれば、以下のようにするのはどうでしょう?

  1. 住所などの個人情報が大量に入ったCSVファイルを作業用のテーブルにcopy。
  2. SQLで作業用のテーブルと必要なテーブルを結合し、計算等を行ってそのままINSERTする。
INSERT INTO XXX SELECT XXXXXXX

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2016/07/27 10:25

    自社ライブラリを使用している部分があるのでSQLにて書けなさそうでした
    逆に単純なところはその方法を取ろうと思います、
    回答有り難うございました

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 88.09%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る