6

My execution plan is posted on PasteThePlan.com. The second nested loop is warning that there is no join predicate. I'm still new to this so I'm confused. Is that because I am using a CTE to determine the values for two of the attributes? This presentation table is used in more that a few reports, and I don't want to create a second one for this very unique one.

Here's the query:

WITH Accumulators AS
(
 SELECT DISTINCT AreaNum, SuperintendentNum, SuperName
   FROM FI.SLAnalysis
  WHERE SuperName LIKE '%ACCUM%'
)
SELECT
    slard.[IO]
  , slard.PhaseName             AS PhaseName
  , slard.JobNum              AS JobNum
--  , slard.JobName              AS JobName
  , CASE
   WHEN SUBSTRING(JobNum, 5, 2) IN ('05', '06')
    THEN ac.SuperintendentNum
   ELSE slard.SuperNum
     END                AS SuperNum
  , CASE
   WHEN SUBSTRING(JobNum, 5, 2) IN ('05', '06')
    THEN ac.SuperName
   ELSE slard.SuperName
     END                AS SuperName
  , slard.AreaNum              AS AreaNum
  , slard.AreaName             AS AreaName
  , fp.WeekEndDate             AS WeekEndDate

  -- WTD Metrics
  , slard.WeeklyRevenue            AS WTDRevenue
  , slard.WeeklyCost             AS WTDCost  
  , slard.WeeklyRevenue - slard.WeeklyCost       AS WTDGP
  , CASE
   WHEN slard.WeeklyRevenue = .01 THEN 0
   ELSE slard.WeeklyRevenue
     END                AS WTDRatio
  , slard.Weekly_PER             AS WTDGPPer
  , slard.WeeklyStandOvrheadAdmin          AS WTDOverAdmin
  , slard.WeeklyStandOvrheadEquip          AS WTDOverEquip
  , (slard.WeeklyRevenue
   -slard.WeeklyCost
   -slard.WeeklyStandOvrheadAdmin
   -slard.WeeklyStandOvrheadEquip)         AS WTDOHGP

  --MTD Metrics      
  , slard.MTDRevenue             AS MTDRevenue
  , slard.MTDCost              AS MTDCost
  , slard.MTDRevenue - MTDCost          AS MTD_GP
  , CASE 
   WHEN slard.MTDRevenue <= .05 AND slard.MTDRevenue > 0 THEN 0
   ELSE slard.MTDRevenue
     END                AS MTDRatio
  , CASE
   WHEN slard.MTDRevenue <> 0
    THEN CAST(slard.MTDCost / slard.MTDRevenue AS DECIMAL(18, 2))
   ELSE CAST(0.00 AS DECIMAL(18, 2))
     END                AS MTDGPPer
  , slard.MonthlyStandOvrheadAdmin         AS MTDOverAdmin
  , slard.MonthlyStandOvrheadEquip         AS MTDOverEquip
  , (slard.MTDRevenue
   -slard.MTDCost
   -slard.MonthlyStandOvrheadAdmin
   -slard.MonthlyStandOvrheadEquip)        AS MTDOHGP
  -- YTD Metrics
  , slard.YTDRevenue             AS YTDRevenue
  , slard.YTDCost              AS YTDCost
  , slard.YTDRevenue - YTDCost          AS YTD_GP
  , CASE
   WHEN slard.YTDRevenue <= .53 AND slard.YTDRevenue > 0 THEN 0
   ELSE slard.YTDRevenue
     END                 AS YTDRatio
  , slard.YTDStandOvrheadAdmin          AS YTDOverAdmin
  , slard.YTDStandOvrheadEquip          AS YTDOverEquip
  , (slard.YTDRevenue
   -slard.YTDCost
   -slard.YTDStandOvrheadAdmin
   -slard.YTDStandOvrheadEquip)         AS YTDOHGP
  FROM [DW].[FI].[SLAnalysisReportData] slard
 INNER JOIN Accumulators ac
   ON slard.AreaNum = ac.AreaNum
 INNER JOIN CORP.FiscalPeriod fp
  ON slard.FY = fp.FY
   AND slard.[Period] = fp.[Period]
WHERE slard.FY = 2018
 AND slard.[Period]  = 3
    AND slard.AreaNum IN ('40')
   AND NOT (    WeeklyRevenue = 0
            AND WeeklyCost = 0
            AND WeeklyStandOvrheadAdmin = 0
            AND WeeklyStandOvrheadEquip = 0
            AND MTDRevenue = 0
            AND MTDCost = 0
            AND MonthlyStandOvrheadAdmin = 0
            AND MonthlyStandOvrheadEquip = 0
            AND YTDRevenue = 0
            AND YTDCost = 0
            AND YTDStandOvrheadAdmin = 0
            AND YTDStandOvrheadEquip = 0
           )
0
10

It's happening here because the predicate slard.AreaNum IN ('40') is pushed to both ends of the Nested Loop Join operation.

I have a blog post about this same thing, but what it boils down to is that your WHERE clause matches a JOIN clause ON slard.AreaNum = ac.AreaNum

In these cases, the optimizer can push SARGable predicates to the the index access (seek or scan), because it's assured that whichever values come out will match.

This is called an implied predicate, and you can read more about them here:

Craig Freedman

Some Bozo

You can ignore the warning in this case.

Hope this helps!

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.