Openbravo Issue Tracking System - Modules
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0040615ModulesAdvanced Warehouse Operationspublic2019-04-15 12:292019-05-31 14:46
Reporterjoniturralde93 
Assigned Tovmromanos 
PriorityurgentSeveritymajorReproducibilityalways
StatusclosedResolutionunable to reproduce 
PlatformOS5OS Version
Product Version 
Target VersionFixed in Version 
Merge Request Status
Regression date
Regression introduced by commit
Regression level
Review Assigned Tovmromanos
Support ticket
OBNetwork customer
Regression introduced in release
Summary0040615: Task loading can be very slow [Frontend]
DescriptionPerformance problem when loading tasks.
- 1100 products (160 of them with assigned attribute sets (a lot of possible values))
- 163 attributes

In local machine, the requests lasts for around 30 seconds.
Attached both executed queries in backend and explain analyze for each one.
Steps To Reproduce- Login/refresh awo frontend, or click menu option "Refresh"
Proposed Solution
Additional Information
TagsNo tags attached.
Relationships
related to defect 0039438 closed guilleaer Modules [AWOFE]Performance loading tasks is very poor 
related to defect 0040865 closed AtulOpenbravo Openbravo ERP Set VOLATILITY LEVEL for Database functions 
Attached Files? Query1 (10,406) 2019-04-15 12:29
https://issues.openbravo.com/file_download.php?file_id=12863&type=bug
? Query2 (10,810) 2019-04-15 12:31
https://issues.openbravo.com/file_download.php?file_id=12864&type=bug
? explain1 (30,543) 2019-04-15 12:31
https://issues.openbravo.com/file_download.php?file_id=12865&type=bug
? explain2 (30,765) 2019-04-15 12:31
https://issues.openbravo.com/file_download.php?file_id=12866&type=bug
png explain1.png (332,635) 2019-04-15 12:31
https://issues.openbravo.com/file_download.php?file_id=12867&type=bug
png explain2.png (312,940) 2019-04-15 12:31
https://issues.openbravo.com/file_download.php?file_id=12868&type=bug
Issue History
Date ModifiedUsernameFieldChange
2019-04-15 12:29joniturralde93New Issue
2019-04-15 12:29joniturralde93Assigned To => Triage Finance
2019-04-15 12:29joniturralde93File Added: Query1
2019-04-15 12:29joniturralde93Resolution time => 1554501600
2019-04-15 12:31joniturralde93File Added: Query2
2019-04-15 12:31joniturralde93File Added: explain1
2019-04-15 12:31joniturralde93File Added: explain2
2019-04-15 12:31joniturralde93File Added: explain1.png
2019-04-15 12:31joniturralde93File Added: explain2.png
2019-04-15 12:31joniturralde93Description Updatedbug_revision_view_page.php?rev_id=18629#r18629
2019-04-15 12:42SandrahuguetAssigned ToTriage Finance => guilleaer
2019-04-15 12:43joniturralde93Resolution time1554501600 => 1557093600
2019-05-08 13:28marvintmResolution time1557093600 => 1558735200
2019-05-27 10:58marvintmResolution time1558735200 => 1559599200
2019-05-30 12:38vmromanosAssigned Toguilleaer => vmromanos
2019-05-30 12:38vmromanosStatusnew => acknowledged
2019-05-31 12:20vmromanosStatusacknowledged => scheduled
2019-05-31 12:20vmromanosRelationship addedrelated to 0039438
2019-05-31 13:08vmromanosRelationship addedrelated to 0040865
2019-05-31 14:29hgbotCheckin
2019-05-31 14:29hgbotNote Added: 0112298
2019-05-31 14:45vmromanosReview Assigned To => vmromanos
2019-05-31 14:45vmromanosNote Added: 0112300
2019-05-31 14:45vmromanosStatusscheduled => closed
2019-05-31 14:46vmromanosResolutionopen => unable to reproduce
2022-09-06 17:18caristuCategoryAdvance Warehouse Operations => Advanced Warehouse Operations

Notes
(0112298)
hgbot   
2019-05-31 14:29   
Repository: erp/pmods/org.openbravo.warehouse.advancedwarehouseoperations
Changeset: 766c9914b537b9de245df97d1182d17461725afa
Author: Víctor Martínez Romanos <victor.martinez <at> openbravo.com>
Date: Fri May 31 14:24:40 2019 +0200
URL: http://code.openbravo.com/erp/pmods/org.openbravo.warehouse.advancedwarehouseoperations/rev/766c9914b537b9de245df97d1182d17461725afa [^]

Related to issue 40615: Added partial index over AD_User_ID
This partial index will help to search for the available tasks by a given user.

The index should be small as it's only working for tasks in available status (which should be just a few in a normal environment).

Note that this index only works as designed in Postgres. In Oracle it will automatically be transformed to a (partial) index over the AD_User_ID, which is also good enough to improve the queries for getting the user's tasks.

---
M src-db/database/model/tables/OBAWO_TASK.xml
---
(0112300)
vmromanos   
2019-05-31 14:45   
Issue rejected: it is working fine in AWO tip (to be published in 3.0PR19Q3).

The two issues related are the ones that actually fixes it. The main problem was in the usage of OBAWO_GETCHILDTASKMAXPRIORITY db function, that is efficient enough for few records, but which adds a heavy performance penalty when it's run for hundreds of records in the same transaction.

From one side the 0039438 changes the db function implementation so it can properly works with both Picking and Reception/Issue lists.

From another side, as part of 3.0PR19Q3, we have redefined the volatility of every db function available in Openbravo (Core and external modules). As part of this exercise we changed this function's volatility (and actually many other functions more) from VOLATILE to STABLE, which adds a very important performance improvement in this scenario. See 0040865 for details.

Only by applying the changesets for issue 0039438, and by redefining the OBAWO_GETCHILDTASKMAXPRIORITY volatility to STABLE directly in PG, the time to load in the customer instance goes from more than 50 seconds to few milliseconds.
This is exactly what should be manually done in case an upgrade to 3.0PR19Q3 is not possible. Note that in this case the STABLE volatility would be lost in case of further database updates from source code.


Finally, as part of the review, we have added a new partial index in the OBAWO_Task table over the AD_USER_ID, which will help to get faster the available tasks for a given user. Note that this index is actually not needed for fixing this concrete issue, but might improve performance in other related flows.