DBから取得したデータをCSVに出力するときにループを使用しない方法はありますか？

あるシステムの改修をしています。
そのシステム内で検索結果をCSVに出力する機能があり、
以下の「取得したデータをループしてCSVのデータに設定する」ような処理が実装されています。

*** アクションクラス ***
// SQL作る？
$sql = Doctrine_Query::create()->from('test t');
$sql->addWhere('t.id = ?', $id);
// 他条件追加したり

// SQL発行して変数設定
$this->csvData = $sql->getQuery()->execute();
// 設定した変数はテンプレートphpで使用する

*** テンプレートphp ***
<?php foreach($csvData as $dataObj): ?>
  <?php echo $dataObj->getId() ?>
  // ここでさらにループしたり
<?php endforeach; ?>

*** SQLの結果の格納オブジェクト？ ***
class Product extends sfDoctrineRecord {
    // メインテーブル？
    public function setTableDefinition() {
        $this->setTableName('product');
        $this->hasColumn('name', 'string', 255, array(
             'type' => 'string',
             'length' => '255',
             ));
        // 他複数のhasColumn
    }

    // 結合テーブルの設定？
    public function setMany() {
        $this->hasMany('hoge2', array(
            'local' => 'mst_id',
            'foreign' => 'id'
            ));
        // 他複数のhasMany
    }
}

このCSV出力がとても遅く、改修する必要が出てきました。
調査したところ、DBからのデータ取得は問題なく、②のループ処理で極端に遅いことがわかりました。
3重ループほどしているのが原因だと思います。
そのためループせずに検索結果をCSVに出力するよう考えていますが、方法が中々見つかりません。
ループせずにCSVを出力する方法はありますでしょうか？

miyahan

2017/09/22 12:50

チューニング・高速化というシビアな質問であれば、もう少し現状の処理方法（コード）が判らないと答えづらいです

red13

2017/09/22 14:21

とりあえず「ループ使わずにCSV出力できるか？」ぐらいしか考えていなかったので、一旦はこのままにしています。

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

回答6件

三重ループになっているということは、データどうしの相関関係を見る、あるいは集計を行うようなコードになっている可能性があります。

そういった処理は、SQLでうまく組んでDBサーバの側に処理させる、という選択肢もあります。

ただ、それ以上のことはループの具体的な中身がわからないとなんとも言えません。

投稿2017/09/22 12:52

編集2017/09/22 12:59

maisumakun

総合スコア145183

red13

2017/09/22 13:18

確かフレームワークはsymfonyを使用し、そのsymfonyの機能でSQLを作成、発行しています。 SQLを組み替えて多重ループを避けたいのですが、そのSQLが他機能でも使用しており、 SQL修正をするとどこまで影響が出るのか分かりません。そのため影響の少ないと思われるCSVの出力部分を修正しようと考えました。

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

1000万回の文字連結を行っても高々数秒しかかかりません。ループを万単位で行うことはプログラミングの世界ではよくあることですし、それが数分かかるのであればループそのものではなくループ内で行っている処理に問題があると想像します。

まずは推測ではなく事実に基づき問題箇所を計測しましょう。最も簡単なのはベンチマークです。print('ポイントA:' . microtime(true)); みたいな単純なコードでもよいので、随所でタイムスタンプを計測・記録し、どこで時間がかかっているか特定しましょう。

ただ今回はちりも積もれば状態の問題なので、より詳細なプロファイリングが必要になってくると思います。余力があればお試し下さい：

xhprofでパフォーマンスチューニング入門 - Qiita
PHP開発時におけるデバッグ／プロファイリングの手段・ツール3+2選 | 東北ギーク
XdebugとWinCacheGrindを使って、パフォーマンス測定を行おう PHPプロ！TIPS+
使っているフレームワークにプロファイリング機能があればそれを使う

またDBMSへの問い合わせが不適切ではないかも確認が必要です。

DBMSで実行したクエリのログ(general log)を取って、同じようなクエリが大量に実行されてないか調査してください。なお general log はその性質上非常に巨大になるため、解析が終わったら記録を止めるのを忘れないで下さい。また、slow log で log_queries_not_using_indexes オプションを使ってインデックスを使っていないクエリがないかも併せて調査してください。

十分なコードが提示されていないため想像でしかありませんが、考えられる原因を挙げておきます：

実はDBMSへの大量アクセスしている

(ORマッパーを使っている場合) 結合先テーブルのカラムを読み込むときに遅延ロードが起きて、１レコード読むたびにDBへ再問い合わせしてしまっている。

→ 予めJOINして読み込むようオプションを設定する。

※個人的に一番臭いと思っています。

実は出力に時間がかかっている

既に php://output を試しているとのことなので、この可能性は薄いでしょう。

ループ内で別の処理・副次問い合わせを繰り返している

ループ外に追い出せるものは追い出す
入力に対して出力が常に一定な関数（DBを検索して商品区分コードから区分名を返すとか）ならば関数キャッシュを使う
必要な情報は最初のDB問い合わせ時に JOIN 等を使って一度に取り出す。

まあこれもあるならとっくに気づいているはずですから望み薄ですね。

結局のところエンジニアは超能力者ではないので真実はわかりません。:-)

以下余談：

そもそもあなたの知識ドメインでは解決できなかった問題を質問しているわけですから、あなたが考えた仮定（ループを減らせば高速化出来る！）のみ書いてもおそらく解決には結びつきません。仮に満足いく回答が得られてもそれは極大値に過ぎず、最良の方法を見つけるのは十分な "情報" が必要です。NDA等で難しい部分もあると思いますが、回答者が協力するわけですから質問者も協力してほしいところです。

投稿2017/09/23 04:13

編集2017/09/23 04:52

miyahan

総合スコア3095

red13

2017/09/23 04:51

質問内容がざっくりとしてるのは自分でもよくわかっていないためです。開発環境の事情により、ローカルでの開発環境を構築することができません。また、確認時にはテスト環境で確認していますがphpやサーバーの設定を容易に変更することができません。テーブルの結合はhasManyで結合していると思われますが、もしかしたらループ時に1件ずつid等の値でDBに探しに行っている可能性があります。そうなるとSQLの組み立てをしている処理を大幅に修正する必要があるかもしれません。よくあるDBから取得したのを配列に格納し、ループで設定する処理しか見たことがないので、 1件ごとにDBアクセスするという考えはありませんでした。

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

このCSV出力がとても遅く、改修する必要が出てきました。

調査したところ、DBからのデータ取得は問題なく、②のループ処理で極端に遅いことがわかりました。
3重ループほどしているのが原因だと思います。

DB データの取得に問題がないのであれば、問題箇所はループ処理の中身であり、中身がわからなければ、手の出しようがありません。。。

ループ毎に全走査して合致箇所だけ抜き出す。みたいな処理しているんじゃないですかね？
そのあたりを整理すれば、早くなると思います。

投稿2017/09/23 00:52

退会済みユーザー

総合スコア0

red13

2017/09/23 01:18

<?php echo $obj->getId() ?> と取得しているだけだと思うんですが、ループ時の中の調査もしないとダメですね。 4クラスほど継承してるクラスがあったりで、実際に処理が実行されるクラスが見つからないのですが、そこはやるしかありませんね。

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

DBのアクセスがむごくない限り、LOOP処理が3重だろうとよほど間抜けな作り方をしていない限り極端に処理が遅くなるとは考えにくいです。差支えない範囲で遅いという現行のコードを現象を再現できるものを載せては？
こうしたら良いのでは？って案がいくつも出てくるかも？

投稿2017/09/22 14:48

Orlofsky

総合スコア16415

red13

2017/09/22 23:53

覚えている範囲でコードを再現してみました。アクションクラスでデータを取得し、テンプレートで出力値を設定しています。そのテンプレートをcsvとしてダウンロードさせているような感じです。

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

SymfonyでSQLを生成している旨の記載がありまずが、スイッチの初期化をしていないために
全てのループでSQL生成を行っているということはないですか？

そもそもオブジェクトでのループで遅いのであれば、
オブジェクトから出力用のSQL生成だけ拾って、
後は自力で生成すればいいと思います。

Orlofskyさんの言われる通り、オブジェクトの作りに問題があるようにしか思えません。

投稿2017/09/23 00:13

n884

総合スコア100

red13

2017/09/23 01:10

全てのループでSQL生成をしているとは思えませんが、そこまでは調査できていません。 SQLを格納するオブジェクトを追記しました。その格納するオブジェクトが問題なのかまでも調査できていません。

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

ループしない方法ではないですが、こんな感じでどうでしょう？

PHP
1$csv=[
2  ["a","b","c"],
3  ["x","y","z"],
4  [1,2,3],
5  [10,200,3000],
6  ];
7foreach ($csv as $row)
8{
9    fputcsv(fopen('php://output', 'w'), $row);
10}
11$str = ob_get_clean();
12print $str;

$csvはfetchAll的な処理でDBから取り出して下さい

投稿2017/09/22 12:45

yambejp

総合スコア114825

red13

2017/09/22 13:23

「php://output」は試しましたが、ほとんど変わりませんでした。 3重ループになっているので、処理を一部削除して1重ループにしてみました。約100件で10秒ほどなのが、3秒ほどになりました。データ数は最大で数万件あるので、1重ループで300秒ほど、そのためループしない処理を試そうと思いました。

yambejp

2017/09/22 13:36

環境にもよりますが、mysqlでoutfileを使うという考え方もあります SELECT * FROM テーブル INTO OUTFILE '/tmp/hoge.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' その後、hoge.csvを公開ディレクトリに移動して出力してやるとかですかね・・・

red13

2017/09/22 14:19

なるほど。調べたらDBサーバー内のcsvファイルに出力するようですね。 phpの実行とDBサーバーは別なので、出力後に取得する処理が必要かもしれませんね。それかexecやsystemを使用して mysql コマンド～ csv.sql > hoge.csv みたいにすれば良いかもしれません。

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

あなたの回答