Stock Item List endpoint very slow on bigger datasets

[LavissaWoW]

Please verify that this bug has NOT been raised before.

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

Describe the bug*

GET /api/stock/?part_detail=true&location_detail=true performs a lot of db operations on larger datasets.

Steps to Reproduce

Call /api/stock/?location=8&part_detail=true&location_detail=true

Expected behaviour

Less operations, thus faster execution

Deployment Method

• [ ] Docker
• [ ] Bare metal

Version Information

0.14.0 dev

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

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

Relevant log output

No response

[SchrodingersGat]

What is a big dataset here (i.e. how many records are being returned)?

Nominally this is why all the tables enforce pagination - returning all the database results at once is going to be slow.

We will need to profile this to work out where the inefficiencies are coming from. It could be a queryset annotation issue, or something else

@LavissaWoW a good starting point would be to perform some queries with different number of results against the endpoint and record the resulting API query time, e.g.

/api/stock/?location=8&part_detail=true&location_detail=true&limit=100

[LavissaWoW]

@SchrodingersGat Location ID 8 in the demo dataset sits at ~140 BUT, what I'm seeing here too, is that the GET param location: x returns all items in location x, but also x/y, x/y/z, x/y/w and so on.

[LavissaWoW]

[22/Feb/2024 23:20:24] "GET /api/stock/?location=7 HTTP/1.1" 200 1232660 [22/Feb/2024 23:25:42] "GET /api/stock/?location=7&part_detail=true HTTP/1.1" 200 1733185 [22/Feb/2024 23:27:13] "GET /api/stock/?location=7&part_detail=true&location_detail=true HTTP/1.1" 200 1840500

Location ID 7 only has 2 stock items. 1232660 bytes without subquery details is a lot more than 2 items

Given that location ID 7 returns all sub-items, the queried amount of items is ~500

[SchrodingersGat]

You can set the cascade=False to exclude items from sublocations

[LavissaWoW]

I'm not sure how to time queries that require auth, as that moves outside the app. But, trying http://localhost:8000/api/stock/?location=7&part_detail=true&location_detail=true&limit=100 in-browser takes about 10-15 seconds to resolve. (My experience is that accessing the API in a browser tab is slow af, so that adds something)

[SchrodingersGat]

Related: The /api/part/ endpoint is also very slow for lots of records. Looks like a significant number of database hits are occurring here

[SchrodingersGat]

@LavissaWoW did you make any progress on this? Note that we now have django-query-count middleware active, which you can enable with the INVENTREE_DEBUG_QUERYCOUNT env var (or with debug_querycount: true in your config file).

[LavissaWoW]

Haven't gotten around to this yet. Was dreading looking at queries, but I can def test with that setting enabled and see what I can glean from the data.

[SchrodingersGat]

@LavissaWoW I am going to bump this issue to the next release cycle