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

JournalArticleFinderImpl.findByC_G_F_C_A_V_T_D_C_S_T_D_R(..) queries are not optimal when JournalArticleLocalization fields are not used

Details

    Description

      Reproduction steps:

      1. Set up a portal
      2. Create a new site
      3. Generate 100000 basic web contents
      4. Create a new structure (add some dummy fields)
      5. Create a few web contents based on the structure
      6. Execute the following Groovy script, with replaced structureKey, companyId, groupId:
        import com.liferay.dynamic.data.mapping.service.DDMStructureLocalServiceUtil;
        import java.util.List;
        import com.liferay.dynamic.data.mapping.model.DDMStructure;
        import java.util.ArrayList;
        import com.liferay.journal.service.JournalArticleLocalServiceUtil;
        import com.liferay.portal.kernel.workflow.WorkflowConstants;
        
        String structureKey =  "1151700";
        List<Long> folderIds = new ArrayList<>();
        long companyId=20101;
        long groupId=34753
        JournalArticleLocalServiceUtil.search(companyId, groupId, folderIds, 0, null, null, structureKey, null, null, null, WorkflowConstants.STATUS_APPROVED, null, -1, -1, null);
        

      Expected behavior: the query is executed relatively quickly, in less than 1 second.
      Actual behavior: the query takes a few seconds to finish.

      Notes:

      • When you debug into JournalArticleFinderImpl.doFindByC_G_F_C_A_V_T_D_C_S_T_D_R(..), you can read the query from the sql variable. You can see that it joins to JournalArticleLocalization table, even though none of its fields are actually used in the where conditions or in the ORDER BY statement.
      • LPS-109979 already solved a similar problem, just with other queries. My guess is that the same would be possible for these complex queries as well.

      Attachments

        Activity

          People

            yang.cao Yang Cao
            vendel.toreki Vendel Töreki
            Kiyoshi Lee Kiyoshi Lee
            Vendel Töreki Vendel Töreki
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:
              1 year, 40 weeks, 4 days ago

              Packages

                Version Package
                7.2.10 DXP FP14
                7.2.10.5 DXP SP5
                7.2.X
                7.3.10 DXP FP1
                7.3.10.1 DXP SP1
                7.3.6 CE GA7
                7.3.X
                7.4.13 DXP GA1
                Master