Dazecoop – Blog of David Cooper – Photography, Videography, Drifting & modifying cars

Zend Framework – at first I wasn’t much impressed. Bloated, over-rated, excessive. I was wrong, now I’m loving it 100%!

As I build more and more larger and more complex websites and web-based applications based around the Zend Framework, I attempt to improve the systems I build every time – more efficient coding, quicker database queries, less queries, etc.

One thing that I’ve wanted for a while, though, is the ability to view the time taken to run each query on any particular page, the total time to execute all queries, and the ability to view all queries. By this I mean I don’t want to re-code all my class’s and controllers in order to show the SQL, but instead create a simple bit of code to do it for me. Surely, Zend being its awesome-self should support something like this?

After a little searching around – why yes, yes it does!

Here’s my version of a simple output debug-type thing to show total query execution time, each query itself and each queries time, the average execution time & some more. Anyway, on your bootstrap file where you’re defining what database to use, enter this below…

'profiler' => true

For example then…

'host'     => 'localhost',
'username' => 'dbuser',
'password' => 'dbpass',
'dbname'   => 'dbname',
'profiler' => true

Then, further down on your bootstrap file, after $controller->dispatch(); copy/paste this…

// Zend framework debug cool stuff - www.dazecoop.co.uk
$debug = false;
if (($debug == true) || (strstr($_SERVER['REQUEST_URI'],'debug=1'))) {

echo '<div style="font-family:arial;padding:10px;background:#efefef;font-size:11px;position:absolute;top:0px;right:0px;">
<a href="javascript:void(0);" onclick="if ($(\'#debug-panel\').css(\'display\')==\'block\'){$(\'#debug-panel\').slideUp();}else{$(\'#debug-panel\').slideDown();}" style="color:#000;">Debug</a></div>';
echo '<div id="debug-panel" style="width:850px;font-family:arial;position:absolute;top:0px;left:0px;display:none;background:#efefef;font-size:11px;color:#000;padding:20px;">';
$profiler = $db->getProfiler();
$totalTime    = $profiler->getTotalElapsedSecs();
$queryCount   = $profiler->getTotalNumQueries();
$longestTime  = 0;
$longestQuery = null;
foreach ($profiler->getQueryProfiles() as $query) {
if ($query->getElapsedSecs() > $longestTime) {
$longestTime  = $query->getElapsedSecs();
$longestQuery = $query->getQuery();
}
$queries .= '<small>('.round($query->getElapsedSecs(),5).' seconds)</small> '.$query->getQuery().'<hr style="border-top:1px solid #cccccc" />';
}
echo 'Executed <strong>' . $queryCount . '</strong> queries in <strong>' . $totalTime . ' seconds</strong>' . "<br />";
echo 'Average query length: ' . $totalTime / $queryCount . ' seconds' . "<br />";
echo 'Queries per second: ' . $queryCount / $totalTime . "<br />";
echo 'Longest query length: ' . $longestTime . "<br />";
echo "Longest query: \n" . $longestQuery . "<br />";
echo '<hr />'.$queries;
echo '</div>';
}

Although making sure you’ve got jQuery already installed before using the above code. And make sure to subscribe to my RSS feed for possible future awesomeness