sql-log-panel: statistics per query type

[smarek]

Would it be possible, to include SQL query statistics in the sql-log-panel ?

What I'm generally missing is:

• number of queries per type (SELECT, UPDATE, DELETE, INSERT, DROP, ...)
• summary of time and rows for these types
• link to slowest query (best would be slowest of type, see no.1)

optionally even those:

• link to query that affected most rows (again best would be breakdown per-type, see no.1)
• if it is possible to link cache and sql-log panels, visual or other indication, whether the query was cache-hit or cache-miss (summary of the cache-hit/miss ratio is then in the cache panel as it should be)

Thank you

[markstory]

Would it be possible, to include SQL query statistics in the sql-log-panel ?

Should be possible, just needs some effort to build it 😄

[smarek]

Since I haven't found the right way, i've hacked some kind of solution, which is probably not performance-optimal or visually pleasing, but serves anybody who could need that right now

I realize that proper solution would start of on refactoring DebugKit\Database\Log\DebugLog to handle different syntax for different PDOs and query type from QueryBuilder or different source and transfering the information down the line to template, but I did not find an easy way to do that.

Contains breakdown summary for query type, slowest query per-type and largest per-type (affected most rows)

diff --git a/sql_log_panel.ctp b/sql_log_panel.ctp
index a2b6fdd..f2b45a1 100644
--- a/sql_log_panel.ctp
+++ b/sql_log_panel.ctp
@@ -32,61 +32,156 @@ SqlFormatter::$pre_attributes = 'style="color: #222; background-color: transpare
 ?>
 
 <?php if (!empty($tables)): ?>
-<h4><?= __d('debug_kit', 'Generated Models') ?></h4>
-<p class="warning"><?= __d('debug_kit', 'The following Table objects used {0} instead of a concrete class:', '<code>Cake\ORM\Table</code>') ?></p>
-<ul class="list">
-<?php foreach ($tables as $table): ?>
-    <li><?= h($table) ?></li>
-<?php endforeach ?>
-</ul>
-<hr />
+    <h4><?= __d('debug_kit', 'Generated Models') ?></h4>
+    <p class="warning"><?= __d('debug_kit', 'The following Table objects used {0} instead of a concrete class:', '<code>Cake\ORM\Table</code>') ?></p>
+    <ul class="list">
+        <?php foreach ($tables as $table): ?>
+            <li><?= h($table) ?></li>
+        <?php endforeach ?>
+    </ul>
+    <hr/>
 <?php endif; ?>
 
 <?php if (!empty($loggers)): ?>
     <?php foreach ($loggers as $logger): ?>
-    <?php
-    $queries = $logger->queries();
-    if (empty($queries)):
-        continue;
-    endif;
+        <?php
+        $queries = $logger->queries();
+        if (empty($queries)):
+            continue;
+        endif;
 
-    $noOutput = false;
-    ?>
-    <div class="sql-log-panel-query-log">
-        <h4><?= h($logger->name()) ?></h4>
-        <h5>
-        <?= __d(
-            'debug_kit',
-            'Total Time: {0} ms &mdash; Total Queries: {1} &mdash; Total Rows: {2}',
-            $logger->totalTime(),
-            count($queries),
-            $logger->totalRows()
-            );
+        $noOutput = false;
         ?>
-        </h5>
+        <div class="sql-log-panel-query-log">
+            <h4><?= h($logger->name()) ?></h4>
+            <h5>
+                <?= __d(
+                    'debug_kit',
+                    'Total Time: {0} ms &mdash; Total Queries: {1} &mdash; Total Rows: {2}',
+                    $logger->totalTime(),
+                    count($queries),
+                    $logger->totalRows()
+                );
+                ?>
+            </h5>
 
-        <table cellspacing="0" cellpadding="0">
-            <thead>
+            <?php
+            $stats = [];
+            $slowest = [];
+            $largest = [];
+            foreach ($queries as $query) {
+                $type = explode(' ', $query['query'])[0];
+                // total stats per-type
+                if (!isset($stats[$type])) {
+                    $stats[$type] = [
+                        'count' => 1,
+                        'took' => $query['took'],
+                        'rows' => $query['rows']
+                    ];
+                } else {
+                    $stats[$type]['count']++;
+                    $stats[$type]['took'] += $query['took'];
+                    $stats[$type]['rows'] += $query['rows'];
+                }
+                // slowest query per-type
+                if (!isset($slowest[$type])) {
+                    $slowest[$type] = $query;
+                } else if ($slowest[$type]['took'] < $query['took']) {
+                    $slowest[$type] = $query;
+                }
+                // largest query per-type
+                if (!isset($largest[$type])) {
+                    $largest[$type] = $query;
+                } else if ($largest[$type]['rows'] < $query['rows']) {
+                    $largest[$type] = $query;
+                }
+            }
+            ?>
+            <h4>Statistics</h4>
+            <hr/>
+            <h5>Breakdown per-type:</h5>
+            <table>
+                <thead>
+                <tr>
+                    <th>Query Type</th>
+                    <th>Count</th>
+                    <th>Total time (ms)</th>
+                    <th>Total rows</th>
+                </tr>
+                </thead>
+                <tbody>
+                <?php foreach ($stats as $type => $a): ?>
+                    <tr>
+                        <td><?= $type ?></td>
+                        <td><?= $a['count'] ?></td>
+                        <td><?= $a['took'] ?></td>
+                        <td><?= $a['rows'] ?></td>
+                    </tr>
+                <?php endforeach; ?>
+                </tbody>
+            </table>
+            <h5>Slowest Query:</h5>
+            <table>
+                <thead>
+                <tr>
+                    <th>Query Type</th>
+                    <th>Took (ms)</th>
+                    <th>Query</th>
+                </tr>
+                </thead>
+                <tbody>
+                <?php foreach($slowest as $type => $query): ?>
+                    <tr>
+                        <td><?= $type ?></td>
+                        <td><?= $query['took'] ?></td>
+                        <td><?= $query['query'] ?></td>
+                    </tr>
+                <?php endforeach; ?>
+                </tbody>
+            </table>
+            <h5>Largest Query:</h5>
+            <table>
+                <thead>
+                <tr>
+                    <th>Query Type</th>
+                    <th>Rows</th>
+                    <th>Query</th>
+                </tr>
+                </thead>
+                <tbody>
+                <?php foreach($largest as $type => $query): ?>
+                    <tr>
+                        <td><?= $type ?></td>
+                        <td><?= $query['rows'] ?></td>
+                        <td><?= $query['query'] ?></td>
+                    </tr>
+                <?php endforeach; ?>
+                </tbody>
+            </table>
+            <hr/>
+
+            <table cellspacing="0" cellpadding="0">
+                <thead>
                 <tr>
                     <th><?= __d('debug_kit', 'Query') ?></th>
                     <th><?= __d('debug_kit', 'Rows') ?></th>
                     <th><?= __d('debug_kit', 'Took (ms)') ?></th>
                 </tr>
-            </thead>
-            <tbody>
+                </thead>
+                <tbody>
                 <?php foreach ($queries as $query): ?>
-                <tr>
-                    <td><?= SqlFormatter::format($query['query']) ?></td>
-                    <td><?= h($query['rows']) ?></td>
-                    <td><?= h($query['took']) ?></td>
-                </tr>
+                    <tr>
+                        <td><?= SqlFormatter::format($query['query']) ?></td>
+                        <td><?= h($query['rows']) ?></td>
+                        <td><?= h($query['took']) ?></td>
+                    </tr>
                 <?php endforeach; ?>
-            </tbody>
-        </table>
-    </div>
+                </tbody>
+            </table>
+        </div>
     <?php endforeach; ?>
 <?php endif; ?>
 
 <?php if ($noOutput): ?>
-<div class="warning"><?= __d('debug_kit', 'No active database connections') ?></div>
+    <div class="warning"><?= __d('debug_kit', 'No active database connections') ?></div>
 <?php endif ?>

[garas]

You could add new methods/properties to DebugLog and either recompute after each log(), or compute stats by calling methods from template.

Could you open this or updated diff as pull request, it is easier review with interactive diff viewer (or avoid unrelated whitespace changes).

[smarek]

I'll provide PR gladly, the only thing I'm not sure about, is whether in all supported db syntaxes the first work of query defines it's type.

[markstory]

@smarek You should be able to capture known types (select, update, insert, delete) with a single regular expression. All other types could be lumped into an 'other' type.

[dereuromark]

ping @smarek

[smarek]

@dereuromark thank you for reminder, i will do this, its still relevant for me, just forgotten.

[github-actions]

This issue is stale because it has been open for 120 days with no activity. Remove the stale label or comment or this will be closed in 15 days