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

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

    Details

    • Type: Improvement
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: Database, Quality Profile
    • Labels:
      None
    • 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).

      Technical details

      The customer has been able to detect that the following query 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 find the missing index :

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

      However, as on SonarQube there's only one organization, adding an index on a column that can contain only one value seems overkill.
      We should find a way to optimize the query without having to add such index.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                julien.lancelot Julien Lancelot
              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated: