Anonymous | Login
Project:
RSS
  
News | My View | View Issues | Roadmap | Summary

View Issue DetailsJump to Notes ] Issue History ] Print ]
ID
0047423
TypeCategorySeverityReproducibilityDate SubmittedLast Update
defect[Retail Modules] Web POSmajoralways2021-07-21 12:002021-07-21 22:31
ReporteregoitzView Statuspublic 
Assigned ToRetail 
PrioritynormalResolutionopenFixed in Version
StatusnewFix in branchFixed in SCM revision
ProjectionnoneETAnoneTarget Version
OSAnyDatabaseAnyJava version
OS VersionDatabase versionAnt version
Product VersionSCM revision 
Review Assigned To
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary

0047423: 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'
)
TagsNOR
Attached Files

- Relationships Relation Graph ] Dependency Graph ]
related to defect 0044911 closedranjith_qualiantech_com Incremental refresh of Product cannot use index on updated 

-  Notes
There are no notes attached to this issue.

- Issue History
Date Modified Username Field Change
2021-07-21 12:00 egoitz New Issue
2021-07-21 12:00 egoitz Assigned To => Retail
2021-07-21 12:00 egoitz Triggers an Emergency Pack => No
2021-07-21 22:30 rafaroda Tag Attached: NOR
2021-07-21 22:30 rafaroda Issue Monitored: rafaroda
2021-07-21 22:31 rafaroda Relationship added related to 0044911


Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker