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

ServiceBuilder filterFindBy queries are not performant

    Details

      Description

      ServiceBuilder filterFindBy queries are not performant:

      Steps to reproduce:

      1. Generate 1000 sites.
      2. Clone and deploy this portlet: https://github.com/sammso/tester-liferay-permission
      3. Create page "Bar" and add Bar portlet to page.
      4. Generate teams using recently deployed portlet going to Control Panel -> Apps -> Generate. Click in "generate-teams".
      5. Create one userx, link it to "Liferay" site (or the one on which these tests are running), then click in "Site Teams" and assign as a member of any of the generated teams (Team A---Team D).
      6. IMPORTANT: This step need some free HDD memory (maybe 1GB) and requires a lot of time to execute (maybe 2-3 hours). Generate bar entities with generate portlet that you can find from control panel (Control Panel -> Apps -> Generate. Click in "generate-bars").
      7. When previous content creation has finished, execute attached get_performance.groovy from Control Panel -> Server Administration -> Script, changing userId by _User.userId field value from previously created "userx". Change groupId to the site groupId too.

      Results
      Before the fix:
      MySQL:

      Total time:6558. Number of results:1000

      PostgreSQL:

      Total time:2304. Number of results:1000

      After the fix:
      MySQL:

      Total time:746. Number of results:1000

      PostgreSQL

      Total time:44. Number of results:1000

      The performance has increased a lot.

      FIX EXPLANATION:
      The inline resource permissions JOIN before the fix was using an INNER SELECT like this:

      FROM
          perpoc_Bar bar
              INNER JOIN
          (SELECT 
              primKey
          FROM
              ResourcePermission
          WHERE
              (ResourcePermission.companyId = 20155)
                  AND (ResourcePermission.name = 'com.sohlman.liferay.foobar.model.Bar')
                  AND (ResourcePermission.scope = 4)
                  AND (MOD(ResourcePermission.actionIds, 2) = 1)
                  AND (ResourcePermission.roleId IN (20165 , 20166, 20163, 24526, 20171)
                  OR (ResourcePermission.ownerId = 32537))
          GROUP BY primKey) InlineSQLResourcePermission ON (((InlineSQLResourcePermission.primKey = bar.barId)
              AND (((bar.groupId = 20182)))))
      

      Having this inner SELECT caused the query to be far from optimal as this MySQL Query Execution plan shows:

      After the fix, the INNER JOIN looks like this:

      FROM
          perpoc_Bar bar
              INNER JOIN
          ResourcePermission ON (ResourcePermission.companyId = 20155)
              AND (ResourcePermission.name = 'com.sohlman.liferay.foobar.model.Bar')
              AND (ResourcePermission.scope = 4)
              AND (MOD(ResourcePermission.actionIds, 2) = 1)
              AND (ResourcePermission.roleId IN (20165 , 20166, 20163, 24526, 20171)
              OR (ResourcePermission.ownerId = 32537))
              AND (((ResourcePermission.primKey = CAST(bar.barId AS CHAR))
              AND (((bar.groupId = 20182)))))
      

      so the INNER JOIN doesn't have any additional SELECT. This MySQL Query Execution plan confirms the query is much efficient:

      Note: ResourceBlock permissions are not suffering performance degration

        Attachments

          Issue Links

            Activity

              People

              • Votes:
                0 Vote for this issue
                Watchers:
                5 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:
                  Days since last comment:
                  3 years, 47 weeks, 6 days ago

                  Packages

                  Version Package
                  6.2.4 CE GA5
                  6.2.X EE
                  7.0.0 M7