Why is my refactored SQL less efficient than the original version?

Question

I'm trying to optimise a stored procedure and after looking at the execution plan and execution times I'm surprised by the results. Can anyone explain them.

The original SQL I wrote has 2 almost identical selects that I then pull together in a CTE, I tried to refactor that so the main work was done once, populating a table variable so I could then have 2 smaller selects that filtered the data the way I needed. The sole difference between the 2 select statements is the value that's passed to TVF_GetChildGroups in the 1st select @AreaID represents the area of reporting for the current context and in the 2nd select @RootReportLevelID is simply all areas. So it's the peers of the user context and the aim is to allow comparison between your scores and the average of everyone else.

DECLARE @Scores TABLE
(
  ShortName VARCHAR(50) ,
  PCTMax INT ,
  PCTAvg INT ,
  PCTMin INT ,
  ALLAvg INT ,
  AllMax INT ,
  AllMin INT
)
INSERT  INTO @Scores
    SELECT  T2.ShortName ,
            MAX(T1.MeridianScore) AS PCTMax ,
            CAST(( CAST(SUM(T1.contribution) AS DECIMAL) / CAST(SUM(T1.maxvalue) AS DECIMAL) * 100 ) AS DECIMAL(5, 2)) AS PCTAvg ,
            MIN(T1.MeridianScore) AS PCTMin ,
            CAST(( CAST(SUM(T2.contribution) AS DECIMAL) / CAST(SUM(T2.maxvalue) AS DECIMAL) * 100 ) AS DECIMAL(5, 2)) AS AllAvg ,
            MAX(T2.MeridianScore) AS AllUpperScore ,
            MIN(T2.MeridianScore) AS AllLowerScore
    FROM    ( SELECT    US.PKID ,
                        PT.ShortName ,
                        CAST(( CAST(SUM(RES.contribution) AS DECIMAL) / CAST(SUM(RES.maxvalue) AS DECIMAL) * 100 ) AS DECIMAL(5, 2)) AS MeridianScore ,
                        SUM(Contribution) AS Contribution ,
                        SUM(MaxValue) AS MaxValue
              FROM      tblUploadedScorecards AS US
                        INNER JOIN tblUploadedScoreCardResults AS RES ON US.PKID = RES.FKUploadedScoreCardID
                        INNER JOIN tblUploadedScorecardHeaders AS USH ON USH.FKUploadedScorecardID = US.PKID
                        INNER JOIN @ProviderTable AS PT ON USH.HeaderValue = PT.FullName
                        INNER JOIN TVF_GetChildGroups(@AreaID) AS TGCG ON TGCG.GroupName = US.Branch
              WHERE     US.FKScoreCardID = 185
                        AND reviewed = 1
                        AND ShopDate BETWEEN @StartDate AND @EndDate
                        AND RES.Rating <> 'I'
                        AND USH.FKScorecardHeaderID = 71
              GROUP BY  US.PKID ,
                        PT.ShortName ) AS T1
            RIGHT JOIN ( SELECT US.PKID ,
                                PT.ShortName ,
                                CAST(( CAST(SUM(RES.contribution) AS DECIMAL) / CAST(SUM(RES.maxvalue) AS DECIMAL) * 100 ) AS DECIMAL(5, 2)) AS MeridianScore ,
                                SUM(Contribution) AS Contribution ,
                                SUM(MaxValue) AS MaxValue
                         FROM   tblUploadedScorecards AS US
                                INNER JOIN tblUploadedScoreCardResults AS RES ON US.PKID = RES.FKUploadedScoreCardID
                                INNER JOIN tblUploadedScorecardHeaders AS USH ON USH.FKUploadedScorecardID = US.PKID
                                INNER JOIN @ProviderTable AS PT ON USH.HeaderValue = PT.FullName
                                INNER JOIN TVF_GetChildGroups(@RootReportLevelID) AS TGCG ON TGCG.GroupName = US.Branch
                         WHERE  US.FKScoreCardID = 185
                                AND reviewed = 1
                                AND ShopDate BETWEEN @StartDate AND @EndDate
                                AND RES.Rating <> 'I'
                                AND USH.FKScorecardHeaderID = 71
                         GROUP BY US.PKID ,
                                PT.ShortName ) AS T2 ON T1.ShortName = T2.ShortName
    GROUP BY T2.ShortName

So I refactored out the commonality into a table variable against which I could apply the join to the TVF later in the hopes of not needing to hit the tables so much and ended up with this:

DECLARE @PreHierarchyResults TABLE 
(
PKID INT ,
Branch VARCHAR(150) ,
ShortName VARCHAR(50) ,
Contribution DECIMAL(20,3),
MaxValue DECIMAL(20,3)
)

INSERT INTO @PreHierarchyResults
    ( PKID ,
      Branch ,
      ShortName ,
      Contribution ,
      MaxValue )
SELECT  US.PKID ,
    US.Branch ,
    PT.ShortName ,
    Contribution AS Contribution ,
    MaxValue AS MaxValue
FROM    tblUploadedScorecards AS US
    INNER JOIN tblUploadedScoreCardResults AS RES ON US.PKID = RES.FKUploadedScoreCardID
    INNER JOIN tblUploadedScorecardHeaders AS USH ON USH.FKUploadedScorecardID = US.PKID
    INNER JOIN @ProviderTable AS PT ON USH.HeaderValue = PT.FullName
WHERE   US.FKScoreCardID = 185
    AND reviewed = 1
    AND ShopDate BETWEEN @StartDate AND @EndDate
    AND RES.Rating <> 'I'
    AND USH.FKScorecardHeaderID = 71


INSERT  INTO @Scores
    SELECT  T2.ShortName ,
            MAX(T1.MeridianScore) AS PCTMax ,
            CAST(( CAST(SUM(T1.contribution) AS DECIMAL) / CAST(SUM(T1.maxvalue) AS DECIMAL) * 100 ) AS DECIMAL(5, 2)) AS PCTAvg ,
            MIN(T1.MeridianScore) AS PCTMin ,
            CAST(( CAST(SUM(T2.contribution) AS DECIMAL) / CAST(SUM(T2.maxvalue) AS DECIMAL) * 100 ) AS DECIMAL(5, 2)) AS AllAvg ,
            MAX(T2.MeridianScore) AS AllUpperScore ,
            MIN(T2.MeridianScore) AS AllLowerScore
    FROM    ( SELECT    PHR.PKID ,
                        ShortName ,
                        CAST(( CAST(SUM(contribution) AS DECIMAL) / CAST(SUM(maxvalue) AS DECIMAL) * 100 ) AS DECIMAL(5, 2)) AS MeridianScore ,
                        SUM(Contribution) AS Contribution ,
                        SUM(MaxValue) AS MaxValue
              FROM      @PreHierarchyResults AS PHR 
                        INNER JOIN TVF_GetChildGroups(@AreaID) AS TGCG ON TGCG.GroupName = PHR.Branch 
              GROUP BY PHR.PKID , ShortName) AS T1
            RIGHT JOIN ( SELECT PHR2.PKID ,
                                ShortName ,
                                CAST(( CAST(SUM(Contribution) AS DECIMAL) / CAST(SUM(Maxvalue) AS DECIMAL) * 100 ) AS DECIMAL(5, 2)) AS MeridianScore ,
                                SUM(Contribution) AS Contribution ,
                                SUM(MaxValue) AS MaxValue
                         FROM   @PreHierarchyResults AS PHR2
                                INNER JOIN TVF_GetChildGroups(@RootReportLevelID) AS TGCG ON TGCG.GroupName = PHR2.Branch 
                         GROUP BY PHR2.PKID , ShortName) AS T2 ON T1.ShortName = T2.ShortName
    GROUP BY T2.ShortName

For the original SQL I get execution times:

SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 11 ms.
Table '#012F94F2'. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblUploadedScoreCardResults'. Scan count 4456, logical reads 13943, physical reads 40, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Worktable'. Scan count 6, logical reads 29800, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblUserGroups'. Scan count 2, logical reads 118, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblUploadedScorecards'. Scan count 0, logical reads 29496, physical reads 8, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblUploadedScorecardHeaders'. Scan count 192, logical reads 746, physical reads 5, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#7D5F040E'. Scan count 186, logical reads 372, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

and after the refactoring I get:

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 10 ms.
Table '#48563EF2'. Scan count 0, logical reads 5106, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblUploadedScoreCardResults'. Scan count 185, logical reads 614, physical reads 41, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblUploadedScorecards'. Scan count 0, logical reads 370, physical reads 8, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblUploadedScorecardHeaders'. Scan count 7, logical reads 23, physical reads 5, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#439189D5'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(5059 row(s) affected)
(1 row(s) affected)

SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 199 ms.
Table '#47621AB9'. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 114, logical reads 1770, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblUserGroups'. Scan count 112, logical reads 998, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#48563EF2'. Scan count 112, logical reads 5376, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Looking at the output I think it's clear that the first version is more efficient, but I just don't understand why it should be the case as it appears to hit 4 tables twice whereas in the 2nd version those tables are only hit the one time.

John Dewey John Dewey · Accepted Answer · 2012-07-06T18:02:15

I would also compare the actual execution plans between your original and revised queries.

Also check the indexes on tblUploadedScorecards, tblUploadedScoreCardResults, and tblUploadedScorecardHeaders. After caching data from those tables into @PreHierarchyResults, you might be losing the benefits of some covering indexes on the join columns in your second query.

Why is my refactored SQL less efficient than the original version?

1 Answers