[API] Test statistics query effficiency needs improving

[SchrodingersGat]

Please verify that this bug has NOT been raised before.

• [X] I checked and didn't find a similar issue

Describe the bug*

New test statistics API endpoint needs optimizing for number of database hits:

Ref: https://github.com/inventree/InvenTree/pull/7164

Steps to Reproduce

• Open "test statistics" tab (e.g. for a build order)
• View output of server logs (with query count debugging turned on)

Expected behaviour

API / database queries should be annotated to prevent duplicate queries

Deployment Method

• [X] Docker
• [ ] Package
• [ ] Bare metal
• [ ] Other - added info in Steps to Reproduce

Version Information

Version Information:

InvenTree-Version: 0.16.0 dev Django Version: 4.2.14 Commit Hash: c2c66c5 Commit Date: 2024-07-31 Commit Branch: build-test-results Database: postgresql Debug-Mode: True Deployed using Docker: True Platform: Linux-5.15.133.1-microsoft-standard-WSL2-x86_64-with Installer: DOC

Active plugins: [{'name': 'InvenTreeBarcode', 'slug': 'inventreebarcode', 'version': '2.1.0'}, {'name': 'InvenTreeCoreNotificationsPlugin', 'slug': 'inventreecorenotificationsplugin', 'version': '1.0.0'}, {'name': 'InvenTreeCurrencyExchange', 'slug': 'inventreecurrencyexchange', 'version': '1.0.0'}, {'name': 'InvenTreeLabel', 'slug': 'inventreelabel', 'version': '1.1.0'}, {'name': 'InvenTreeLabelMachine', 'slug': 'inventreelabelmachine', 'version': '1.0.0'}, {'name': 'InvenTreeLabelSheet', 'slug': 'inventreelabelsheet', 'version': '1.0.0'}, {'name': 'DigiKeyPlugin', 'slug': 'digikeyplugin', 'version': '1.0.0'}, {'name': 'LCSCPlugin', 'slug': 'lcscplugin', 'version': '1.0.0'}, {'name': 'MouserPlugin', 'slug': 'mouserplugin', 'version': '1.0.0'}, {'name': 'TMEPlugin', 'slug': 'tmeplugin', 'version': '1.0.0'}]

Please verify if you can reproduce this bug on the demo site.

• [ ] I can reproduce this bug on the demo site.

Relevant log output

http://localhost:8000/api/test-statistics/by-build/1/
|------|-----------|----------|----------|----------|------------|
| Type | Database  |   Reads  |  Writes  |  Totals  | Duplicates |
|------|-----------|----------|----------|----------|------------|
| REQU |  default  |    1     |    0     |    1     |     0      |
|------|-----------|----------|----------|----------|------------|
| RESP |  default  |   129    |    0     |   129    |    122     |
|------|-----------|----------|----------|----------|------------|
Total queries: 130 in 0.1329s 


Executed 60 time(s).
SELECT COUNT(*) AS "__count" FROM "stock_stockitemtestresult" WHERE
("stock_stockitemtestresult"."stock_item_id" = #number# AND
"stock_stockitemtestresult"."template_id" = #number# AND
"stock_stockitemtestresult"."result")

[SchrodingersGat]

@martonmiklos FYI

[SchrodingersGat]

When viewing stats by part it gets even worse:

[martonmiklos]

Uh, I will look into it. How did you activated this sql debug output?

[SchrodingersGat]

Use the env var INVENTREE_DEBUG_QUERYCOUNT=True or in your config file, debug_querycount=True

[martonmiklos]

Use the env var INVENTREE_DEBUG_QUERYCOUNT=True or in your config file, debug_querycount=True

I have reworked the queries to use joins, however I have not been able to use the debug output as I wanted. I set the env var (tried both the config.yaml too):

I tried to send the statistic requests (which were server properly) right after the server started, however the SQL debug output was not activated. However when I logged in the first (notification list query I think) got debugged.

Is there any way to set the SQL debug for each query? Or how can I specify which query should it debug?

[wolflu05]

There are certain thresholds defined (>25 queries or >0.1s). Set them to a lower value (1 for to log for all requests). You can find them in the settings.py right after the implementation for the previously mentioned querycount enable var. just search with your code editor.