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

The SQL request used to get hotspot on metric can be improved

    XMLWordPrintable

    Details

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

      Description

      The current SQL request is the following one :

      SELECT `project_measures`.* FROM `project_measures` INNER JOIN `snapshots` ON `snapshots`.id = `project_measures`.snapshot_id WHERE (snapshots.qualifier in ('FIL','UTS','CLA','UTS') AND snapshots.islast=1 AND snapshots.status = 'P' AND (snapshots.id=28035446 OR (snapshots.root_snapshot_id=28035446 AND snapshots.path LIKE '28035446.%')) AND project_measures.rule_id IS NULL AND project_measures.characteristic_id IS NULL AND project_measures.person_id IS NULL AND project_measures.metric_id = 28) ORDER BY project_measures.value DESC LIMIT 5
      

      Performances are highly impacted by the nested following OR condition :

      (snapshots.id=28035446 OR (snapshots.root_snapshot_id=28035446 AND snapshots.path LIKE '28035446.%')
      

      This is a known anti-pattern (at least with MySQL) : the conditions of an inner joint must not contain any alternative.

      In this condition, I think that the first one is fully useless :

      snapshots.root_snapshot_id=28035446 AND snapshots.path LIKE '28035446.%'
      

      If the first one is not useless then we must do an union between two different SQL requests instead of using this alternative.

        Attachments

          Activity

            People

            Assignee:
            simon.brandhof Simon Brandhof (Inactive)
            Reporter:
            freddy.mallet Freddy Mallet (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: