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

Redundant database queries during search with multiple APs

    Details

      Description

      Reproduction Steps

      1 - Turn on hibernate.show_sql=true in portal-ext.properties.
      2 - Monitor SQL statements issued by the portal.
      3 - In Liferay, add an Asset Publisher and 10 Web Contents with the "Liferay Rules" title
      4 - Add a Search Portlet and search for the word "Rules"
      5 - In Liferay's log search for the following (or very similar) query.

      If you have 10 WCs, this will be called 10 times:

      SELECT
        PortletPreferences.portletPreferencesId as portletP1_37_0_,
        PortletPreferences.ownerId as ownerId37_0_,
        PortletPreferences.ownerType as ownerType37_0_,
        PortletPreferences.plid as plid37_0_,
        PortletPreferences.portletId as portletId37_0_,
        PortletPreferences.preferences as preferen6_37_0_
      FROM
        PortletPreferences
      INNER JOIN Layout ON
        (Layout.plid = PortletPreferences.plid)
      WHERE
        (Layout.companyId = 10154) AND
        (Layout.groupId = 10180) AND
        (PortletPreferences.ownerId = 0) AND
        (PortletPreferences.ownerType = 3) AND
        (
          (PortletPreferences.portletId = '101') OR (PortletPreferences.portletId LIKE '101_INSTANCE_%')
        ) AND
        (Layout.privateLayout = 0)
      

      Let's denote ...
      K: Number of items returned by the search portlet.
      N: Number of asset publishers in the portal.
      ...

      The query above will be executed K times, returning each time N + 1 rows; which means that K(N + 1) rows are fetched from the DB for every search request.

      As the Customer reports that their scenario includes ~890 asset publishers per site, database transactions may be hit pretty hard.

      Further Information

      Please refer to the following forum thread: https://in.liferay.com/web/global.engineering/forums/-/message_boards/message/1284919

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                he.song Serena Song (Inactive)
                Reporter:
                laszlo.csontos Laszlo Csontos (Inactive)
                Participants of an Issue:
                Recent user:
                Esther Sanz
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:
                  Days since last comment:
                  6 years, 28 weeks, 2 days ago

                  Packages

                  Version Package
                  6.1.2 CE GA3
                  6.1.30 EE GA3
                  6.2.0 CE M5