Welcome Serena Central users! CLICK HERE
The migration of the Serena Central community is currently underway. Be sure to read THIS MESSAGE to get your new login set up to access your account.
Honored Contributor.. Blue_1 Honored Contributor..
Honored Contributor..
2765 views

Login is very slow

Jump to solution

We are using version 9.41, when a user try to login it takes more than 20 second to login. There is a query to the operator record at login that takes 15 seconds, the query is something like below 

select name from operator where lower full name

How can i prevent this query? How can i make login faster?

 

Thanks

0 Likes
1 Solution

Accepted Solutions
Absent Member.. WarrenPowers Absent Member..
Absent Member..

Re: Login is very slow

Jump to solution

Hi brav0,

    We did update the SL. Here are the changes:

Starts at line ~319:

  /**
   * To get the contact's info by an operator, additionally, add the label to the contact info.
   */
  function getContactByOperator(operatorName, label, needPortrait){
    //var operator = $('operator').select('tolower(name) = \"' + operatorName.toLowerCase() + '\"').uniqueResult();
    //WWP: Removed lower functions to improve performance
    var operator = $('operator').select('name = \"' + operatorName + '\"').uniqueResult();
    if(operator){
      var contact = getContact(operator.contact_name); // legacy SMC stored this field in xml
      contact.label = label;
      if(needPortrait){
        contact.portrait = getPortrait(operatorName);
      }
      return contact;
    }
    return null;
  }

Starts at line ~491:

  function getPortrait(userName){
    //var operator = $('operator').select('tolower(name) = \"' + userName.toLowerCase() + '\"').uniqueResult();
    //WWP: removed lower functions to improve performance
    var operator = $('operator').select('name = \"' + userName + '\"').uniqueResult();
    if(operator){
      var contact = $('contacts').select('contact.name = \"' + operator.contact_name + '\"').uniqueResult();
      if(!contact){
        return null;
      }
      var attachments = contact.getAttachments();
      for(var index in attachments){
        var attachment = attachments[index];
        if(attachment.name == contact.contact_name && attachment.attachmentType == 'img'){
          return {base64Image: compressImage(attachment.value, 64), mimeType: attachment.type};
        }
      }
    }
    return null;
  }
  

So far the login time after this change is inline with expected duration. The change is currently being tested in lower environments, however we have found no issue thus far. Looks like this worked!

25 Replies
Acclaimed Contributor.. Kelalek2 Acclaimed Contributor..
Acclaimed Contributor..

Re: Login is very slow

Jump to solution

May I ask how did you find out that the slow login performance is caused by a single query? What evidence (logs, trace etc.) lead you to that conclusion?

According to my experiences, logging into SM 9.41 is faster than SM 9.34. In any case, ~15-20 seconds is way too much and it's certain that something is wrong in your system.

---
Moving on, this account is no longer active. Best regards, Kelalek
- So Long, and Thanks for All the Fish
Acclaimed Contributor.. brav0 Acclaimed Contributor..
Acclaimed Contributor..

Re: Login is very slow

Jump to solution
Hi , what is the expected response time for login ? how much is what it is taking now ? First thing which you could check is my turning on the debug port with debugdbquery parameter This would give you an idea about what are the tables which are getting queried when a user logs in. For eg : DBACCESS -Select against file format in 0.000000 seconds similar you will find the files which are getting queried and look for transactions (query, fetch etc) which takes more than expected no of seconds. thx
I am Listening..
Acclaimed Contributor.. brav0 Acclaimed Contributor..
Acclaimed Contributor..

Re: Login is very slow

Jump to solution
Another place to look -- Look at the global lists which are getting built on startup. Remove the unnecessary gl's from build on start up and regen them once or twice a day depending on the updates on those files.
I am Listening..
Absent Member.. WarrenPowers Absent Member..
Absent Member..

Re: Login is very slow

Jump to solution

Hi brav0, I work with blue_1 in the same environments. For web client users, login takes anywhere from 20-30 seconds right now. I would expect login to take sub 5 seconds. When we log in with the thick client, login takes sub 5 seconds and the SQL statement blue_1 provided is not run.

We see this query via an sqllimit breach:

RTE D 1477: sqociSelectSome - EXECUTE:SELECT m1."NAME" FROM OPERATORM1 m1 WHERE ((LOWER(m1."NAME")=:Y)) ORDER BY m1."NAME" ASC

This query preforms a full table scan on operatorm1 due to the LOWER function. We utilize a case sensative Oracle DB and in this context the LOWER function does not make a lot of sense.

We did run debugdbquery:999 and it shows the same query being run twice (which aligns with the sqllimit logs we see).

Any assistance is appreciated.

 

TomPowe Acclaimed Contributor.
Acclaimed Contributor.

Re: Login is very slow

Jump to solution

Can you tell where in the code that query is running?  We have several queries ran in login.DEFAULT format control.  Look there and see if you can pinpoint the code...

Absent Member.. WarrenPowers Absent Member..
Absent Member..

Re: Login is very slow

Jump to solution

We are unsure where the query is running. I've attached the queries section and subroutines section of FC. Queries has one entry out to operator. We don't specify LOWER.

If LOWER wasn't included in the query, it would hit an index and return much faster.

Any recommendation on other locations to search? This is in a 9.41 p3 codeless environment.

 

Acclaimed Contributor.. brav0 Acclaimed Contributor..
Acclaimed Contributor..

Re: Login is very slow

Jump to solution
seems like there is a true query running on name field of operator . Are there any global lists which is getting built on operator table ? You might need to post a web client trace log here .
I am Listening..
Highlighted
Absent Member.. WarrenPowers Absent Member..
Absent Member..

Re: Login is very slow

Jump to solution

I checked for all global lists where filename is operator, and no results where returned. I can provide a web client trace, but i'm unsure how to do that. Could you advise?

Acclaimed Contributor.. brav0 Acclaimed Contributor..
Acclaimed Contributor..

Re: Login is very slow

Jump to solution
To get the trace from web client, make sure you have the below parameter turned on in the web.xml , honorUrlPort parameter is set to true. You could open the debug port from the command line with the debubgdbquery and sqldebug parameter turned on. access the webclient http:///sm/index.do>serverPort= The user should get connected to the port and you will see the log file getting logged.
I am Listening..
Acclaimed Contributor.. brav0 Acclaimed Contributor..
Acclaimed Contributor..

Re: Login is very slow

Jump to solution
http:///sm/index.do>serverPort="serverPortNumber"
I am Listening..
Acclaimed Contributor.. brav0 Acclaimed Contributor..
Acclaimed Contributor..

Re: Login is very slow

Jump to solution
http:///sm/index.do?serverPort="port number" , This is syntax. You could refer this URL /t5/Service-Manager-Service-Center/Tracing-a-single-web-client-user/td-p/2404297
I am Listening..
Absent Member.. WarrenPowers Absent Member..
Absent Member..

Re: Login is very slow

Jump to solution

It looks like you can only attach images to the site. Here are some excerpts from the log:

Here is the breach:

6357052(87425103) 12/05/2016 15:13:15  RTE D (0x372AE2E0)      DBACCESS - Fetch Abs(  0) against file Todo
6357052(87425103) 12/05/2016 15:13:15  RTE D (0x372AE2E0)      DBACCESS - Fetch Abs(  0) against file Todo in 0.000000 seconds [rc=0 ] (record.id="C98667")
6357052(73728031) 12/05/2016 15:13:18  RTE W sqllimit exceeded, user=XXXXXXX          limit=5.000 actual=21.601 SQL statement follows
6357052(73728031) 12/05/2016 15:13:18  RTE D 1466: sqociSelectSome - EXECUTE:SELECT m1."NAME" FROM OPERATORM1 m1 WHERE ((LOWER(m1."NAME")=:Y)) ORDER BY m1."NAME" ASC
6357052(73728031) 12/05/2016 15:13:18  RTE D _ociDescribeSqlda:   1: NAME                             type=1  -  p/s=  0/   0 size=  60/  60 BYTE  871 1 VARCHAR2(60) NULLABLE
6357052(73728031) 12/05/2016 15:13:18  RTE D sql diff 0.000  total 23.717  call#:1467 --> _describe:SELECT m1."NAME" FROM OPERATORM1 m1 WHERE ((LOWER(m1."NAME")=:Y)) ORDER BY m1."NAME" ASC
6357052(73728031) 12/05/2016 15:13:18  RTE D sqociInitSqlda: NAME, type: 9-VARCHAR (was 1-VARCHAR2), len: 62
6357052(73728031) 12/05/2016 15:13:18  RTE D sql diff 0.000  total 23.717  call#:1468 --> sqociSelectSome - SINGLE FETCH:SELECT m1."NAME" FROM OPERATORM1 m1 WHERE ((LOWER(m1."NAME")=:Y)) ORDER BY m1."NAME" ASC
6357052(73728031) 12/05/2016 15:13:18  RTE D sql diff 0.000  total 23.717  call#:1469 --> sqociSelectSome - SINGLE FETCH:EOF

Earlier in the thread we had a query to operatorm1 with the same clause, but without lower:

6357052(73728031) 12/05/2016 15:12:54  RTE D (0x3557AF40)      DBACCESS - Cache Term   against file info
6357052(73728031) 12/05/2016 15:12:54  RTE D (0x35A3B7C0)      DBACCESS - Cache Init   against file operator
6357052(73728031) 12/05/2016 15:12:54  RTE D (0x354C7470)      DBACCESS - Cache Find   against file scaccess found 1 NULL record (filename="operator" and security.id="")
6357052(73728031) 12/05/2016 15:12:54  RTE D (0x354C7470)      DBACCESS - Cache Find   against file scaccess found 1 NULL record (filename="operator" and security.id=NULL)
6357052(73728031) 12/05/2016 15:12:54  RTE D (0x35A3B7C0)      DBACCESS - Find         against file operator
6357052(73728031) 12/05/2016 15:12:54  RTE D sql diff 0.000  total 2.039  call#:1258 --> _prepare:SELECT * FROM OPERATORM1 WHERE "NAME"=:Y
6357052(73728031) 12/05/2016 15:12:54  RTE D sql diff 0.000  total 2.039  call#:1259 --> _ociBindQuery:SELECT * FROM OPERATORM1 WHERE "NAME"=:Y
6357052(73728031) 12/05/2016 15:12:54  RTE D sql diff 0.003  total 2.042  call#:1260 --> sqociSelect - EXECUTE:SELECT * FROM OPERATORM1 WHERE "NAME"=:Y
6357052(73728031) 12/05/2016 15:12:54  RTE D sql diff 0.000  total 2.042  call#:1261 --> _ociReadDataFromLob - Get LOB length:NAMES
6357052(73728031) 12/05/2016 15:12:54  RTE D sql diff 0.001  total 2.043  call#:1262 --> _ociReadDataFromLob - Read LOB:NAMES
6357052(73728031) 12/05/2016 15:12:54  RTE D sql diff 0.000  total 2.043  call#:1263 --> _ociReadDataFromLob - Get LOB length:VALUES
6357052(73728031) 12/05/2016 15:12:54  RTE D sql diff 0.001  total 2.044  call#:1264 --> _ociReadDataFromLob - Read LOB:VALUES
6357052(73728031) 12/05/2016 15:12:54  RTE D sql diff 0.000  total 2.044  call#:1265 --> _ociReadDataFromLob - Get LOB length:GROUPS
6357052(73728031) 12/05/2016 15:12:54  RTE D sql diff 0.001  total 2.045  call#:1266 --> _ociReadDataFromLob - Read LOB:GROUPS

 

Acclaimed Contributor.. brav0 Acclaimed Contributor..
Acclaimed Contributor..

Re: Login is very slow

Jump to solution
One more question -- Did you also looked at your ToDo inbox queries ?. These are also run when a user logs in. Also, I am unable to interpret much from the abstract of the log which you have provided.
I am Listening..
TomPowe Acclaimed Contributor.
Acclaimed Contributor.

Re: Login is very slow

Jump to solution

Can you do another trace with RTM:3 and debugdbquery:999 and provide that log?

The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.