Uploaded image for project: 'SonarQube'
  1. SonarQube
  2. SONAR-12659

Displaying quality profiles can be slow when instance has a lot of projects/components

    XMLWordPrintable

    Details

    • Edition:
      Community
    • Production Notes:
      None

      Description

      Description

      Displaying list of quality profile and a single quality profile can be very slow when the SonarQube instance has a big number of projects (137.000 in the case of the customer having reported this issue).

      Investigation

      The customer has been able to detect that the issue is coming from the following query in 7.9 is very slow :

      <select id="countProjectsByOrganizationAndProfiles" resultType="KeyLongValue" parameterType="map">
          select pqp.profile_key as "key", count(pj.uuid) as "value"
          from projects pj
          inner join project_qprofiles pqp on pqp.project_uuid = pj.uuid
          inner join org_qprofiles oqp on oqp.uuid = pqp.profile_key
          where
            pj.enabled = ${_true}
            and pj.organization_uuid = #{organizationUuid, jdbcType=VARCHAR}
            and oqp.organization_uuid = pj.organization_uuid
            and <foreach collection="profileUuids" item="profileUuid" open="(" separator=" or " close=")">
              oqp.uuid = #{profileUuid, jdbcType=VARCHAR}
            </foreach>
          group by pqp.profile_key
        </select>
      

      He has been able to fix this issue by adding the following index :

      Missing index (Impact 79.3962) : create nonclustered index on dbo.projects (enabled, organization_uuid) include uuid
      

       
      In 8.6 the query is now :

      <select id="countProjectsByProfiles" resultType="KeyLongValue" parameterType="map">
          select pqp.profile_key as "key", count(pj.uuid) as "value"
          from components pj
          inner join project_qprofiles pqp on pqp.project_uuid = pj.uuid
          inner join org_qprofiles oqp on oqp.uuid = pqp.profile_key
          where
            pj.enabled = ${_true}
            and <foreach collection="profileUuids" item="profileUuid" open="(" separator=" or " close=")">
              oqp.uuid = #{profileUuid, jdbcType=VARCHAR}
            </foreach>
          group by pqp.profile_key
        </select>
      

      How to fix

      Thanks to SONAR-13142, stating 8.2, the PROJECTS table now only contains projects, no more all components (they have been migrated to the COMPONENTS table).

      The query could then very easily be improved to replace usage of the COMPONENTS table by the PROJECT}}S table, and to add a filter on qualifier {{TRK.

       

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              belen.pruvost Belén Pruvost
              Reporter:
              julien.lancelot Julien Lancelot
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Due:
                Created:
                Updated:
                Resolved: