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

JournalArticleLocalServiceUtil.search() performance imrpovement

    Details

      Description

      JournalArticleLocalServiceUtil.search(), which uses JournalArticleFinderImpl.findByC_G_C_A_V_T_D_C_T_S_T_D_S_R() to query journal articles can be improved in terms of response time, if the following custom SQL in journal.xml were restructured.

      SELECT
      	JournalArticle.*
      FROM
      	(
      		SELECT
      			groupId AS tempGroupId, articleId AS tempArticleId, MAX(version) AS tempVersion
      		FROM
      			JournalArticle
      		WHERE
      			(companyId = ?) AND
      			(groupId = ?) AND
      			(classNameId = ?) AND
      			(
      				(articleId LIKE ? [$AND_OR_NULL_CHECK$]) [$AND_OR_CONNECTOR$]
      				(version = ?) [$AND_OR_CONNECTOR$]
      				(lower(title) LIKE ? [$AND_OR_NULL_CHECK$]) [$AND_OR_CONNECTOR$]
      				(description LIKE ? [$AND_OR_NULL_CHECK$]) [$AND_OR_CONNECTOR$]
      				(content LIKE ? [$AND_OR_NULL_CHECK$]) [$AND_OR_CONNECTOR$]
      				(displayDate >= ? [$AND_OR_NULL_CHECK$]) [$AND_OR_CONNECTOR$]
      				(displayDate <= ? [$AND_OR_NULL_CHECK$]) [$AND_OR_CONNECTOR$]
      				(
      					(status = ?) AND
      					(reviewDate <= ? [$AND_OR_NULL_CHECK$])
      				)
      			)
      		[$GROUP_BY_CLAUSE$]
      	) TEMP_TABLE
      	INNER JOIN
      		JournalArticle ON
      			(TEMP_TABLE.tempGroupId = JournalArticle.groupId) AND
      			(TEMP_TABLE.tempArticleId = JournalArticle.articleId) AND
      			(TEMP_TABLE.tempVersion = JournalArticle.version)
      WHERE
      	(type_ = ? [$AND_OR_NULL_CHECK$]) [$AND_OR_CONNECTOR$]
      	(structureId LIKE ? [$AND_OR_NULL_CHECK$]) [$AND_OR_CONNECTOR$]
      	(templateId LIKE ? [$AND_OR_NULL_CHECK$]) [$AND_OR_CONNECTOR$]
      	(companyId = ?)
      ORDER BY
      	id_ ASC
      

      If the outer WHERE predicates are moved into the inner WHERE the query completes two times faster.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                support-lep@liferay.com SE Support
                Reporter:
                laszlo.csontos Laszlo Csontos (Inactive)
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Packages

                  Version Package
                  6.2.0 CE M3