Anonymous | Login
Project:
RSS
  
News | My View | View Issues | Roadmap | Summary

View Issue Details[ Jump to Notes ] [ Issue History ] [ Print ]
ID
0043085
TypeCategorySeverityReproducibilityDate SubmittedLast Update
defect[Retail Modules] Web POSmajorhave not tried2020-01-31 12:362020-02-14 20:38
ReporterALopeteguiView Statuspublic 
Assigned Toranjith_qualiantech_com 
PriorityhighResolutionfixedFixed in VersionRR20Q2
StatusclosedFix in branchFixed in SCM revision6ef57085849a
ProjectionnoneETAnoneTarget Version
OSAnyDatabaseAnyJava version
OS VersionDatabase versionAnt version
Product VersionSCM revision 
Review Assigned Toguilleaer
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary

0043085: 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 [^]
TagsPerformance
Attached Files

- Relationships [ Relation Graph ] [ Dependency Graph ]
causes defect 0043828 closedranjith_qualiantech_com [20Q2] User access and visibility management per terminal via "POS Terminal Access" subtab no longer works 

-  Notes
(0117582)
hgbot (developer)
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 (reporter)
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 (developer)
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 (manager)
2020-02-14 20:38

 reclosed after verify additional commit

- Issue History
Date Modified Username Field Change
2020-01-31 12:36 ALopetegui New Issue
2020-01-31 12:36 ALopetegui Assigned To => Retail
2020-01-31 12:36 ALopetegui Triggers an Emergency Pack => No
2020-02-04 14:11 ranjith_qualiantech_com Assigned To Retail => ranjith_qualiantech_com
2020-02-04 14:13 ranjith_qualiantech_com Status new => scheduled
2020-02-05 15:19 guilleaer Resolution time => 1582153200
2020-02-06 10:36 ALopetegui Tag Attached: Performance
2020-02-10 07:14 hgbot Checkin
2020-02-10 07:14 hgbot Note Added: 0117582
2020-02-10 07:14 hgbot Status scheduled => resolved
2020-02-10 07:14 hgbot Resolution open => fixed
2020-02-10 07:14 hgbot Fixed in SCM revision => http://code.openbravo.com/erp/pmods/org.openbravo.retail.posterminal/rev/c027ae221f7e4043a27c4c8c1380835d53413c4e [^]
2020-02-10 09:12 guilleaer Review Assigned To => guilleaer
2020-02-10 09:12 guilleaer Status resolved => closed
2020-02-10 09:12 guilleaer Fixed in Version => RR20Q2
2020-02-12 10:16 ALopetegui Note Added: 0117679
2020-02-13 06:41 hgbot Checkin
2020-02-13 06:41 hgbot Note Added: 0117714
2020-02-13 06:41 hgbot Status closed => resolved
2020-02-13 06:41 hgbot Fixed in SCM revision http://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:38 guilleaer Note Added: 0117819
2020-02-14 20:38 guilleaer Status resolved => closed
2020-04-23 08:22 ranjith_qualiantech_com Relationship added causes 0043828

Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker