title: Nested Loops (Inner Join) cost 83%. Is any way to rewrite it somehow?
body: <p>The SP runs very slow. When I look at execution plan - I can see that 83% of its cost goes to <code>Nested Loops (Inner Join)</code>
  <a href="https://i.stack.imgur.com/tr3ln.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/tr3ln.png" alt="enter image description here"></a></p>
  
  <p>Is any chance to substitute it somehow?</p>
  
  <p>Here is my SP</p>
  
  <pre><code>ALTER PROCEDURE [dbo].[EarningPlazaCommercial] 
      @State      varchar(50),
      @StartDate  datetime,
      @EndDate    datetime,
      @AsOfDate   datetime,
      @ClassCode  nvarchar(max),
      @Coverage   varchar(100)
  AS
  BEGIN
  SET NOCOUNT ON;  
  CREATE TABLE #PolicyNumbers  (PolicyNumber varchar(50))
  INSERT INTO #PolicyNumbers SELECT  PolicyNumber FROM tblClassCodesPlazaCommercial T1 
                                  WHERE NOT EXISTS    (
                                                      SELECT 1 FROM tblClassCodesPlazaCommercial T2  
                                                      WHERE  T1.PolicyNumber = T2.PolicyNumber
                                                      AND ClassCode  IN 
                                                      (SELECT * FROM [dbo].[StringOfStringsToTable](@ClassCode,','))
                                                      )   
  CREATE CLUSTERED INDEX IDX_C_PolicyNumbers_PolicyNumber ON #PolicyNumbers(PolicyNumber)
  
  ; WITH Earned_to_date AS (
     SELECT Cast(@AsOfDate AS DATE) AS Earned_to_date
  ), policy_data AS (
      SELECT
              PolicyNumber
  ,           Cast(PolicyEffectiveDate AS DATE) AS PolicyEffectiveDate
  ,           Cast(PolicyExpirationDate AS DATE) AS PolicyExpirationDate
  ,           WrittenPremium
       FROM   PlazaInsuranceWPDataSet pid
       WHERE  NOT EXISTS (SELECT PolicyNumber FROM #PolicyNumbers pn WHERE pn.PolicyNumber = pid.PolicyNumber)
              AND State IN (SELECT * FROM [dbo].[StringOfStringsToTable](@State,',')) 
              AND Coverage    IN (SELECT * FROM [dbo].[StringOfStringsToTable](@Coverage,','))        
  ) 
  </code></pre>
  
  <p>...</p>
  
  <p>--Part of the <code>Execution Plan</code>
  <a href="https://i.stack.imgur.com/9x3hx.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/9x3hx.png" alt="enter image description here"></a></p>
  
  <p><a href="https://i.stack.imgur.com/Zx6dQ.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Zx6dQ.png" alt="enter image description here"></a></p>
  
  <p>Here I am adding my full query for Stored Procedure:</p>
  
  <pre><code>ALTER PROCEDURE [dbo].[EarningPlazaCommercial] 
      @State      varchar(50),
      @StartDate  datetime,
      @EndDate    datetime,
      @AsOfDate   datetime,
      @ClassCode  nvarchar(max),
      @Coverage   varchar(100)
  AS
  BEGIN
  SET NOCOUNT ON;  
  CREATE TABLE #PolicyNumbers  (PolicyNumber varchar(50))
  INSERT INTO #PolicyNumbers SELECT  PolicyNumber FROM tblClassCodesPlazaCommercial T1 
                                  WHERE NOT EXISTS    (
                                                      SELECT 1 FROM tblClassCodesPlazaCommercial T2  
                                                      WHERE  T1.PolicyNumber = T2.PolicyNumber
                                                      AND ClassCode  IN 
                                                      (SELECT * FROM [dbo].[StringOfStringsToTable](@ClassCode,','))
                                                      )   
  CREATE CLUSTERED INDEX IDX_C_PolicyNumbers_PolicyNumber ON #PolicyNumbers(PolicyNumber)
  
  ; WITH Earned_to_date AS (
     SELECT Cast(@AsOfDate AS DATE) AS Earned_to_date
     --SELECT @AsOfDate AS Earned_to_date
  ), policy_data AS (
      SELECT
              PolicyNumber
  ,           Cast(PolicyEffectiveDate AS DATE) AS PolicyEffectiveDate
  ,           Cast(PolicyExpirationDate AS DATE) AS PolicyExpirationDate
  ,           WrittenPremium
  --,         State
       FROM   PlazaInsuranceWPDataSet pid
       WHERE  NOT EXISTS (SELECT PolicyNumber FROM #PolicyNumbers pn WHERE pn.PolicyNumber = pid.PolicyNumber)
              AND State IN (SELECT * FROM [dbo].[StringOfStringsToTable](@State,',')) 
              AND Coverage    IN (SELECT * FROM [dbo].[StringOfStringsToTable](@Coverage,','))        
  ) 
  
  , digits AS (
  SELECT digit
     FROM (VALUES (0), (1), (2), (3), (4)
  ,      (5), (6), (7), (8), (9)) AS z2 (digit)
  ), numbers AS (
  SELECT 1000 * d4.digit + 100 * d3.digit + 10 * d2.digit + d1.digit AS number
      FROM digits AS d1
      CROSS JOIN digits AS d2
      CROSS JOIN digits AS d3
      CROSS JOIN digits AS d4
  ), calendar AS (
  SELECT
      DateAdd(month, number, '1753-01-01') AS month_of
  ,   DateAdd(month, number, '1753-02-01') AS month_after
      FROM numbers
  ), policy_dates AS (
  SELECT
     PolicyNumber
  ,   CASE
          WHEN month_of &lt; PolicyEffectiveDate THEN PolicyEffectiveDate
          ELSE month_of
      END AS StartRiskMonth
  ,   CASE
         WHEN PolicyExpirationDate &lt; month_after THEN PolicyExpirationDate
         WHEN Earned_to_date.Earned_to_date &lt; month_after THEN Earned_to_date
         ELSE month_after
      END AS EndRiskMonth
  ,   DateDiff(day, PolicyEffectiveDate, PolicyExpirationDate) AS policy_days
  ,   WrittenPremium
      FROM policy_data
      JOIN calendar
          ON (policy_data.PolicyEffectiveDate &lt; calendar.month_after
          AND calendar.month_of &lt; policy_data.PolicyExpirationDate)
      CROSS JOIN Earned_to_date
      WHERE  month_of &lt; Earned_to_date
  )
  SELECT      --PolicyEffectiveDate,
              --PolicyExpirationDate,
              --PolicyNumber,
              Year(StartRiskMonth) as YearStartRisk, 
              Month(StartRiskMonth) as MonthStartRisk,
              c.YearNum,c.MonthNum,
              convert(varchar(7), StartRiskMonth, 120) as RiskMonth,
              sum(WrittenPremium * DateDiff(day, StartRiskMonth, EndRiskMonth) / policy_days) as EarnedPremium
  FROM        tblCalendar  c
  LEFT  JOIN policy_dates l ON c.YearNum=Year(l.StartRiskMonth) and c.MonthNum = Month(l.StartRiskMonth) AND l.StartRiskMonth BETWEEN @StartDate AND  @EndDate
  WHERE c.YearNum Not IN (2017) --and PolicyNumber = 'PACA1000191-00'
  GROUP BY    convert(varchar(7), StartRiskMonth, 120),
              Year(StartRiskMonth) , Month(StartRiskMonth),
              c.YearNum,c.MonthNum--,PolicyNumber--,PolicyEffectiveDate,PolicyExpirationDate
  ORDER BY     c.YearNum,c.MonthNum
              --convert(varchar(7), StartRiskMonth, 120)
  DROP TABLE #PolicyNumbers
  END 
  GO
  </code></pre>
  
  <p>Full actual execution plan from production link:</p>
  
  <p><a href="https://aligngeneral-my.sharepoint.com/personal/oserdyuk_aligngeneral_com/_layouts/15/guestaccess.aspx?guestaccesstoken=VuiFBK6zMim%2fyIh%2bNrQaOcgrg%2fpIJNKDTStt765cBfQ%3d&amp;docid=1abc31e385da14574a930e99e22f00c7b&amp;rev=1&amp;expiration=2017-01-06T22%3a20%3a34.000Z" rel="nofollow noreferrer">https://aligngeneral-my.sharepoint.com/personal/oserdyuk_aligngeneral_com/_layouts/15/guestaccess.aspx?guestaccesstoken=VuiFBK6zMim%2fyIh%2bNrQaOcgrg%2fpIJNKDTStt765cBfQ%3d&amp;docid=1abc31e385da14574a930e99e22f00c7b&amp;rev=1&amp;expiration=2017-01-06T22%3a20%3a34.000Z</a></p>
  
  <p>And this is how my TempDB configured:
  <a href="https://i.stack.imgur.com/fS0KM.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/fS0KM.png" alt="enter image description here"></a></p>