DAO Performance Issues

Yii 2.0 General Discussions
1

I am writing a script to build a cache. I wrote the script in vanilla php using PDO first. Then I moved it over to Yii 2 using DAO and it went from taking 8.5-9 seconds to perform the script to around 21 seconds. The stripped down version of the script is as follows:




$query = 'SELECT * FROM table1';


$query1Results = \Yii::$app->db->createCommand($query)->queryAll(\PDO::FETCH_OBJ);


$segmentsQuery = 'SELECT table2.*, table3.field1

     FROM table2

         JOIN table3 ON table3.foreignKey = table2.primaryKey

     WHERE table2.foreignKey = :table1PrimaryKey

     ORDER BY table2.field2';

$query2Command = \Yii::$app->db->createCommand($segmentsQuery);


$segmentFeesQuery = 'SELECT table4.field3, table5.field4, table5.field5

     FROM table4

          JOIN table5 ON table5.primaryKey = table4.foreignKey

     WHERE table4.foreignKey = :table2PrimaryKey';

$query3Command = \Yii::$app->db->createCommand($segmentFeesQuery);


foreach($query1Results as $query1Result) {

    $data = array('table1PrimaryKey'=>$query1Result->PrimaryKey);

    $query2Results = $query2Command->bindValues($data)->queryAll(\PDO::FETCH_OBJ);


    //setup some arrays for data storage


    foreach($query2Results as $query2Result) {


        //put a bunch of the data into the arrays setup above


        $data = array('table2PrimaryKey' => $query2Result->PrimaryKey);

        $query3Results = $query3Command->bindValues($data)->queryAll(\PDO::FETCH_OBJ);


        //setup another array for storing data


        foreach($query3Results as $query3Result) {

            //store data in the array made above

        }

    }

}


//format and output the arrays generated

I was having issues with the script running out of memory at about 17 seconds but once I disabled YII_DEBUG that issue was resolved.

Any Ideas that you might have, configurations to check, etc. on how we can improve this performance would be very appreciated.