Cte Very Slow When Joined

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

Pushed predicate with a constant value

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

Stuck Predicate

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 slow performance on Left join

As @Habo mentioned, we need the actual execution plan (e.g. run the query with "include actual execution plan" turned on.) I looked over what you posted and there is nothing there that will explain the problem. The difference with the actual plan vs the estimated plan is that the actual number of rows retrieved are recorded; this is vital for troubleshooting poorly performing queries.

That said, I do see a HUGE problem with both queries. It's a problem that, once fixed will, improve both queries to less than a second. Your query is leveraging two scalar user Defined Functions (UDFs): dbo.fn_WorkDaysAge & dbo.fn_WorkDate15. Scalar UDFs ruin
everything. Not only are they slow, they force a serial execution plan which makes any query they are used in much slower.

I don't have the code for dbo.fn_WorkDaysAge or dbo.fn_WorkDate15 I have my own "WorkDays" function which is inline (code below). The syntax is a little different but the performance benefits are worth the effort. Here's the syntax difference:

-- Scalar 
SELECT d.*, workDays = dbo.countWorkDays_scalar(d.StartDate,d.EndDate)
FROM   <sometable> AS d;

-- Inline version
SELECT d.*, f.workDays
FROM   <sometable> AS d
CROSS APPLY dbo.countWorkDays(d.StartDate,d.EndDate) AS f;

Here's a performance test I put together to show the difference between an inline version vs the scalar version:

-- SAMPLE DATA
IF OBJECT_ID('tempdb..#dates') IS NOT NULL DROP TABLE #dates;

WITH E1(x)  AS (SELECT 1 FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS x(x)),
     E3(x)  AS (SELECT 1 FROM E1 a, E1 b, E1 c),
     iTally AS (SELECT N=ROW_NUMBER() OVER (ORDER BY (SELECT 1)) FROM E3 a, E3 b)
SELECT TOP (100000) 
  StartDate = CAST(DATEADD(DAY,-ABS(CHECKSUM(NEWID())%1000),GETDATE()) AS DATE),
  EndDate   = CAST(DATEADD(DAY,+ABS(CHECKSUM(NEWID())%1000),GETDATE()) AS DATE)
INTO #dates
FROM iTally;

-- PERFORMANCE TESTS
PRINT CHAR(10)+'Scalar Version (always serial):'+CHAR(10)+REPLICATE('-',60);
GO
DECLARE @st DATETIME = GETDATE(), @workdays INT;
  SELECT @workdays = dbo.countWorkDays_scalar(d.StartDate,d.EndDate)
  FROM   #dates AS d;
PRINT DATEDIFF(MS,@st,GETDATE());
GO 3

PRINT CHAR(10)+'Inline Version:'+CHAR(10)+REPLICATE('-',60);
GO
DECLARE @st DATETIME = GETDATE(), @workdays INT;
  SELECT @workdays = f.workDays
  FROM   #dates AS d
  CROSS APPLY dbo.countWorkDays(d.StartDate,d.EndDate) AS f
PRINT DATEDIFF(MS,@st,GETDATE());
GO 3

Results:

Scalar Version (always serial):
------------------------------------------------------------
Beginning execution loop
380
363
350
Batch execution completed 3 times.

Inline Version:
------------------------------------------------------------
Beginning execution loop
47
47
46
Batch execution completed 3 times.

As you can see - the inline version about 8 times faster than the scalar version. Replacing those scalar UDFs with an inline version will almost certainly speed this query up regardless of join type.

Other problems I see include:

I see a lot of Index scans, this is a sign you need more filtering and/or better indexes.
dbo.tblCrosswalkWghtPhnEffTarget does not have any indexes which means it will always get scanned.

Functions used for performance test:

-- INLINE VERSION
----------------------------------------------------------------------------------------------
IF OBJECT_ID('dbo.countWorkDays') IS NOT NULL DROP FUNCTION dbo.countWorkDays;
GO
CREATE FUNCTION dbo.countWorkDays (@startDate DATETIME, @endDate DATETIME) 
/*****************************************************************************************
[Purpose]:
 Calculates the number of business days between two dates (Mon-Fri) and excluded weekends.
 dates.countWorkDays does not take holidays into considerations; for this you would need a 
 seperate "holiday table" to perform an antijoin against.

 The idea is based on the solution in this article:
   https://www.sqlservercentral.com/Forums/Topic153606.aspx?PageIndex=16

[Author]:
 Alan Burstein

[Compatibility]:
 SQL Server 2005+

[Syntax]:
--===== Autonomous
 SELECT f.workDays
 FROM   dates.countWorkDays(@startdate, @enddate) AS f;

--===== Against a table using APPLY
 SELECT t.col1, t.col2, f.workDays
 FROM dbo.someTable t
 CROSS APPLY dates.countWorkDays(t.col1, t.col2) AS f;

[Parameters]:
  @startDate = datetime; first date to compare
  @endDate   = datetime; date to compare @startDate to

[Returns]:
 Inline Table Valued Function returns:
 workDays = int; number of work days between @startdate and @enddate

[Dependencies]:
 N/A

[Developer Notes]:
 1. NULL when either input parameter is NULL, 

 2. This function is what is referred to as an "inline" scalar UDF." Technically it's an
    inline table valued function (iTVF) but performs the same task as a scalar valued user
    defined function (UDF); the difference is that it requires the APPLY table operator
    to accept column values as a parameter. For more about "inline" scalar UDFs see this
    article by SQL MVP Jeff Moden: http://www.sqlservercentral.com/articles/T-SQL/91724/
    and for more about how to use APPLY see the this article by SQL MVP Paul White:
    http://www.sqlservercentral.com/articles/APPLY/69953/.

    Note the above syntax example and usage examples below to better understand how to
    use the function. Although the function is slightly more complicated to use than a
    scalar UDF it will yield notably better performance for many reasons. For example,
    unlike a scalar UDFs or multi-line table valued functions, the inline scalar UDF does
    not restrict the query optimizer's ability generate a parallel query execution plan.

 3. dates.countWorkDays requires that @enddate be equal to or later than @startDate. Otherwise
    a NULL is returned.

 4. dates.countWorkDays is NOT deterministic. For more deterministic functions see:
    https://msdn.microsoft.com/en-us/library/ms178091.aspx

[Examples]:
 --===== 1. Basic Use
 SELECT f.workDays 
 FROM   dates.countWorkDays('20180608', '20180611') AS f;

---------------------------------------------------------------------------------------
[Revision History]: 
 Rev 00 - 20180625 - Initial Creation - Alan Burstein
*****************************************************************************************/
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT workDays =
    -- If @startDate or @endDate are NULL then rerturn a NULL
  CASE WHEN SIGN(DATEDIFF(dd, @startDate, @endDate)) > -1 THEN
                (DATEDIFF(dd, @startDate, @endDate) + 1) --total days including weekends
               -(DATEDIFF(wk, @startDate, @endDate) * 2) --Subtact 2 days for each full weekend
    -- Subtract 1 when startDate is Sunday and Substract 1 when endDate is Sunday: 
    -(CASE WHEN DATENAME(dw, @startDate) = 'Sunday'   THEN 1 ELSE 0 END)
    -(CASE WHEN DATENAME(dw, @endDate)   = 'Saturday' THEN 1 ELSE 0 END)
  END;
GO    

-- SCALAR VERSION
----------------------------------------------------------------------------------------------
IF OBJECT_ID('dbo.countWorkDays_scalar') IS NOT NULL DROP FUNCTION dbo.countWorkDays_scalar;
GO
CREATE FUNCTION dbo.countWorkDays_scalar (@startDate DATETIME, @endDate DATETIME) 
RETURNS INT WITH SCHEMABINDING AS
BEGIN
  RETURN
  (
    SELECT workDays =
        -- If @startDate or @endDate are NULL then rerturn a NULL
      CASE WHEN SIGN(DATEDIFF(dd, @startDate, @endDate)) > -1 THEN
                    (DATEDIFF(dd, @startDate, @endDate) + 1) --total days including weekends
                   -(DATEDIFF(wk, @startDate, @endDate) * 2) --Subtact 2 days for each full weekend
        -- Subtract 1 when startDate is Sunday and Substract 1 when endDate is Sunday: 
        -(CASE WHEN DATENAME(dw, @startDate) = 'Sunday'   THEN 1 ELSE 0 END)
        -(CASE WHEN DATENAME(dw, @endDate)   = 'Saturday' THEN 1 ELSE 0 END)
      END
  );
END
GO

UPDATE BASED ON OP'S QUESTION IN THE COMMENTS:

First for the inline table valued function version of each function. Note that I'm using my own tables and don't have time to make the names match your environment but I did my best to include comments in the code. Also note that if, in your function, workingday = '1' is simply pulling weekdays then you'll find my function above to be a much faster alternative to your dbo.fn_WorkDaysAge function. If workingday = '1' also filters out holidays then it won't work.

CREATE FUNCTION dbo.fn_WorkDaysAge_itvf
(
 @first_date  DATETIME,
 @second_date DATETIME
)
RETURNS TABLE AS RETURN
SELECT  WorkDays = COUNT(*)
FROM    dbo.dimdate -- DateDimension
WHERE   DateValue   -- [date]
BETWEEN @first_date AND @second_date
AND     IsWeekend = 0 --workingday = '1'
GO

CREATE FUNCTION dbo.fn_WorkDate15_itvf
(
 @TauStartDate DATETIME
)
RETURNS TABLE AS RETURN
WITH DATES AS 
(
  SELECT 
  ROW_NUMBER() OVER(Order By DateValue Desc) as RowNum, DateValue
  FROM dbo.dimdate -- DateDimension
  WHERE DateValue BETWEEN @TauStartDate AND --GETDATE() testing below 
   CASE WHEN GETDATE() < @TauStartDate + 200 THEN GETDATE() ELSE @TauStartDate + 200 END
  AND IsWeekend = 0 --workingday = '1'
)
--Get the 15th businessday from the current date
SELECT DateValue
FROM  DATES
WHERE RowNum = 16;
GO

Now, to replace your scalar UDFs with the inline table valued functions, you would do this (note my comments):

WITH agent_split_stats AS ( 
Select
    racf,
    agent_stats.SkillGroupSkillTargetID,
    aht_target.EnterpriseName,
    aht_target.target,
    Sum(agent_stats.CallsHandled) as n_calls_handled,
    CASE WHEN (Sum(agent_stats.TalkInTime) + Sum(agent_stats.IncomingCallsOnHoldTime) + Sum(agent_stats.WorkReadyTime)) = 0 THEN 1 ELSE
        (Sum(agent_stats.TalkInTime) + Sum(agent_stats.IncomingCallsOnHoldTime) + Sum(agent_stats.WorkReadyTime)) END
    AS total_handle_time
from tblAceyusAgntSklGrp as agent_stats
INNER JOIN tblCrosswalkWghtPhnEffTarget as aht_target
  ON aht_target.SgId = agent_stats.SkillGroupSkillTargetID
  AND agent_stats.DateTime BETWEEN aht_target.StartDt and aht_target.EndDt
INNER JOIN tblAgentMetricCrosswalk as xwalk
  ON xwalk.SkillTargetID = agent_stats.SkillTargetID
INNER JOIN tblTauClassList AS T
  ON T.SaRacf = racf
-- INLINE FUNCTIONS HERE:
CROSS APPLY dbo.fn_WorkDaysAge_itvf(TauStart, GETDATE()) AS wd
CROSS APPLY dbo.fn_WorkDate15_itvf(TauStart)             AS w15
-- NEW WHERE CLAUSE:
WHERE       agent_stats.DateTime >= 
              CASE WHEN wd.workdays < 15 THEN TauStart ELSE w15.workdays END
And Graduated = 'No'
AND CallsHandled <> 0
AND Target is not null
Group By
racf, agent_stats.SkillGroupSkillTargetID, aht_target.EnterpriseName, aht_target.target
),
agent_split_stats_with_weight AS (
SELECT 
    agent_split_stats.*,
    agent_split_stats.n_calls_handled/SUM(agent_split_stats.n_calls_handled) OVER(PARTITION BY agent_split_stats.racf) AS [weight]
FROM agent_split_stats
),
agent_split_effectiveness AS 
(
  SELECT 
      agent_split_stats_with_weight.*,
      (((agent_split_stats_with_weight.target * agent_split_stats_with_weight.n_calls_handled) / 
         agent_split_stats_with_weight.total_handle_time)*100)*
         agent_split_stats_with_weight.weight AS effectiveness_sum
  FROM agent_split_stats_with_weight
),
agent_effectiveness AS
(
  SELECT 
      racf AS SaRacf,
      ROUND(SUM(effectiveness_sum),2) AS WpeScore
  FROM agent_split_effectiveness
  GROUP BY racf
),
tau AS
(
  SELECT L.SaRacf, TauStart, Goal as WpeGoal 
  ,CASE WHEN agent_effectiveness.WpeScore IS NULL THEN 1 ELSE WpeScore END as WpeScore
  FROM tblTauClassList AS L
  LEFT JOIN agent_effectiveness
    ON agent_effectiveness.SaRacf = L.SaRacf
  LEFT JOIN tblCrosswalkTauGoal AS G
    ON  G.Year   = TauYear
    AND G.Bucket = 'Wpe'
  WHERE TermDate IS NULL
  AND   Graduated = 'No'
)
SELECT tau.*,
-- NEW CASE STATEMENT HERE: 
CASE WHEN wd.workdays > 14 AND WpeScore >= WpeGoal THEN 'Pass' ELSE 'Fail' END 
from tau
-- INLINE FUNCTIONS HERE:
CROSS APPLY dbo.fn_WorkDaysAge_itvf(TauStart, GETDATE()) AS wd
CROSS APPLY dbo.fn_WorkDate15_itvf(TauStart)             AS w15;

Note that I can't test this right now but it should be correct (or close)

Why CTE (Common Table Expressions) in some cases slow down queries comparing to temporary tables in SQL Server

The answer is simple.

SQL Server doesn't materialise CTEs. It inlines them, as you can see from the execution plans.

Other DBMS may implement it differently, a well-known example is Postgres, which does materialise CTEs (it essentially creates temporary tables for CTEs behind the hood).

Whether explicit materialisation of intermediary results in explicit temporary tables is faster, depends on the query.

In complex queries the overhead of writing and reading intermediary data into temporary tables can be offset by more efficient simpler execution plans that optimiser is able to generate.

On the other hand, in Postgres CTE is an "optimisation fence" and engine can't push predicates across CTE boundary.

Sometimes one way is better, sometimes another. Once the query complexity grows beyond certain threshold an optimiser can't analyse all possible ways to process the data and it has to settle on something. For example, the order in which to join the tables. The number of permutations grows exponentially with the number of tables to choose from. Optimiser has limited time to generate a plan, so it may make a poor choice when all CTEs are inlined. When you manually break complex query into smaller simpler ones you need to understand what you are doing, but optimiser has a better chance to generate a good plan for each simple query.

SQL Server CTE referred in self joins slow

I believe that CTE results are retrieved every time. With a temp table the results are stored until it is dropped. This would seem to explain the performance gains you saw when you switched to a temp table.

Another benefit is that you can create indexes on a temporary table which you can't do to a cte. Not sure if there would be a benefit in your situation but it's good to know.

CTE - LEFT OUTER JOIN Performance Problem

Instead of a recusive CTE, generate ALL_PERIODS with a CROSS join between the Plan table and a "number table" either persisted, or as a non-recursive CTE.

WITH N As
(
   select top 100 row_number() over (order by (select null)) i
   from (values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10) ) v1(i),
        (values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10) ) v2(i)
),
plan_period AS 
(
    SELECT 
        PLAN_NR, START_DATE,
        N.i period_nr, DATEADD(day, 7*N.i, START_DATE) next_date
    FROM TABLE_1 CROSS JOIN N
),

Why is this CTE so much slower than using temp tables?

As you can see in the query plan, with CTEs, the engine reserves the right to apply them basically as a lookup, even when you want a join.

If it isn't sure enough it can run the whole thing independently, in advance, essentially generating a temp table... let's just run it once for each row.

This is perfect for the recursion queries they can do like magic.

But you're seeing - in the nested Nested Loops - where it can go terribly wrong.

You're already finding the answer on your own by trying the real temp table.

Why CTE is so slow comparing to Temp tables?

There are two reasons.

Probably the more important reason is that SQL Server does not materialize CTEs. So, for every reference, SQL Server recalculates the entire CTE. As far as I know, SQL Server also does not do common subquery optimizations on the execution DAG, so it always regenerates the CTES (although the execution plans might be different for each instance).

The second reason is that temporary tables have statistics, and these statistics can inform the query plan to create a better plan.

I suspect that you can simplify the logic. However, you would need to ask a new question with an explanation of what you want to do, along with sample data and desired results.

Cte Very Slow When Joined