0042269: [Performance] ProductCharacteristicValue master data performance improvement

Openbravo Issue Tracking System - Retail Modules
View Issue Details

ID	Project	Category	View Status	Date Submitted	Last Update
0042269	Retail Modules	Web POS	public	2019-11-13 12:58	2019-12-02 07:45

Reporter	ioritzCia
Assigned To	alekosmp86
Priority	high	Severity	major	Reproducibility	always
Status	closed	Resolution	fixed
Platform		OS	30	OS Version	Ubuntu 18.04
Product Version	RR19Q1.2
Target Version		Fixed in Version	RR20Q1
Merge Request Status
Review Assigned To	guilleaer
OBNetwork customer	Gold
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency Pack	No

Summary	0042269: [Performance] ProductCharacteristicValue master data performance improvement
Description	The query generated by the ProductCharacteristicValue master data refresh either incremental or full can be improved in performance. Filtering by characteri1_.em_obpos_useonwebpos = 'Y' makes the planner to build a less efficient plan. To take into account: - Oracle: An in clause with more than 1000 elements is not possible in Oracle, a subselect would, but a subselect does not improve the plan. It would be quite strange to have more than 20 characteristics as em_obpos_useonwebpos = 'Y', even more to have more than 1000. - To check if in environments with not many characteristics this does not make it perform worse. If it leaves it the same but improves cases with many characteristics it would be a nice solution to include.
Steps To Reproduce	Set log min duration statement of the environment accordingly (typically < 200ms) to be able to see both executions in log. Log in WebPOS and check for the execution of the query Original query example: https://pastebin.com/16RFjMdi [^] Changed query example: https://pastebin.com/8ZPUFBwk [^] Plan before the change: https://explain.depesz.com/s/IO5k [^] Plan after the change: https://explain.depesz.com/s/sps9 [^]
Proposed Solution	Diff attached. Making a query to obtain the characteristic_ids its really fast and setting them in an in clause in the query improves the performance. It does not improve the timing only but also the amount of information the query requires to read either from memory or disk.
Additional Information
Tags	Performance
Relationships
Attached Files	productCharacteristicValue.diff (2,886) 2019-11-13 12:58 https://issues.openbravo.com/file_download.php?file_id=13527&type=bug

Issue History
Date Modified	Username	Field	Change
2019-11-13 12:58	ioritzCia	New Issue
2019-11-13 12:58	ioritzCia	Assigned To	=> Retail
2019-11-13 12:58	ioritzCia	File Added: productCharacteristicValue.diff
2019-11-13 12:58	ioritzCia	OBNetwork customer	=> Gold
2019-11-13 12:58	ioritzCia	Resolution time	=> 1575500400
2019-11-13 12:58	ioritzCia	Triggers an Emergency Pack	=> No
2019-11-13 12:59	ioritzCia	Tag Attached: Performance
2019-11-13 13:05	ioritzCia	Description Updated	bug_revision_view_page.php?rev_id=19686#r19686
2019-11-13 13:05	ioritzCia	Steps to Reproduce Updated	bug_revision_view_page.php?rev_id=19688#r19688
2019-11-13 13:05	ioritzCia	Description Updated	bug_revision_view_page.php?rev_id=19689#r19689
2019-11-13 13:07	ioritzCia	Proposed Solution updated
2019-11-13 13:12	ioritzCia	Description Updated	bug_revision_view_page.php?rev_id=19690#r19690
2019-11-19 15:41	alekosmp86	Assigned To	Retail => alekosmp86
2019-11-25 15:40	hgbot	Checkin
2019-11-25 15:40	hgbot	Note Added: 0115802
2019-11-25 15:40	hgbot	Status	new => resolved
2019-11-25 15:40	hgbot	Resolution	open => fixed
2019-11-25 15:40	hgbot	Fixed in SCM revision	=> http://code.openbravo.com/erp/pmods/org.openbravo.retail.posterminal/rev/df764f0c7f467276fe632582b3e84d7f727cafe7 [^]
2019-12-02 07:45	guilleaer	Review Assigned To	=> guilleaer
2019-12-02 07:45	guilleaer	Status	resolved => closed
2019-12-02 07:45	guilleaer	Fixed in Version	=> RR20Q1

Notes