Uploaded image for project: 'Confluence Server and Data Center'
  1. Confluence Server and Data Center
  2. CONFSERVER-74889

Optimize SQL query in com.atlassian.confluence.internal.persistence.hibernate.AbstractContentEntityObjectHibernateDao#queryContributionStatusByTouch

    XMLWordPrintable

Details

    • 1
    • We collect Confluence feedback from various sources, and we evaluate what we've collected when planning our product roadmap. To understand how this piece of feedback will be reviewed, see our Implementation of New Features Policy.

    Description

      Query has poor performance on large CONTENT table. Please optimize it:

       

      final String sqlString = "SELECT rt.currentId as contentId, rt.draftprevver as latestVersionid, rt.RELATIONID as relationId, " +
      "CASE WHEN lt.LASTMODDATE < rt.touchdate THEN rt.CONTENT_STATUS ELSE lt.CONTENT_STATUS END as contentStatus, rt.touchdate as lastModifiedDate " +
      "FROM CONTENT lt " +
      "JOIN ( " +
      "SELECT ucr.LASTMODDATE as touchdate, ucr.RELATIONID, c.LASTMODDATE, c.CONTENTID, " +
      "c.TITLE, c.CONTENT_STATUS, coalesce(c.PREVVER, c.CONTENTID) as currentId, c.PREVVER as draftprevver " +
      "FROM CONTENT c " +
      "LEFT JOIN USERCONTENT_RELATION ucr " +
      "ON c.CONTENTID = ucr.TARGETCONTENTID " +
      "WHERE ucr.RELATIONNAME = 'touched' " +
      "AND ucr.LASTMODIFIER = :userKey " +
      " and c.CONTENTTYPE in (:contentTypes) " +
      " and c.CONTENT_STATUS in (:contentStatuses) " +
      ") rt " +
      "on lt.CONTENTID = rt.currentId " +
      "WHERE lt.CONTENTID in (:contentIds)";
      
      

       

      Using "coalesce(c.PREVVER, c.CONTENTID) as currentId" in subquery is bad idea. 

      EXPAIN on our database

       

                                                          • 1. row ***************************
                                                            id: 1
                                                            select_type: SIMPLE
                                                            table: lt
                                                            partitions: NULL
                                                            type: const
                                                            possible_keys: PRIMARY
                                                            key: PRIMARY
                                                            key_len: 8
                                                            ref: const
                                                            rows: 1
                                                            filtered: 100.00
                                                            Extra: NULL
                                                          • 2. row ***************************
                                                            id: 1
                                                            select_type: SIMPLE
                                                            table: c
                                                            partitions: NULL
                                                            type: index
                                                            possible_keys: NULL
                                                            key: c_si_ct_pv_cs_cd_idx
                                                            key_len: 1559
                                                            ref: NULL
                                                            rows: 5356462
                                                            filtered: 4.00
                                                            Extra: Using where; Using index
                                                          • 3. row ***************************
                                                            id: 1
                                                            select_type: SIMPLE
                                                            table: ucr
                                                            partitions: NULL
                                                            type: ref
                                                            possible_keys: u2c_relation_unique,relation_u2c_targetcontent_idx
                                                            key: relation_u2c_targetcontent_idx
                                                            key_len: 8
                                                            ref: confluencedb.c.CONTENTID
                                                            rows: 1
                                                            filtered: 4.92
                                                            Extra: Using where
                                                            3 rows in set, 1 warning (0.00 sec)

      Subquery is fetched 5356462 rows and after filtered it to 1 row.

      WORKAROUND

      Add extra condition in sub-query:

       

      and (c.PREVVER in (:contentIds) or c.CONTENTID in (:contentIds))

       

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              04f0c8ecb0ed Ivan Mashintsev
              Votes:
              5 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: