Closed rhertogh closed 1 year ago
How many actual queries are there?
@samdark
How many actual queries are there?
In total there were 70 queries in the example. The excessive 20 queries were in this case caused because the related models were not eager loaded. So in this case the total number of queries for the request could be reduced to 51 (70 - 20 duplicates + 1 for the eager loading).
What is the actual benefit of this?
@bizley
The main goal is to detect programming mistakes like not eager loading data. For example a REST API index action returns 20 models. When serializing those models a lot of additional queries might automatically be executed to populate relational data when there is no eager loading of those relations.
The current 'duplicates' functionality is not able to detect those repeated calls since the query itself is different each time (in the screenshot above the id
value in SELECT * FROM users WHERE id=x.
).
Similar to the current $criticalQueryThreshold
we can add a $repeatingCallerCallsCriticalThreshold
(with a much lower value) providing a warning if the same code makes a lot of DB queries.
So the actual number of queries per line is not 1 but 20? If so then it could be considered a bug that it displays 1.
No, there is only one query per row, similar to the current 'duplicates'.
If there are two duplicate queries, you would see 2 rows, each showing 2
duplicates.
For reference, the existing 'duplicates' implementation: https://github.com/yiisoft/yii2-debug/issues/281#issuecomment-341917287
Duplicates occur when the exact same query is executed (could be the same or different source code). E.g: | File:Line | Query | DB Panel 'Duplicates' | DB Panel 'Repeating calls' |
---|---|---|---|---|
User.php:20 | SELECT * FROM user WHERE id=123; |
2 | 0 | |
Test.php:55 | SELECT * FROM user WHERE id=123; |
2 | 0 |
Results in 2 duplicates, 0 repeating callers.
Repeating calls occur when the exact same source code makes a DB call (could be the same or different queries). E.g: | File:Line | Query | DB Panel 'Duplicates' | DB Panel 'Repeating calls' |
---|---|---|---|---|
User.php:75 | SELECT * FROM user WHERE id=123; |
0 | 3 | |
User.php:75 | SELECT * FROM user WHERE id=456; |
0 | 3 | |
User.php:75 | SELECT * FROM user WHERE id=789; |
0 | 3 |
Results in 0 duplicates, 1 repeating caller making 3 repeating calls.
A combination of both duplicates and repeating calls is also possible E.g: | File:Line | Query | DB Panel 'Duplicates' | DB Panel 'Repeating calls' |
---|---|---|---|---|
User.php:75 | SELECT * FROM user WHERE id=123; |
2 | 3 | |
User.php:75 | SELECT * FROM user WHERE id=456; |
0 | 3 | |
User.php:75 | SELECT * FROM user WHERE id=789; |
0 | 3 | |
Test.php:55 | SELECT * FROM user WHERE id=123; |
2 | 0 |
Results in 2 duplicates, 1 repeating caller making 3 repeating calls.
Ah, now I get it. While it's useful, I think it likely should be a separate panel since this number is not per query.
Yes, a separate tab would indeed be good. Then it can even be grouped by the stack trace.
What steps will reproduce the problem?
The database panel only show queries as duplicate when they match exactly, this ignores queries called with different parameters which makes it hard to identify them.
What's expected?
An additional column that shows repeating calls from the same function.
Implementation
I've got a proof of concept working:![image](https://user-images.githubusercontent.com/1292337/229305517-1121d9c9-6acc-40f7-b68e-a67e29fa2452.png)
It works by hashing the stack trace and count how many times that is repeated.
I would like to hear if anybody is interested in a pull request or has other ideas to include.