CTE very slow when Joined

Question

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. 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: CTE Index recommendations on multiple keyed table 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). The table `tblChargeShare` contains some other information that I need, such as an `InvoiceID`, so I placed my CTE in a view `vwChargeShareSubCharges` and joined it to the table. The query: ``` Select t.* from vwChargeShareSubCharges t inner join tblChargeShare s on t.CustomerID = s.CustomerID and t.MasterChargeID = s.ChargeID Where s.ChargeID = 1291094 ``` Returns a result in a few ms. The query: ``` Select ChargeID from tblChargeShare Where InvoiceID = 1045854 ``` Returns 1 row: ``` 1291094 ``` But the query: ``` Select t.* from vwChargeS…

Accepted Answer

The best SQL Server can do for you here is to push the filter on `ChargeID` down into the anchor part of the recursive CTE inside the view. That allows a seek to find the only row you need to build the hierarchy from. When you provide the parameter as a constant value SQL Server can make that optimization (using a rule called `SelOnIterator`, for those who are interested in that sort of thing):

When you use a local variable it can not do this, so the predicate on `ChargeID` gets stuck outside the view (which builds the full hierarchy starting from all `NULL` ids):

One way to get the optimal plan when using a variable is to force the optimizer to compile a fresh plan on every execution. The resulting plan is then tailored to the specific value in the variable at execution time. This is achieved by adding an `OPTION (RECOMPILE)` query hint:

```
Declare @ChargeID int = 60900;

-- Produces a fast execution plan, at the cost of a compile on every execution
Select *
from [vwChargeShareSubCharges]
Where MasterChargeID = @ChargeID
OPTION (RECOMPILE);
```

A second option is to change the view into an inline table function. This allows you to specify the position of the filtering predicate explicitly:

```
CREATE FUNCTION [dbo].[udfChargeShareSubCharges]
(
    @ChargeID int
)
RETURNS TABLE AS RETURN
(
  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 
  AND ChargeID = @ChargeID -- Filter placed here explicitly

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 
)
```

Use it like this:

```
Declare @ChargeID int = 60900

select *
from dbo.udfChargeShareSubCharges(@ChargeID)
```

The query can also benefit from an index on `ParentChargeID`.

```
create index ix_ParentChargeID on tblChargeTest(ParentChargeID)
```

Here is another answer about a similar optimization rule in a similar scenario. Optimizing Execution Plans for Parameterized T-SQL Queries Containing Window Functions

CTE very slow when Joined

Solution

Problem

Related problems