Openbravo Issue Tracking System - Retail Modules
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0047423Retail ModulesWeb POSpublic2021-07-21 12:002021-09-01 07:55
Reporteregoitz 
Assigned Toranjith_qualiantech_com 
PrioritynormalSeveritymajorReproducibilityalways
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target VersionFixed in VersionRR21Q4 
Merge Request Status
Review Assigned To
OBNetwork customer
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0047423: Slow incremental refresh of products
DescriptionThe incremental refresh in environment with lots of products, and stores, and one assortment and pricelist per store, is very slow.
The query reads lot of information from disk and create also general slowness on the server.
Steps To Reproduce- Update 30.000 of prices of a catalog of 240.000 on an environment as explained in the Description.

Proposed SolutionInitial query:

select ...
from ...
where ...

AND (
                      product1_.updated>'2021-07-20 10:06:39.019'
           OR obretco_pr0_.updated>'2021-07-20 10:06:39.019'
           OR uom4_.updated>'2021-07-20 10:06:39.019'
           OR pricingpro2_.updated>'2021-07-20 10:06:39.019')
...

Proposed change:

select ...
from ...
where ...

AND product1_.m_product_id in (
    SELECT o.m_product_id
      FROM obretco_prol_product o
      WHERE o.updated > '2021-07-20 10:06:39.019'
        AND o.obretco_productlist_id='F5E7536B8CEA476D82AE0CF40555A426' --> to filter by the assortment of the store
    UNION
    SELECT p.m_product_id
      FROM m_productprice p
      WHERE p.updated > '2021-07-20 10:06:39.019'
        AND p.m_pricelist_version_id='49D82092B886446CAB1245A5227CE9C9' --> to filter by the price list of the store
    UNION
    SELECT mp.m_product_id
      FROM m_product mp
      INNER JOIN m_uom u
        ON u.m_uom_id = mp.m_uom_id
      WHERE u.updated > '2021-07-20 10:06:39.019'
    UNION
    SELECT pro.m_product_id
      FROM m_product pro
      WHERE pro.updated > '2021-07-20 10:06:39.019'
)
Additional Information
TagsNOR
Relationships
related to defect 0044911 closed ranjith_qualiantech_com Incremental refresh of Product cannot use index on updated 
related to defect 0047510 closed ranjith_qualiantech_com in 21Q2, incremental refresh process is slower in 21Q2 than previous releases 
related to defect 0047262 closed ranjith_qualiantech_com Incremental refresh not updating business partner product price 
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2021-07-21 12:00egoitzNew Issue
2021-07-21 12:00egoitzAssigned To => Retail
2021-07-21 12:00egoitzResolution time => 1628546400
2021-07-21 12:00egoitzTriggers an Emergency Pack => No
2021-07-21 22:30rafarodaTag Attached: NOR
2021-07-21 22:30rafarodaIssue Monitored: rafaroda
2021-07-21 22:31rafarodaRelationship addedrelated to 0044911
2021-07-30 09:41adrianromeroStatusnew => acknowledged
2021-08-03 14:39adrianromeroAssigned ToRetail => ranjith_qualiantech_com
2021-08-03 14:39adrianromeroStatusacknowledged => scheduled
2021-08-04 14:31hgbotNote Added: 0130974
2021-08-06 12:25adrianromeroRelationship addedrelated to 0047510
2021-08-11 09:22adrianromeroRelationship addedrelated to 0047262
2021-08-19 14:30marvintmResolution time1628546400 => 1629842400
2021-08-24 13:44marvintmNote Deleted: 0130974
2021-08-24 13:44hgbotNote Added: 0131321
2021-08-27 07:22marvintmResolution time1629842400 => 1630620000
2021-08-27 11:38hgbotNote Added: 0131401
2021-09-01 07:55hgbotResolutionopen => fixed
2021-09-01 07:55hgbotStatusscheduled => closed
2021-09-01 07:55hgbotFixed in Version => RR21Q4
2021-09-01 07:55hgbotNote Added: 0131467
2021-09-01 07:55hgbotNote Added: 0131468

Notes
(0131321)
hgbot   
2021-08-24 13:44   
Merge request closed: https://gitlab.com/openbravo/product/pmods/org.openbravo.retail.posterminal/-/merge_requests/535 [^]
(0131401)
hgbot   
2021-08-27 11:38   
Merge Request created: https://gitlab.com/openbravo/product/pmods/org.openbravo.retail.posterminal/-/merge_requests/555 [^]
(0131467)
hgbot   
2021-09-01 07:55   
Directly closing issue as related merge request is already approved.

Repository: https://gitlab.com/openbravo/product/pmods/org.openbravo.retail.posterminal [^]
Changeset: c5ae003638440f56bbb46f56892060022d36be27
Author: Ranjith S R <ranjith@qualiantech.com>
Date: 2021-09-01T05:54:49+00:00
URL: https://gitlab.com/openbravo/product/pmods/org.openbravo.retail.posterminal/-/commit/c5ae003638440f56bbb46f56892060022d36be27 [^]

Fixed ISSUE-47423: Added Preference to Load Product based on Product Price Changes
* Added Preference "WebPOS Product filter by Price"
* During incremental refresh, Product Price which are changed will be loaded
* Preference "WebPOS Product filter by Price" and "WebPOS Product filter by entities" should not set at same time

---
M src-db/database/sourcedata/AD_PREFERENCE.xml
M src-db/database/sourcedata/AD_REF_LIST.xml
M src/org/openbravo/retail/posterminal/master/Product.java
---
(0131468)
hgbot   
2021-09-01 07:55   
Merge request merged: https://gitlab.com/openbravo/product/pmods/org.openbravo.retail.posterminal/-/merge_requests/555 [^]