Openbravo Issue Tracking System - Retail Modules |
| View Issue Details |
|
| ID | Project | Category | View Status | Date Submitted | Last Update |
| 0043587 | Retail Modules | Web POS | public | 2020-03-26 10:41 | 2020-04-16 18:50 |
|
| Reporter | ALopetegui | |
| Assigned To | ranjith_qualiantech_com | |
| Priority | urgent | Severity | critical | Reproducibility | have not tried |
| Status | closed | Resolution | fixed | |
| Platform | | OS | 5 | OS Version | |
| Product Version | | |
| Target Version | | Fixed in Version | RR20Q3 | |
| Merge Request Status | |
| Review Assigned To | marvintm |
| OBNetwork customer | Gold |
| Support ticket | |
| Regression level | |
| Regression date | |
| Regression introduced in release | |
| Regression introduced by commit | |
| Triggers an Emergency Pack | No |
|
| Summary | 0043587: Master data load pagination with offset needs too much memory |
| Description | 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. |
| Steps To Reproduce | 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.
|
| Proposed Solution | 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
|
| Additional Information | |
| Tags | Performance |
| Relationships | | related to | defect | 0043543 | | closed | prakashmurugesan88 | ProductCharacteristicValue does not finish on incremental |
|
| Attached Files |  paginationbyidprodcharvalue.diff (1,123) 2020-03-26 13:09
https://issues.openbravo.com/file_download.php?file_id=14218&type=bug |
|
| 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 |
|
Notes |
|
|
|
|
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
|
|
|
|
|
|
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
|
|
|
|
|
(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
---
|
|