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.