title: CTE very slow when Joined
body: <p>I have posted something similar before, but I am approaching this from a different direction now so I opened a new question. I hope this is OK.</p>
  
  <p>I have been working with a CTE that creates a sum of charges based on a Parent Charge. The SQL and details can be seen here:</p>
  
  <p><a href="https://stackoverflow.com/questions/22802484/cte-index-recommendations-on-multiple-keyed-table">CTE Index recommendations on multiple keyed table</a></p>
  
  <p>I don't think I am missing anything on the CTE, but I am getting a problem when I use it with a big table of data (3.5 million rows).</p>
  
  <p>The table <code>tblChargeShare</code> contains some other information that I need, such as an <code>InvoiceID</code>, so I placed my CTE in a view <code>vwChargeShareSubCharges</code> and joined it to the table.</p>
  
  <p>The query:</p>
  
  <pre><code>Select t.* from vwChargeShareSubCharges t
  inner join 
  tblChargeShare  s 
  on t.CustomerID = s.CustomerID 
  and t.MasterChargeID = s.ChargeID 
  Where  s.ChargeID = 1291094
  </code></pre>
  
  <p>Returns a result in a few ms. </p>
  
  <p>The query:</p>
  
  <pre><code>Select ChargeID from tblChargeShare Where InvoiceID = 1045854
  </code></pre>
  
  <p>Returns 1 row:</p>
  
  <pre><code>1291094
  </code></pre>
  
  <p>But the query:</p>
  
  <pre><code>Select t.* from vwChargeShareSubCharges t
  inner join 
  tblChargeShare  s 
  on t.CustomerID = s.CustomerID 
  and t.MasterChargeID = s.ChargeID 
  Where  InvoiceID = 1045854
  </code></pre>
  
  <p>Takes 2-3 minutes to run.</p>
  
  <p>I saved the execution plans and loaded them into SQL Sentry. The Tree for the fast query looks like this:</p>
  
  <p><img src="https://i.stack.imgur.com/O6g31.png" alt="Fast Query"></p>
  
  <p>The plan from the slow query is:</p>
  
  <p><img src="https://i.stack.imgur.com/mk6nL.png" alt="Slow Query"></p>
  
  <p>I have tried reindexing, running the query through tuning advisor and various combinations of sub queries. Whenever the join contains anything other than the PK, the query is slow.</p>
  
  <p>I had a similar question here:</p>
  
  <p><a href="https://stackoverflow.com/questions/12362221/sql-server-query-time-out-depending-on-where-clause">SQL Server Query time out depending on Where Clause</a></p>
  
  <p>Which used functions to do the summimg of child rows instead of a CTE. This is the rewrite using CTE to try and avoid the same problem I am now experiencing. I have read the responses in that answer but I am none the wiser - I read some information about hints and parameters but I can't make it work. I had thought that rewriting using a CTE would solve my problem. The query is fast when running on a tblCharge with a few thousand rows.</p>
  
  <p>Tested in both SQL 2008 R2 and SQL 2012</p>
  
  <p>Edit:</p>
  
  <p>I have condensed the query into a single statement, but the same issue persists:</p>
  
  <pre><code>WITH RCTE AS
  (
  SELECT  ParentChargeId, s.ChargeID, 1 AS Lvl, ISNULL(TotalAmount, 0) as TotalAmount,  ISNULL(s.TaxAmount, 0) as TaxAmount,  
  ISNULL(s.DiscountAmount, 0) as DiscountAmount, s.CustomerID, c.ChargeID as MasterChargeID
  from tblCharge c inner join tblChargeShare s
  on c.ChargeID = s.ChargeID Where s.ChargeShareStatusID &lt; 3 and ParentChargeID is NULL
  
  UNION ALL
  
  SELECT c.ParentChargeID, c.ChargeID, Lvl+1 AS Lvl, ISNULL(s.TotalAmount, 0),  ISNULL(s.TaxAmount, 0),  ISNULL(s.DiscountAmount, 0) , s.CustomerID 
  , rc.MasterChargeID 
  from tblCharge c inner join tblChargeShare s
  on c.ChargeID = s.ChargeID
  INNER JOIN RCTE rc ON c.PArentChargeID = rc.ChargeID and s.CustomerID = rc.CustomerID  Where s.ChargeShareStatusID &lt; 3 
  )
  
  Select MasterChargeID as ChargeID, rcte.CustomerID, Sum(rcte.TotalAmount) as TotalCharged, Sum(rcte.TaxAmount) as TotalTax, Sum(rcte.DiscountAmount) as TotalDiscount
  from RCTE inner join tblChargeShare s on rcte.ChargeID = s.ChargeID and RCTE.CustomerID = s.CustomerID 
  Where InvoiceID = 1045854
  Group by MasterChargeID, rcte.CustomerID 
  GO
  </code></pre>
  
  <hr>
  
  <p>Edit: 
  More playing around,I just don't understand this.</p>
  
  <p>This query is instant (2ms):</p>
  
  <pre><code>Select t.* from
  vwChargeShareSubCharges t
  Where  t.MasterChargeID = 1291094
  </code></pre>
  
  <p>Whereas this takes 3 minutes:</p>
  
  <pre><code>DECLARE @ChargeID int = 1291094
  
  Select t.* from
  vwChargeShareSubCharges t
  Where  t.MasterChargeID = @ChargeID
  </code></pre>
  
  <p>Even if I put heaps of numbers in an "In", the query is still instant:</p>
  
  <pre><code>Where  t.MasterChargeID in (1291090, 1291091, 1291092, 1291093,  1291094, 1291095, 1291096, 1291097, 1291098, 1291099, 129109)
  </code></pre>
  
  <hr>
  
  <p>Edit 2:</p>
  
  <p>I can replicate this from scratch using this example data:</p>
  
  <p>I have created some dummy data to replicate the issue. It isn't so significant, as I only added 100,000 rows, but the bad execution plan still happens (run in SQLCMD mode):</p>
  
  <pre><code>CREATE TABLE [tblChargeTest](
  [ChargeID] [int] IDENTITY(1,1) NOT NULL,
  [ParentChargeID] [int] NULL,
  [TotalAmount] [money] NULL,
  [TaxAmount] [money] NULL,
  [DiscountAmount] [money] NULL,
  [InvoiceID] [int] NULL,
  CONSTRAINT [PK_tblChargeTest] PRIMARY KEY CLUSTERED 
  (
  [ChargeID] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,     ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  ) ON [PRIMARY]
  END
  GO
  
  Insert into tblChargeTest
  (discountAmount, TotalAmount, TaxAmount)
  Select ABS(CHECKSUM(NewId())) % 10, ABS(CHECKSUM(NewId())) % 100, ABS(CHECKSUM(NewId())) % 10
  GO 100000
  
  Update tblChargeTest
  Set ParentChargeID = (ABS(CHECKSUM(NewId())) % 60000) + 20000
  Where ChargeID = (ABS(CHECKSUM(NewId())) % 20000)
  GO 5000
  
  CREATE VIEW [vwChargeShareSubCharges] AS
  WITH RCTE AS
  (
  SELECT  ParentChargeId, ChargeID, 1 AS Lvl, ISNULL(TotalAmount, 0) as TotalAmount,   ISNULL(TaxAmount, 0) as TaxAmount,  
  ISNULL(DiscountAmount, 0) as DiscountAmount,  ChargeID as MasterChargeID
  FROM tblChargeTest Where ParentChargeID is NULL
  
  UNION ALL
  
  SELECT rh.ParentChargeID, rh.ChargeID, Lvl+1 AS Lvl, ISNULL(rh.TotalAmount, 0),    ISNULL(rh.TaxAmount, 0),  ISNULL(rh.DiscountAmount, 0) 
  , rc.MasterChargeID 
  FROM tblChargeTest rh
  INNER JOIN RCTE rc ON rh.PArentChargeID = rc.ChargeID --and rh.CustomerID =  rc.CustomerID 
  )
  
  Select MasterChargeID,  ParentChargeID, ChargeID, TotalAmount, TaxAmount, DiscountAmount , Lvl
  FROM  RCTE r 
  GO
  </code></pre>
  
  <p>Then run these two queries:</p>
  
  <pre><code>--Slow Query:
  Declare @ChargeID int = 60900
  
  Select *
  from [vwChargeShareSubCharges]
  Where MasterChargeID = @ChargeID
  
  --Fast Query:
  Select *
  from [vwChargeShareSubCharges]
  Where MasterChargeID = 60900
  </code></pre>