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

View Issue DetailsJump to Notes ] Issue History ] Print ]
ID
0043081
TypeCategorySeverityReproducibilityDate SubmittedLast Update
defect[Retail Modules] Web POSmajorhave not tried2020-01-31 11:532020-02-06 10:36
ReporterALopeteguiView Statuspublic 
Assigned ToRetail 
PriorityhighResolutionopenFixed in Version
StatusnewFix in branchFixed in SCM revision
ProjectionnoneETAnoneTarget Version
OSAnyDatabaseAnyJava version
OS VersionDatabase versionAnt version
Product VersionSCM revision 
Review Assigned To
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary

0043081: In the Login, preference load has bad performance

DescriptionIn the login, the preferences are loaded filtering by VisibleAt_Org_ID, VisibleAt_Role_ID and VisibleAt_Client_ID.

However, there is not any index in those columns, and the query makes a sequential scan of all ad_preference table, and with more than 100K preferences could be a problem.

This is the explain plan:
https://explain.depesz.com/s/HYv1 [^]
Steps To ReproduceWith more than 100K preferences, do login and check the preferences load query.
This is the query:
select adpreferen0_.AD_Preference_ID as AD_Prefe1_81_, adpreferen0_.AD_Client_ID as AD_Clien2_81_, adpreferen0_.AD_Org_ID as AD_Org_I3_81_, adpreferen0_.IsActive as IsActive4_81_, adpreferen0_.Created as Created5_81_, adpreferen0_.CreatedBy as CreatedB6_81_, adpreferen0_.Updated as Updated7_81_, adpreferen0_.UpdatedBy as UpdatedB8_81_, adpreferen0_.AD_Window_ID as AD_Windo9_81_, adpreferen0_.AD_User_ID as AD_User10_81_, adpreferen0_.Attribute as Attribu11_81_, adpreferen0_.Value as Value12_81_, adpreferen0_.Property as Propert13_81_, adpreferen0_.IsPropertyList as IsPrope14_81_, adpreferen0_.VisibleAt_Client_ID as Visible15_81_, adpreferen0_.VisibleAt_Org_ID as Visible16_81_, adpreferen0_.VisibleAt_Role_ID as Visible17_81_, adpreferen0_.Selected as Selecte18_81_, adpreferen0_.AD_Module_ID as AD_Modu19_81_, adpreferen0_.Inherited_From as Inherit20_81_ from AD_Preference adpreferen0_ where (adpreferen0_.VisibleAt_Client_ID='XXX' or coalesce(adpreferen0_.VisibleAt_Client_ID, 'XX')='0') and (adpreferen0_.VisibleAt_Role_ID='XX' or adpreferen0_.VisibleAt_Role_ID is null) and (coalesce(adpreferen0_.VisibleAt_Org_ID, '0') in ('XX' , 'XX' ) and (adpreferen0_.AD_User_ID='XX' or adpreferen0_.AD_User_ID is null) and (adpreferen0_.AD_Org_ID in ('XX' , '0' , )) and (adpreferen0_.AD_Client_ID in ('XX' , '0')) and adpreferen0_.IsActive='Y' order by adpreferen0_.AD_Preference_ID

Proposed SolutionIn the analyzed customer, the way to organize preferences is with VisibleAt_Role_ID column, so the best way to improve the query is creating an index in that column:

CREATE INDEX <index-name> ON AD_PREFERENCE (VisibleAt_Role_ID)

This is the explain plan after the index:
https://explain.depesz.com/s/SKyt [^]

Note: If can be other clients with other ways to split preferences, also could be interesting to create indexes on columns VisibleAt_Org_ID and VisibleAt_Client_ID.
TagsPerformance
Attached Files

- Relationships Relation Graph ] Dependency Graph ]

-  Notes
There are no notes attached to this issue.

- Issue History
Date Modified Username Field Change
2020-01-31 11:53 ALopetegui New Issue
2020-01-31 11:53 ALopetegui Assigned To => Retail
2020-01-31 11:53 ALopetegui Triggers an Emergency Pack => No
2020-02-06 10:36 ALopetegui Tag Attached: Performance


Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker