So one thing I figured out was the whole regeneration of Schemas on each request. Unfortunately, Schema generation (and the time it takes) isn’t shown in the debug tool / timings. This would be a nice addition I think. After updating my app.php
'cacheMetadata' => true
And turning off +2 minutes in bootstrap.php
if (Configure::read('debug')) {
//Configure::write('Cache._cake_model_.duration', '+2 minutes');
//Configure::write('Cache._cake_core_.duration', '+2 minutes');
}
this did help reduce page load times by about half.
However, I’m still running into view render times that are way off.
For example, I select all the rows of a table.
$instructor ->find();
Which produces 130 results in 86ms (per the Timer)
Rendering these results into index.ctp take 20 seconds.
20 seconds to render 130 rows with 6 columns?
The Timer says this is all in the index.ctp, but i don’t quite believe it.
I’ve paired this back to the simplest of foreach statements, removing anything related to pagination.
<tbody>
<?php foreach ($instructor as $i): ?>
<tr>
<td><?= $i->Instructor_Number ?></td>
<td><?= $i->First_Name . " " . $i->Last_Name ?></td>
<td><?= $i->Company_Name ?></td>
<td><?= $i->Office_Phone_Number ?></td>
<td><?= $i->Specialization ?></td>
<td><?= $i->Association ?></td>
</tr>
<?php endforeach; ?>
</tbody>
So i realized that foreach in this case isn’t actually iterating over an Array but over a Query object. So, it’s not the view, but the Query that is taking all the time.
Getting back to $instructor.
While it appears to take 86ms to perform the query, adding the following shows that in fact it’s not the view at all but still the query.
$instructorsTable = TableRegistry::get('SomeTable');
$instructor = $instructorsTable ->find()->toArray();
die("Here taking 20 seconds");
The finger is now pointing back at the Table /Query / DataSource.
So, going as low level as I can imagine, while still using Cake functions.
$connection = ConnectionManager::get('default');
$instructor = $connection->execute('SELECT * FROM Instructor');
And this takes 20 seconds.
As an experiment
$this->connection = new PDO ("sqlsrv:Server=$this->hostname;Database=$this->dbname", "$this->username", "$this->pwd");
$statement = $this->connection->prepare(''SELECT * FROM Instructor');
$rows->$statement->fetchAll();
Takes just 242 milliseconds to print_r($rows) back to the screen.
I’m just stumped. I have no idea what manipulations of the data Cake is doing behind the scenes that could warrant taking almost 20 seconds to complete.
I still feel like there is some basic switch I’m missing. Or maybe there is some serious bug with SQL Server Datasource??. I just don’t know.
Thanks again!