Openbravo Issue Tracking System - Retail Modules
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0043085Retail ModulesWeb POSpublic2020-01-31 12:362020-02-14 20:38
ReporterALopetegui 
Assigned Toranjith_qualiantech_com 
PriorityhighSeveritymajorReproducibilityhave not tried
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target VersionFixed in VersionRR20Q2 
Merge Request Status
Review Assigned Toguilleaer
OBNetwork customerGold
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0043085: LoginUtilsServlet makes an inefficient query to get user images
DescriptionThe query to get the users which can be login in the webpos and also the query to get the users for approvals, makes several sub select in the from clause. This query could be improved joining the tables instead of doing exists.

Those queries are launched quite often, mainly the query for the login users.
The queries are done in loginUtilsSerlet class, o getUserImages function:

[1]https://code.openbravo.com/erp/pmods/org.openbravo.retail.posterminal/file/tip/src/org/openbravo/retail/posterminal/LoginUtilsServlet.java#l132 [^]
[1]Explain plan: https://explain.depesz.com/s/2pTJ [^]

[2]https://code.openbravo.com/erp/pmods/org.openbravo.retail.posterminal/file/tip/src/org/openbravo/retail/posterminal/LoginUtilsServlet.java#l151 [^]
[2] Explain plan: https://explain.depesz.com/s/o9jq [^]

Steps To ReproduceThe first query is launched loading the webpos initial page.
The second query is launched when the user needs an approval for other user in any action.

The queries individually are not very bad, but with high concurrency could be a problem.
Proposed SolutionRewrite the hql query in order to avoid the exists subselects and put all the tables in the from clause:
These are the explain plan with the query adapted, the improvement is almost the 50%.

[1] https://explain.depesz.com/s/GycY [^]

[2] https://explain.depesz.com/s/YRA2 [^]
Additional Information
TagsPerformance
Relationships
causes defect 0043828 closed ranjith_qualiantech_com [20Q2] User access and visibility management per terminal via "POS Terminal Access" subtab no longer works 
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2020-01-31 12:36ALopeteguiNew Issue
2020-01-31 12:36ALopeteguiAssigned To => Retail
2020-01-31 12:36ALopeteguiOBNetwork customer => Gold
2020-01-31 12:36ALopeteguiTriggers an Emergency Pack => No
2020-02-04 14:11ranjith_qualiantech_comAssigned ToRetail => ranjith_qualiantech_com
2020-02-04 14:13ranjith_qualiantech_comStatusnew => scheduled
2020-02-05 15:19guilleaerResolution time => 1582153200
2020-02-06 10:36ALopeteguiTag Attached: Performance
2020-02-10 07:14hgbotCheckin
2020-02-10 07:14hgbotNote Added: 0117582
2020-02-10 07:14hgbotStatusscheduled => resolved
2020-02-10 07:14hgbotResolutionopen => fixed
2020-02-10 07:14hgbotFixed in SCM revision => http://code.openbravo.com/erp/pmods/org.openbravo.retail.posterminal/rev/c027ae221f7e4043a27c4c8c1380835d53413c4e [^]
2020-02-10 09:12guilleaerReview Assigned To => guilleaer
2020-02-10 09:12guilleaerStatusresolved => closed
2020-02-10 09:12guilleaerFixed in Version => RR20Q2
2020-02-12 10:16ALopeteguiNote Added: 0117679
2020-02-13 06:41hgbotCheckin
2020-02-13 06:41hgbotNote Added: 0117714
2020-02-13 06:41hgbotStatusclosed => resolved
2020-02-13 06:41hgbotFixed in SCM revisionhttp://code.openbravo.com/erp/pmods/org.openbravo.retail.posterminal/rev/c027ae221f7e4043a27c4c8c1380835d53413c4e [^] => http://code.openbravo.com/erp/pmods/org.openbravo.retail.posterminal/rev/6ef57085849a2568a712ed4fbfec1c529984b10e [^]
2020-02-14 20:38guilleaerNote Added: 0117819
2020-02-14 20:38guilleaerStatusresolved => closed
2020-04-23 08:22ranjith_qualiantech_comRelationship addedcauses 0043828

Notes
(0117582)
hgbot   
2020-02-10 07:14   
Repository: erp/pmods/org.openbravo.retail.posterminal
Changeset: c027ae221f7e4043a27c4c8c1380835d53413c4e
Author: Ranjith S R <ranjith <at> qualiantech.com>
Date: Mon Feb 10 11:44:40 2020 +0530
URL: http://code.openbravo.com/erp/pmods/org.openbravo.retail.posterminal/rev/c027ae221f7e4043a27c4c8c1380835d53413c4e [^]

Fixed issue 43085 : Optimized UserImage query by removing subquery

* Updated UserImage query by removing preference, TerminalAccess RoleOrg Subquery

---
M src/org/openbravo/retail/posterminal/LoginUtilsServlet.java
---
(0117679)
ALopetegui   
2020-02-12 10:16   
ad_role table is twice in from clause

It can be fixed instead of doing:
userRoles.role.forPortalUsers = false

doing:
role.forPortalUsers = false
(0117714)
hgbot   
2020-02-13 06:41   
Repository: erp/pmods/org.openbravo.retail.posterminal
Changeset: 6ef57085849a2568a712ed4fbfec1c529984b10e
Author: Ranjith S R <ranjith <at> qualiantech.com>
Date: Thu Feb 13 11:11:03 2020 +0530
URL: http://code.openbravo.com/erp/pmods/org.openbravo.retail.posterminal/rev/6ef57085849a2568a712ed4fbfec1c529984b10e [^]

Fixed issue 43085 : Optimized UserImage query by removing subquery

* Update Query by removing additional joins in userRoles

---
M src/org/openbravo/retail/posterminal/LoginUtilsServlet.java
---
(0117819)
guilleaer   
2020-02-14 20:38   
reclosed after verify additional commit