CODEWITHSUNDEEP
sql-servert-sqlquery-performance
geostocker
geostocker

Reputation: 1200

Decrease execution time of SQL query

I've got a question in terms of processing and making a query more efficient whilst maintaining its accuracy. Before I display the query I'd like to point out some basics of it.

I've got a case that manipulates the where-clause to get all childs of the parent. Basically I've got two types of data that I need to display; a red and a green type. The red type has a column (TRK_TrackerGroup_LKID2) set to NULL by default, whereas the green data has a value in said column (ranging from 5-7).

My problem is that I need to extract both types of data to accurately get a count of outstanding issues in a view, but doing so (by adding the case) the execution time goes from < 1 second to well over 15 seconds.

This is the query (with the mentioned case):

SELECT TS.id        AS TrackerStartDateID,
       TSM.mappingtypeid,
       TSM.maptoid,
       TFLK.trk_trackergroup_lkid,
       Count(TF.id) AS Cnt
FROM   [dbo].[trk_startdate] TS
       INNER JOIN [dbo].[trk_startdatemap] TSM
               ON TS.id = TSM.trk_startdateid
                  AND TSM.deletedflag = 0
       INNER JOIN [dbo].[trk_trackerfeatures] TF
               ON TF.trk_startdateid = TS.id
                  AND TF.deletedflag = 0
       INNER JOIN [dbo].[trk_trackerfeatures_lk] TFLK
               ON TFLK.id = TF.trk_feature_lkid
WHERE  TS.deletedflag = 0
       AND TF.applicabletoproject = 1
       AND TF.readyforwork = CASE  -- HERE IS THE PROBLEM
                               WHEN TF.trk_trackerstatus_lkid2 IS NULL THEN 0
                               ELSE 1
                             END
       AND TF.datestamp = (SELECT Max(TF2.datestamp)
                           FROM   [dbo].[trk_trackerfeatures] TF2
                                  INNER JOIN [dbo].[trk_trackerfeatures_lk] TFLK2
                                          ON TFLK2.id = TF2.trk_feature_lkid
                           WHERE  TF.trk_startdateid = TF2.trk_startdateid
                                  AND TFLK2.trk_trackergroup_lkid = TFLK.trk_trackergroup_lkid)
GROUP  BY TS.id,
          TSM.mappingtypeid,
          TSM.maptoid,
          TFLK.trk_trackergroup_lkid,
          TF.datestamp

It functions as a 'parent' in the sense that it grabs the latest inserted data-set (using DateStamp) from every single child-group. This is necessary to produce a parent-report in SSRS report at a later time, but at the moment my problem (as mentioned above) is the execution time.

I'd like to hear if there are any suggestions on how to decrease the execution time whilst maintaining the accuracy of the query.

Expected output:

enter image description here

Without the case I get this:

enter image description here

Upvotes: 0

Views: 88

Answers (2)

Marc Guillot
Marc Guillot

Reputation: 6465

The most problematic bit of your query seems to be the correlated subquery, because you must call it for every possible row.

You should optimize this first. To do so you can add indexes that the engine could use to quickly calculate that value on each row.

Based on your query I would add these two indexes multiples :

  1. On Table trackerfeatures, index fields : trk_startdateid, datestamp

  2. On Table trk_trackerfeatures_lk, index fields : id, trk_trackergroup_lkid

Upvotes: 0

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48207

Your problem is this condition cant use INDEX

AND TF.readyforwork = CASE  -- HERE IS THE PROBLEM
                        WHEN TF.trk_trackerstatus_lkid2 IS NULL THEN 0
                        ELSE 1
                      END

Try to change it to 

AND ( TF.readyforwork = 0 and TF.trk_trackerstatus_lkid2 IS NULL 
   OR TF.readyforwork = 1 and TF.trk_trackerstatus_lkid2 IS NOT NULL
    )

But again you should check with EXPLAIN ANALIZE to test if your query is using index or not.

Upvotes: 3

Related Questions