Uploaded image for project: 'PUBLIC - Liferay Portal Community Edition'
  1. PUBLIC - Liferay Portal Community Edition
  2. LPS-69863

Custom SQL UserFinder.findByC_FN_MN_LN_SN_EA_S retrieves unnecesary columns from database

    Details

      Description

      Custom SQL UserFinder.findByC_FN_MN_LN_SN_EA_S is used in two methods:

      • UserFinderImpl.countByC_FN_MN_LN_SN_EA_S
      • UserFinderImpl.doFindByC_FN_MN_LN_SN_EA_S
        (more info see LPS-68951)

      In both methods when we query using custom SQL UserFinder.findByC_FN_MN_LN_SN_EA_S, we are only using the userId column, but it queries other unnecessary columns:

      SELECT
      	User_.userId AS userId, User_.screenName AS screenName, User_.firstName AS firstName, User_.middleName AS middleName, User_.lastName AS lastName, User_.loginDate as loginDate
      FROM
      	User_
      [$JOIN$]
      WHERE
            [....]
      

      We should query only SELECT User_.userId FROM User_ ......

      In case of having 1000000 users, I have detected a improvement of 40%

      Steps to reproduce

      1. Configure in portal-ext.properties: users.indexer.enabled=false and users.search.with.index=false
      2. Create 1000000 users in Liferay
      3. Execute attanched groovy script searchCount.groovy
        • Without changing SQL: UserLocalServiceUtil.searchCount takes 1.5-2 seconds
        • After changing SQL to select count(userId): Time is reduced to 0.5-1 seconds
      4. Execute attanched groovy script searchWithOrderBy.txt
        • Without changing SQL: UserLocalServiceUtil.search getting 500 users sorted by name takes 4-5 seconds
        • After changing SQL to select count(userId): Time is reduced to 2-3 seconds

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                linda.sui Linda Sui
                Reporter:
                jorge.diaz Jorge Diaz
                Participants of an Issue:
                Recent user:
                Csaba Turcsan
              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:
                  Days since last comment:
                  2 years, 41 weeks ago

                  Packages

                  Version Package
                  6.2.X EE
                  7.0.0 DXP FP10
                  7.0.0 DXP SP2
                  7.0.3 CE GA4
                  7.1.X
                  Master