Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0025466Openbravo ERPA. Platformpublic2014-01-13 11:472014-02-12 18:29
Reporterjecharri 
Assigned Toalostale 
PriorityurgentSeveritymajorReproducibilityalways
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Versionpi 
Target Version3.0PR14Q2Fixed in Version3.0PR14Q2 
Merge Request Status
Review Assigned Toshankarb
OBNetwork customerOBPS
Web browserGoogle Chrome
ModulesCore
Support ticket23706
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0025466: Performace problem during the login
DescriptionPerformace problem during the login

Due to solve issue https://issues.openbravo.com/view.php?id=24962 [^] now we are having this issue. The problem is that now the query:
StringBuilder hql = new StringBuilder();
    hql.append("select count(*)");
    hql.append(" from ADSession s ");
    hql.append(" where s.loginStatus='F'");
    hql.append(" and s.username = :name");
    hql.append(" and s.creationDate > (select coalesce(max(s1.creationDate), s.creationDate-1)");
    hql.append(" from ADSession s1");
    hql.append(" where s1.username = s.username");
    hql.append(" and s1.loginStatus!='F')");
    Query q = OBDal.getInstance().getSession().createQuery(hql.toString());
    q.setParameter("name", userName);

is executed in file "src/org/openbravo/base/secureApp/UserLock.java" and that query has bad performance
Steps To Reproduce-confiure parameter "login.trial.user.lock"

Verify that it takes a long time to login for example if your table ad_session is big (200MB)
Proposed Solution
Additional Information
TagsPerformance
Relationships
related to defect 0038651 closed alostale problems in user locking implementation 
related to defect 0044414 new Triage Platform Base UserLock feature (delay login on wrong login) has bad performance by default 
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2014-01-13 11:47jecharriNew Issue
2014-01-13 11:47jecharriAssigned To => AugustoMauch
2014-01-13 11:47jecharriWeb browser => Google Chrome
2014-01-13 11:47jecharriModules => Core
2014-01-13 11:47jecharriOBNetwork customer => Yes
2014-01-13 11:47jecharriSupport ticket => 23706
2014-01-13 11:47jecharriResolution time => 1392764400
2014-01-13 11:47jecharriTriggers an Emergency Pack => No
2014-01-14 13:34alostaleTag Attached: Performance
2014-01-15 09:01shuehnerIssue Monitored: shuehner
2014-01-27 15:30alostaleNote Added: 0063718
2014-01-27 15:30alostaleAssigned ToAugustoMauch => alostale
2014-01-27 15:32hgbotCheckin
2014-01-27 15:32hgbotNote Added: 0063719
2014-01-27 15:32hgbotStatusnew => resolved
2014-01-27 15:32hgbotResolutionopen => fixed
2014-01-27 15:32hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/496cbeb8f989ceabb54b9d0e00c3400dfb2e61d8 [^]
2014-01-27 15:33alostaleReview Assigned To => shankarb
2014-01-28 06:45shankarbNote Added: 0063731
2014-01-28 06:45shankarbStatusresolved => closed
2014-01-28 06:45shankarbFixed in Version => 3.0MP32
2014-01-28 11:57jecharriNote Added: 0063745
2014-01-28 11:57jecharriStatusclosed => new
2014-01-28 11:57jecharriResolutionfixed => open
2014-01-28 11:57jecharriFixed in Version3.0MP32 =>
2014-01-29 11:06hgbotCheckin
2014-01-29 11:06hgbotNote Added: 0063776
2014-01-29 11:06hgbotStatusnew => resolved
2014-01-29 11:06hgbotResolutionopen => fixed
2014-01-29 11:06hgbotFixed in SCM revisionhttp://code.openbravo.com/erp/devel/pi/rev/496cbeb8f989ceabb54b9d0e00c3400dfb2e61d8 [^] => http://code.openbravo.com/erp/devel/pi/rev/b424cda58024897df64fdf1ba1512444bb741038 [^]
2014-01-29 11:09alostaleNote Added: 0063778
2014-02-04 22:39jecharriNote Added: 0063905
2014-02-12 04:23shankarbNote Added: 0064070
2014-02-12 04:23shankarbStatusresolved => closed
2014-02-12 04:23shankarbFixed in Version => 3.0MP32
2014-02-12 18:29hudsonbotCheckin
2014-02-12 18:29hudsonbotNote Added: 0064119
2014-02-12 18:29hudsonbotCheckin
2014-02-12 18:29hudsonbotNote Added: 0064133
2018-05-29 16:18alostaleRelationship addedrelated to 0038651
2020-06-18 17:00shuehnerRelationship addedrelated to 0044414

Notes
(0063718)
alostale   
2014-01-27 15:30   
Tested in a PG DB with 590K entries in AD_Session.

Time is reduced from ~75s to 320ms.
(0063719)
hgbot   
2014-01-27 15:32   
Repository: erp/devel/pi
Changeset: 496cbeb8f989ceabb54b9d0e00c3400dfb2e61d8
Author: Asier Lostalé <asier.lostale <at> openbravo.com>
Date: Mon Jan 27 15:32:04 2014 +0100
URL: http://code.openbravo.com/erp/devel/pi/rev/496cbeb8f989ceabb54b9d0e00c3400dfb2e61d8 [^]

fixed bug 25466: login performance when checking user lock

  username is not joined but passed as parameter, in this way query is
  executed in a more efficient manner

---
M src/org/openbravo/base/secureApp/UserLock.java
---
(0063731)
shankarb   
2014-01-28 06:45   
Code reviewed and verified in pi changeset 9b47b9cdf8c1.
(0063745)
jecharri   
2014-01-28 11:57   
The solution does not work in oracle. The query is still slow
(0063776)
hgbot   
2014-01-29 11:06   
Repository: erp/devel/pi
Changeset: b424cda58024897df64fdf1ba1512444bb741038
Author: Asier Lostalé <asier.lostale <at> openbravo.com>
Date: Wed Jan 29 11:05:39 2014 +0100
URL: http://code.openbravo.com/erp/devel/pi/rev/b424cda58024897df64fdf1ba1512444bb741038 [^]

fixed bug 25466: login performance when checking user lock

  splitting query with subquery into 2 different ones which perform better

---
M src/org/openbravo/base/secureApp/UserLock.java
---
(0063778)
alostale   
2014-01-29 11:09   
After latest fix tested in:
-PG with 590K entries in AD_Session.
-ORA with 265K entries in AD_Session.

Improvements:
-PG remains almost the same as with previous fix: from 75s to 300ms
-ORA: from 85s to 100ms
(0063905)
jecharri   
2014-02-04 22:39   
For me the results are not as good as Asier had:

-before the patch 3 seconds
-after that 860ms x 2 = 1.720 seconds
(0064070)
shankarb   
2014-02-12 04:23   
Code reviewed and verified in pi changeset d704451d1d2e.
(0064119)
hudsonbot   
2014-02-12 18:29   
A changeset related to this issue has been promoted main and to the
Central Repository, after passing a series of tests.

Promotion changeset: https://code.openbravo.com/erp/devel/main/rev/d1a5bb862230 [^]
Maturity status: Test
(0064133)
hudsonbot   
2014-02-12 18:29   
A changeset related to this issue has been promoted main and to the
Central Repository, after passing a series of tests.

Promotion changeset: https://code.openbravo.com/erp/devel/main/rev/d1a5bb862230 [^]
Maturity status: Test