OBPIH-7566 Improve performance of cycle count all products tab#5614
OBPIH-7566 Improve performance of cycle count all products tab#5614alannadolny merged 2 commits intorelease/0.9.6from
Conversation
kchelstowski
requested review from
SebastianLib,
alannadolny,
awalkowiak,
ewaterman and
jmiranda
github-actions
bot
added
type: bug
| FROM product_inventory_candidate pic | ||
| ) AS transaction_candidate | ||
| GROUP BY transaction_candidate.inventory_id, transaction_candidate.product_id; | ||
|
|
There was a problem hiding this comment.
in a nutshell what this change does is that it uses the helper views ("materialized views") that I created for reporting views. By reading data from tables and not having to do heavy grouping that is then stored in-memory, the response time reduced from 48 seconds to 6 seconds on obstg (tested via injecting the changes directly into db).
There was a problem hiding this comment.
I also tested whether the:
select count(*) from product_physical_count_history;would still return the same amount of results after the refactor, and it does 👍
There was a problem hiding this comment.
Pull Request Overview
This PR improves the performance of the cycle count "all products" tab by refactoring the product_physical_count_history view to use pre-computed candidate tables instead of querying transaction tables directly.
- Replaces direct transaction table queries with UNION of three candidate tables (
adjustment_candidate,inventory_baseline_candidate, andproduct_inventory_candidate) - Introduces a new
product_inventory_candidatetable to handle legacy product inventory transactions (type 11) - Adds appropriate indexes to the new table for optimal query performance
Reviewed Changes
Copilot reviewed 3 out of 3 changed files in this pull request and generated no comments.
| File | Description |
|---|---|
| product-physical-count-history.sql | Refactored to query from pre-computed candidate tables using UNION ALL instead of joining transaction tables directly |
| product-inventory-candidate.sql | New table to materialize product inventory transactions (type 11) with indexes for performance |
| changelog.xml | Added changeset to create the product_inventory_candidate table before it's referenced by the physical count history view |
💡 Add Copilot custom instructions for smarter, more guided reviews. Learn how to get started.
| ON product_inventory_candidate (transaction_id); | ||
|
|
||
| CREATE INDEX idx_product_inventory_base | ||
| ON product_inventory_candidate (product_id, inventory_id, transaction_date); |
There was a problem hiding this comment.
to cover all the scenarios, I had to create one more helper materialized view for the old product inventory transactions that we still take into account while calculating the last counted date on all products tab.
This one is almost the copy of inventory-count-helper-views views, but with transaction type of 11.
| <!-- views required for cycle count last counted --> | ||
| <changeSet id="1740696149645-0" author="kchelstowski" runOnChange="true" runAlways="true" failOnError="true"> | ||
| <sqlFile path="views/product-inventory-candidate.sql"/> | ||
| </changeSet> |
There was a problem hiding this comment.
this has to be placed at the top of the sub-views, as the product-physical-count-history relies on this.
alannadolny
left a comment
alannadolny
left a comment
There was a problem hiding this comment.
The same as Copilot - I have nothing to add
…cal_count_history view that access them
|
I had to move the inventory count helper views higher as well, as tests were failing. In regular instances we shouldn't need to do that, because the inventory count views already exist, but just in case I moved it if someone started openboxes "from scratch". |
Codecov Report✅ All modified and coverable lines are covered by tests. Additional details and impacted files@@ Coverage Diff @@
## release/0.9.6 #5614 +/- ##
===============================================
Coverage ? 8.52%
Complexity ? 1127
===============================================
Files ? 712
Lines ? 45618
Branches ? 10914
===============================================
Hits ? 3890
Misses ? 41149
Partials ? 579 ☔ View full report in Codecov by Sentry. 🚀 New features to boost your workflow:
|
✨ Description of Change
Link to GitHub issue or Jira ticket:
Description:
📷 Screenshots & Recordings (optional)