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

Performance issue when loading Project Referentials with MS SQL

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 4.5.2, 5.0
    • Fix Version/s: 4.5.4, 5.0.1
    • Component/s: None
    • Labels:
      None

      Description

      This performance issue seems to be a side effect of SONAR-5849 on MSSQL and happens when analysing a project containing a lot of modules and past snapshots.

      For one customer the execution of the following SQL request takes 2mn

      2015.01.13 10:04:24 INFO  http-bio-10.230.49.59-9000-exec-1 web[sql]  125228ms Executed SQL: SELECT p.id, p.kee as kee, p.name as name, p.long_name as longName, p.qualifier as qualifier, p.scope as scope, p.language as language, s.root_project_id as projectId, p.root_id as subProjectId, p.path as path , parent.kee as parentModuleKey FROM projects p INNER JOIN snapshots s ON s.project_id=p.idAND s.islast=1 INNER JOIN ( SELECT s.id, s.parent_snapshot_id as parentId, s.root_snapshot_id as rootId, s.root_project_id as rootProjectId, s.project_id as resourceId, s.created_at as createdAt, s.build_date as buildDate, s.status as status, s.purge_status as purgeStatus, s.islast as last, s.scope as scope, s.qualifier as qualifier, s.version as version, s.path as path, s.depth as depth, s.period1_mode as period1Mode, s.period2_mode as period2Mode, s.period3_mode as period3Mode, s.period4_mode as period4Mode, s.period5_mode as period5Mode, s.period1_param as period1Param, s.period2_param as period2Param, s.period3_param as period3Param, s.period4_param as period4Param, s.period5_param as period5Param, s.period1_date as period1Date, s.period2_date as period2Date, s.period3_date as period3Date, s.period4_date as period4Date, s.period5_date as period5Date FROM snapshots s INNER JOIN snapshots root_snapshot ON root_snapshot.id= s.root_snapshot_id AND root_snapshot.islast = 1 INNER JOIN snapshots current_snapshot ON current_snapshot.root_project_id = root_snapshot.project_id AND s.islast = 1 INNER JOIN projects module ON module.id= current_snapshot.project_id AND module.enabled = 1 AND module.kee = ? WHERE s.islast = 1 AND s.scope = 'PRJ' AND s.path LIKE current_snapshot.path + CAST(current_snapshot.id AS varchar(15)) + '.%' ) snapshotModules on snapshotModules.resourceId=p.idLEFT OUTER JOIN snapshots parent_snapshot on parent_snapshot.id= snapshotModules.parentId LEFT OUTER JOIN projects parent on parent.id= parent_snapshot.project_id - parameters are: <no.tine:reku>
      

      and the execution of the following one takes 29s

      2015.01.13 10:04:53 INFO  http-bio-10.230.49.59-9000-exec-1 web[sql]  29731ms Executed SQL: SELECT prop.id as id, prop.prop_key as "key", prop.text_value as value, prop.resource_id as resourceId, prop.user_id as userId FROM properties prop INNER JOIN ( SELECT s.id, s.parent_snapshot_id as parentId, s.root_snapshot_id as rootId, s.root_project_id as rootProjectId, s.project_id as resourceId, s.created_at as createdAt, s.build_date as buildDate, s.status as status, s.purge_status as purgeStatus, s.islast as last, s.scope as scope, s.qualifier as qualifier, s.version as version, s.path as path, s.depth as depth, s.period1_mode as period1Mode, s.period2_mode as period2Mode, s.period3_mode as period3Mode, s.period4_mode as period4Mode, s.period5_mode as period5Mode, s.period1_param as period1Param, s.period2_param as period2Param, s.period3_param as period3Param, s.period4_param as period4Param, s.period5_param as period5Param, s.period1_date as period1Date, s.period2_date as period2Date, s.period3_date as period3Date, s.period4_date as period4Date, s.period5_date as period5Date FROM snapshots s INNER JOIN snapshots root_snapshot ON root_snapshot.id= s.root_snapshot_id AND root_snapshot.islast = 1 INNER JOIN snapshots current_snapshot ON current_snapshot.root_project_id = root_snapshot.project_id AND s.islast = 1 INNER JOIN projects module ON module.id= current_snapshot.project_id AND module.enabled = 1 AND module.kee = ? WHERE s.islast = 1 AND s.scope = 'PRJ' AND s.path LIKE current_snapshot.path + CAST(current_snapshot.id AS varchar(15)) + '.%' ) snapshotModules on snapshotModules.resourceId=prop.resource_id INNER JOIN projects p on p.id= prop.resource_id WHERE prop.user_id IS NULL - parameters are: <no.tine:reku>
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                julien.lancelot Julien Lancelot
                Reporter:
                freddy.mallet Freddy Mallet (Inactive)
              • Votes:
                2 Vote for this issue
                Watchers:
                7 Start watching this issue

                Dates

                • Due:
                  Created:
                  Updated:
                  Resolved: