Openbravo Issue Tracking System - Retail Modules
View Issue Details
0043587Retail ModulesWeb POSpublic2020-03-26 10:412020-04-16 18:50
ALopetegui 
ranjith_qualiantech_com 
urgentcriticalhave not tried
closedfixed 
5
 
RR20Q3 
marvintm
No
0043587: Master data load pagination with offset needs too much memory
The master data pagination uses offset method to make a pagination. This method with high number of rows makes the query slower in each pagination if the data to load does't fit in memory cache.
offset instructs the databases skip the first N results of a query. However, the database must still fetch these rows from the disk and bring them in order before it can send the following ones.
More info in the links:
https://use-the-index-luke.com/no-offset [^]
https://use-the-index-luke.com/sql/partial-results/fetch-next-page [^]

In one client which needs to charge 2Million of product characteristics value, needs 75 minutes to finish the load, caused because of the combination of insufficient memory and offset pagination issue. Because it takes more time for each pagination.
Having a big amount of master data, por example 500.000 products, and a database server with 8gb RAM, postgres with 2gb shared buffers.

Do full login and check the logs, the pagination will take more time for each offset or will need at least to use more memory.
The proposed solution is also explained in the link above.
Instead of doing offset, doing Seek Method which consists on using the values of the previous page as a delimiter.

Offset method: Explain example: https://explain.depesz.com/s/k2te [^]
  Select M_Product_Ch_Value_ID.*
  from M_Product_Ch_Value_ID
  order by m_product_id
  limit 30000 offset 60000

Seek Method: Explain example: https://explain.depesz.com/s/jRF6 [^]
  Select M_Product_Ch_Value_ID.*
  from M_Product_Ch_Value_ID
  where m_product_id > last_page_product_id
  order by m_product_id
  limit 30000
  
Performance
related to defect 0043543 closed prakashmurugesan88 ProductCharacteristicValue does not finish on incremental 
diff paginationbyidprodcharvalue.diff (1,123) 2020-03-26 13:09
https://issues.openbravo.com/file_download.php?file_id=14218&type=bug
Issue History
2020-03-26 10:41ALopeteguiNew Issue
2020-03-26 10:41ALopeteguiAssigned To => Retail
2020-03-26 10:41ALopeteguiResolution time => 1586383200
2020-03-26 10:41ALopeteguiTriggers an Emergency Pack => No
2020-03-26 10:41ALopeteguiTag Attached: Performance
2020-03-26 10:43martinsdanIssue Monitored: martinsdan
2020-03-26 10:44ALopeteguiSummaryMaster data load pagination with offset needs needs too much memory => Master data load pagination with offset needs too much memory
2020-03-26 10:58ALopeteguiTypedesign defect => defect
2020-03-26 13:09marvintmFile Added: paginationbyidprodcharvalue.diff
2020-03-26 13:10marvintmNote Added: 0118833
2020-03-31 16:31alekosmp86Assigned ToRetail => alekosmp86
2020-03-31 16:31alekosmp86Statusnew => scheduled
2020-04-08 08:35guillermogilRelationship addedrelated to 0043543
2020-04-08 13:30guilleaerAssigned Toalekosmp86 => Retail
2020-04-13 10:52ranjith_qualiantech_comAssigned ToRetail => ranjith_qualiantech_com
2020-04-14 02:48hgbotCheckin
2020-04-14 02:48hgbotNote Added: 0119142
2020-04-14 08:47guilleaerStatusscheduled => resolved
2020-04-14 08:47guilleaerResolutionopen => fixed
2020-04-14 13:40migueldejuanaNote Added: 0119155
2020-04-14 13:40migueldejuanaStatusresolved => new
2020-04-14 13:40migueldejuanaResolutionfixed => open
2020-04-15 18:26ranjith_qualiantech_comStatusnew => scheduled
2020-04-16 06:58hgbotCheckin
2020-04-16 06:58hgbotNote Added: 0119201
2020-04-16 07:44hgbotCheckin
2020-04-16 07:44hgbotNote Added: 0119202
2020-04-16 07:45hgbotCheckin
2020-04-16 07:45hgbotNote Added: 0119203
2020-04-16 10:00ranjith_qualiantech_comStatusscheduled => resolved
2020-04-16 10:00ranjith_qualiantech_comResolutionopen => fixed
2020-04-16 18:50marvintmReview Assigned To => marvintm
2020-04-16 18:50marvintmStatusresolved => closed
2020-04-16 18:50marvintmFixed in Version => RR20Q3

Notes
(0118833)
marvintm   
2020-03-26 13:10   
Attached a patch with a fix candidate implementation. It needs to be throughly tested, both functionally and in terms of performance improvement.
(0119142)
hgbot   
2020-04-14 02:48   
Repository: erp/pmods/org.openbravo.retail.posterminal
Changeset: c5a833693eb1039678c043f66bb1c42f4ef3bf45
Author: Ranjith S R <ranjith <at> qualiantech.com>
Date: Tue Apr 14 06:18:37 2020 +0530
URL: http://code.openbravo.com/erp/pmods/org.openbravo.retail.posterminal/rev/c5a833693eb1039678c043f66bb1c42f4ef3bf45 [^]

Related to issue-43587 : Added Pagination for Product Characteristic Value

---
M src/org/openbravo/retail/posterminal/master/ProductCharacteristicValue.java
M web/org.openbravo.retail.posterminal/js/model/productcharacteristicvalue.js
---
(0119155)
migueldejuana   
2020-04-14 13:40   
We should add in app/model/masterdata/ProductCharacteristicValueModel.js this:

 this.setPaginationById(true);

as we do in app/model/masterdata/ProductPriceModel.js

and ensure that it improves the performance (it was not done)
(0119201)
hgbot   
2020-04-16 06:58   
Repository: erp/pmods/org.openbravo.retail.posterminal
Changeset: 32400593eae76b79f82b1e984ba8a25c33c729e3
Author: Ranjith S R <ranjith <at> qualiantech.com>
Date: Thu Apr 16 10:27:59 2020 +0530
URL: http://code.openbravo.com/erp/pmods/org.openbravo.retail.posterminal/rev/32400593eae76b79f82b1e984ba8a25c33c729e3 [^]

Related to issue-43587 : Added Pagination for MasterData Models

---
M src/org/openbravo/retail/posterminal/master/BPSetLine.java
M src/org/openbravo/retail/posterminal/master/DiscountBusinessPartnerSet.java
M src/org/openbravo/retail/posterminal/master/DiscountFilterBusinessPartner.java
M src/org/openbravo/retail/posterminal/master/DiscountFilterBusinessPartnerGroup.java
M src/org/openbravo/retail/posterminal/master/DiscountFilterCharacteristic.java
M src/org/openbravo/retail/posterminal/master/DiscountFilterProduct.java
M src/org/openbravo/retail/posterminal/master/DiscountFilterProductCategory.java
M src/org/openbravo/retail/posterminal/master/DiscountFilterRole.java
M src/org/openbravo/retail/posterminal/master/OfferPriceList.java
M src/org/openbravo/retail/posterminal/master/PriceList.java
M src/org/openbravo/retail/posterminal/master/ProductBOM.java
M src/org/openbravo/retail/posterminal/master/ProductServiceLinked.java
M web/org.openbravo.retail.posterminal/app/model/masterdata/BPSetLineModel.js
M web/org.openbravo.retail.posterminal/app/model/masterdata/PriceListModel.js
M web/org.openbravo.retail.posterminal/app/model/masterdata/ProductBOMModel.js
M web/org.openbravo.retail.posterminal/app/model/masterdata/ProductCharacteristicValueModel.js
M web/org.openbravo.retail.posterminal/app/model/masterdata/ProductServiceLinkedModel.js
M web/org.openbravo.retail.posterminal/app/model/masterdata/discount/DiscountFilterBusinessPartnerGroupModel.js
M web/org.openbravo.retail.posterminal/app/model/masterdata/discount/DiscountFilterBusinessPartnerModel.js
M web/org.openbravo.retail.posterminal/app/model/masterdata/discount/DiscountFilterBusinessPartnerSetModel.js
M web/org.openbravo.retail.posterminal/app/model/masterdata/discount/DiscountFilterCharacteristicModel.js
M web/org.openbravo.retail.posterminal/app/model/masterdata/discount/DiscountFilterPriceListModel.js
M web/org.openbravo.retail.posterminal/app/model/masterdata/discount/DiscountFilterProductCategoryModel.js
M web/org.openbravo.retail.posterminal/app/model/masterdata/discount/DiscountFilterProductModel.js
M web/org.openbravo.retail.posterminal/app/model/masterdata/discount/DiscountFilterRoleModel.js
M web/org.openbravo.retail.posterminal/js/model/pricelist.js
M web/org.openbravo.retail.posterminal/js/model/product-servicelinked.js
---
(0119202)
hgbot   
2020-04-16 07:44   
Repository: erp/pmods/org.openbravo.retail.multiupc
Changeset: 8dce22a3a0a739456621992acb7e0e5346362b44
Author: Ranjith S R <ranjith <at> qualiantech.com>
Date: Thu Apr 16 11:14:44 2020 +0530
URL: http://code.openbravo.com/erp/pmods/org.openbravo.retail.multiupc/rev/8dce22a3a0a739456621992acb7e0e5346362b44 [^]

Related to issue-43587 : Added Pagination for MultiUPC Model

---
M src/org/openbravo/retail/multiupc/MultiUPC.java
M web/org.openbravo.retail.multiupc/js/multiupc.js
---
(0119203)
hgbot   
2020-04-16 07:45   
Repository: erp/pmods/org.openbravo.retail.complementary
Changeset: 4fe4d57655481bfaf47a7b61223f6c26d9ae6d40
Author: Ranjith S R <ranjith <at> qualiantech.com>
Date: Thu Apr 16 11:14:51 2020 +0530
URL: http://code.openbravo.com/erp/pmods/org.openbravo.retail.complementary/rev/4fe4d57655481bfaf47a7b61223f6c26d9ae6d40 [^]

Related to issue-43587 : Added Pagination for Complementary Model

---
M src/org/openbravo/retail/complementary/master/ComplementaryProducts.java
M web/org.openbravo.retail.complementary/app/model/masterdata/ComplementaryProductsModel.js
M web/org.openbravo.retail.complementary/js/modelComplementaryProduct.js
---