CTE very slow when Joined
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
Next to get to a solution, I would recommend to SELECT INTO the CTE into e temp table and join from there. From personal experience joining with CTE my query was returning for 5mins while simply inserting the data generated by CTE into a temp table brought it down to just 4secs. I was actually joining two CTEs together but I guess this would apply to all long running queries when a CTE is joined to a LONG table (especially outer joins).
--temp tables if needed to work with intermediate values
If object_id('tempdb..#p') is not null
drop table #p
;WITH cte as (
select * from t1
)
select *
into #p
from cte
--then use the temp table as you would normally use the CTE
select * from #p