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

BaseCompanyIdUpgradeProcess.TableUpdate.getSelectSQL() has a performance issue

    Details

      Description

      We create update statements like:
       

      update Image set companyId = (select max(companyId) from DLFileEntry where  DLFileEntry.largeImageId = Image.imageId);

      This is generated in the getSelectSQL() method. The performance problem comes from that part, that we query each element in DLFileEntry here, not only those that have some connection to Image.

      This could be fixed by adding an extra condition in the WHERE calues to limit the results to those rows, where the Image and DLFileEntry has connection:

      update Image set companyId = (select max(companyId) from DLFileEntry where DLFileEntry.largeImageId > 0 and DLFileEntry.largeImageId = Image.imageId);
      

      With Imaged table has around 50.000 records and DLFileEntry table has around 200.000 records, it takes 1,5-2 hours where there's no connection at all between the tables, if we add the connection check, it takes about 10 seconds.

      It affects every user of DXP

        Attachments

          Activity

            People

            Assignee:
            victor.ware Victor Ware
            Reporter:
            vilmos.papp Vilmos Papp
            Participants of an Issue:
            Recent user:
            Victor Ware
            Engineering Assignee:
            Vilmos Papp
            Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:
              Days since last comment:
              13 weeks, 1 day ago

                Packages

                Version Package
                7.4.2 CE GA3 DXP 7,4
                Master