0036312: Improve performance of ProducePrice masterdata loading

Openbravo Issue Tracking System - Retail Modules

View Issue Details

Project

Category

View Status

Date Submitted

Last Update

0036312

Retail Modules

Web POS

public

2017-06-22 12:20

2017-06-27 09:03

Reporter

shuehner

Assigned To

migueldejuana

Priority

normal

Severity

major

Reproducibility

have not tried

Status

closed

Resolution

fixed

Platform

OS Version

Product Version

Target Version

Fixed in Version

RR17Q3

Merge Request Status

Review Assigned To

marvintm

OBNetwork customer

Support ticket

Regression level

Regression date

Regression introduced in release

Regression introduced by commit

Triggers an Emergency Pack

Summary

0036312: Improve performance of ProducePrice masterdata loading

Description

Main query is from ProductPrice.java

current for access of obretco_prol_product it uses the auto-created index for the unique constraint:
"obretco_prol_product_un" UNIQUE CONSTRAINT, btree (ad_client_id, m_product_id, obretco_productlist_id)

2 Changes should be done
a.) The part ad_client_id seems functionally useless. as no data can be created for this table in ad_client_id=0 and no normal uuid for products or assortment is possible to be re-used cross-client.
So extra adding ad_client_id does not change anything.

Note: Still api-change which should be done properly.

b.) That a.) the order of the remaining columns should be swapped to be
obretco_productlist_id, m_product_id

As the login query does filter by fixed value assortment_id

That change improved query time by ca. 40%
Example improvement seen:
Testing incremental one:
- without index: 5.3s
- with index 3.3s

Testing full one:
- without: 10s
- with index: 6.3s

Note: That order change makes it impossible to use the combined index for filtering by 'only' m_product_id. However this table already has another index for m_product_id which will be used.

Note:
Apart there is single column index on obretco_productlist_id which is functionally covered by the 'new' modified index from b.)
So it could be deleted.
However reading from smaller single column index is slightly faster then multi-column index -> So some testing is needed before decising if to keep or delete that index.

Apart the query has an extra ueless 2nd join to m_pricelist_version to get the m_pricelist_id column of the resultset. That property definition should be fixed to not required the extra join.

Steps To Reproduce

Proposed Solution

Additional Information

Tags

Performance

Relationships

depends on	backport	0036348	RR17Q2.2	closed	migueldejuana	Improve performance of ProducePrice masterdata loading
depends on	backport	0036349	RR17Q1.3	closed	migueldejuana	Improve performance of ProducePrice masterdata loading
related to	defect	0036327		closed	migueldejuana	Api-change: Change obretco_prol_product_un unique constraint
related to	design defect	0037025		closed	gorka_gil	Perfromance problem in incremental refresh process, with product price query