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

Loading the issues of a given file is slow on Oracle

    XMLWordPrintable

    Details

    • Type: Improvement
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 4.3
    • Component/s: Database, Issues
    • Labels:
      None

      Description

      The tab "Issues" of source viewers can be very slow on Oracle db with millions of rows in the table ISSUES.

      The following SQL request is executed in ~3 minutes on my box with 21 millions issues (<USER_ID> and <FILE_KEY> to be replaced) :

      SELECT
        *
      FROM (SELECT
              i.id
            FROM
              issues i INNER JOIN projects root_project ON root_project.id = i.root_component_id AND root_project.enabled = 1
              INNER JOIN (SELECT
                            p.kee AS root_project_kee
                          FROM user_roles ur INNER JOIN projects p
                              ON p.id = ur.resource_id AND p.scope = 'PRJ' AND p.qualifier = 'TRK'
                          WHERE ur.role = 'user' AND ur.user_id = <USER_ID>
                          UNION SELECT
                                  p.kee AS root_project_kee
                                FROM group_roles gr INNER JOIN projects p
                                    ON p.id = gr.resource_id AND p.scope = 'PRJ' AND p.qualifier = 'TRK'
                                WHERE gr.role = 'user' AND (gr.group_id IS NULL OR gr.group_id IN (SELECT
                                                                                                     gu.group_id
                                                                                                   FROM groups_users gu
                                                                                                   WHERE gu.user_id = <USER_ID>))
                         ) authorizedProjects
                ON authorizedProjects.root_project_kee = root_project.kee
              INNER JOIN projects project_component
                ON project_component.id = i.component_id AND project_component.enabled = 1 AND
                   (project_component.kee = '<FILE_KEY>')
            WHERE i.resolution IS NULL)
      WHERE rownum <= 10000;
      

      Dropping the first useless join dramatically increases performances. Execution is 2 seconds long :

      SELECT
        *
      FROM (SELECT
              i.id
            FROM issues i 
              INNER JOIN (SELECT
                            p.id AS root_project_id
                          FROM user_roles ur INNER JOIN projects p
                              ON p.id = ur.resource_id AND p.scope = 'PRJ' AND p.qualifier = 'TRK'
                          WHERE ur.role = 'user' AND ur.user_id = <USER_ID>
                          UNION SELECT
                                  p.id AS root_project_id
                                FROM group_roles gr INNER JOIN projects p
                                    ON p.id = gr.resource_id AND p.scope = 'PRJ' AND p.qualifier = 'TRK'
                                WHERE gr.role = 'user' AND (gr.group_id IS NULL OR gr.group_id IN (SELECT
                                                                                                     gu.group_id
                                                                                                   FROM groups_users gu
                                                                                                   WHERE gu.user_id = <USER_ID>))
                         ) authorizedProjects
                ON authorizedProjects.root_project_id = i.root_component_id
              INNER JOIN projects project_component
                ON project_component.id = i.component_id AND project_component.enabled = 1 AND
                   (project_component.kee = '<FILE_KEY>')
            WHERE i.resolution IS NULL)
      WHERE rownum <= 10000;
      

        Attachments

          Activity

            People

            Assignee:
            simon.brandhof Simon Brandhof (Inactive)
            Reporter:
            simon.brandhof Simon Brandhof (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Due:
              Created:
              Updated:
              Resolved: