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

View Issue Details[ Jump to Notes ] [ Issue History ] [ Print ]
ID
0043587
TypeCategorySeverityReproducibilityDate SubmittedLast Update
defect[Retail Modules] Web POScriticalhave not tried2020-03-26 10:412020-04-16 18:50
ReporterALopeteguiView Statuspublic 
Assigned Toranjith_qualiantech_com 
PriorityurgentResolutionfixedFixed in VersionRR20Q3
StatusclosedFix in branchFixed in SCM revision
ProjectionnoneETAnoneTarget Version
OSAnyDatabaseAnyJava version
OS VersionDatabase versionAnt version
Product VersionSCM revision 
Merge Request Status
Review Assigned Tomarvintm
OBNetwork customerGold
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary

0043587: Master data load pagination with offset needs too much memory

DescriptionThe 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.
Steps To ReproduceHaving 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.
Proposed SolutionThe 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
  
TagsPerformance
Attached Filesdiff file icon paginationbyidprodcharvalue.diff [^] (1,123 bytes) 2020-03-26 13:09 [Show Content]

- Relationships [ Relation Graph ] [ Dependency Graph ]
related to defect 0043543 closedprakashmurugesan88 ProductCharacteristicValue does not finish on incremental 

-  Notes
(0118833)
marvintm (viewer)
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 (developer)
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 (viewer)
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 (developer)
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 (developer)
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 (developer)
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
---

- Issue History
Date Modified Username Field Change
2020-03-26 10:41 ALopetegui New Issue
2020-03-26 10:41 ALopetegui Assigned To => Retail
2020-03-26 10:41 ALopetegui OBNetwork customer => OBPS
2020-03-26 10:41 ALopetegui Resolution time => 1586383200
2020-03-26 10:41 ALopetegui Triggers an Emergency Pack => No
2020-03-26 10:41 ALopetegui Tag Attached: Performance
2020-03-26 10:43 guillermogil OBNetwork customer OBPS => Gold
2020-03-26 10:43 martinsdan Issue Monitored: martinsdan
2020-03-26 10:44 ALopetegui Summary Master data load pagination with offset needs needs too much memory => Master data load pagination with offset needs too much memory
2020-03-26 10:58 ALopetegui Type design defect => defect
2020-03-26 13:09 marvintm File Added: paginationbyidprodcharvalue.diff
2020-03-26 13:10 marvintm Note Added: 0118833
2020-03-31 16:31 alekosmp86 Assigned To Retail => alekosmp86
2020-03-31 16:31 alekosmp86 Status new => scheduled
2020-04-08 08:35 guillermogil Relationship added related to 0043543
2020-04-08 13:30 guilleaer Assigned To alekosmp86 => Retail
2020-04-13 10:52 ranjith_qualiantech_com Assigned To Retail => ranjith_qualiantech_com
2020-04-14 02:48 hgbot Checkin
2020-04-14 02:48 hgbot Note Added: 0119142
2020-04-14 08:47 guilleaer Status scheduled => resolved
2020-04-14 08:47 guilleaer Resolution open => fixed
2020-04-14 13:40 migueldejuana Note Added: 0119155
2020-04-14 13:40 migueldejuana Status resolved => new
2020-04-14 13:40 migueldejuana Resolution fixed => open
2020-04-15 18:26 ranjith_qualiantech_com Status new => scheduled
2020-04-16 06:58 hgbot Checkin
2020-04-16 06:58 hgbot Note Added: 0119201
2020-04-16 07:44 hgbot Checkin
2020-04-16 07:44 hgbot Note Added: 0119202
2020-04-16 07:45 hgbot Checkin
2020-04-16 07:45 hgbot Note Added: 0119203
2020-04-16 10:00 ranjith_qualiantech_com Status scheduled => resolved
2020-04-16 10:00 ranjith_qualiantech_com Resolution open => fixed
2020-04-16 18:50 marvintm Review Assigned To => marvintm
2020-04-16 18:50 marvintm Status resolved => closed
2020-04-16 18:50 marvintm Fixed in Version => RR20Q3

Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker