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

            Assignee:
            yang.cao Yang Cao
            Reporter:
            vendel.toreki Vendel Töreki
            Participants of an Issue:
            Recent user:
            Sophia Zhang
            Engineering Assignee:
            Vendel Töreki
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:
              Days since last comment:
              35 weeks, 2 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