Sort operator takes more query cost

Question

There is Schedule table every day there will be set of schedules inserted in to this table for many customers, Table structure will be somewhat like this

Create table factSchedule (factID bigint identity (1,1) primary key, custId char(10), scheduleDate date, amount money, LoadedOn date)

let’s say,

Per customer will have more than 100 schedule entries

Per day there will be 10,000 customers inserted with respective schedule entries

This table will be updated daily by inserting updated schedule entries, lets say for first loadedOn date there will be 100 records for an customer and on second loadedOn date there will be same 100 records but with different Amounts so eventually there will be ~200 records in two days for one customer so you can imagine how big this table would be...

Now I have a SP which will return some calculated bucket fields based on the very first scheduleDate for a given input date in factSchedule table. So first I needed a cte table which will contain only the first schedule date of each custId for given LoadedOn field as input parameter like below;

;with cteSchedule as
( SELECT result.*
     (select custId, scheduleDate, Amount,@inputDate as compareDate, row_number ()over ( partition by loadedon, custId order by loadedon, custId, scheduleDate)rownum From factSchedule Where LoadedOn = @inputDate and scheduleDate >= @ inputDate)
) as result
WHERE result.rownum = 1

Now I have another table called as Customertable this table will have one record for each custId so aprox 10,000 customers in this table.

so here my SP goes,

    ;with cteSchedule as
( SELECT result.*
     (select custId, scheduleDate, Amount,@inputDate as compareDate, row_number ()over ( partition by loadedon, custId order by loadedon, custId, scheduleDate)rownum From factSchedule Where LoadedOn = @inputDate and scheduleDate >= @ inputDate)
) as result
WHERE result.rownum = 1

Select Maintable.CustID,
(select case when scheduleDate < dateadd (day,7,compareday)
             then ‘Customertable.someAmount ’
       Else 0 end
  From  factSchedule and cteSchedule.custId = Maintable.custId) end as 
  Amt_0to7_days,

  (select case when ScheduleDate >= DATEADD (DAY,7,compareday) AND ScheduleDate <= DATEADD (MONTH,1,compareday)
             then ‘Customertable.someAmount’
       Else 0 end
  From  factSchedule and cteSchedule.custId = Maintable.custId) end as 
  Amt_7to30_days,

    (select case when ScheduleDate >= DATEADD (MONTH,1,compareday) AND ScheduleDate <= DATEADD (MONTH,3,compareday)
             then ‘Customertable.someAmount’
       Else 0 end
  From  factSchedule and cteSchedule.custId = Maintable.custId) end as 
  Amt_1to3_Months,

 -- "like wise it will have another five buckets."

 From Customertable 

And below is the execution plan for this SP, it shows Sort operator in my first cteSchedule table takes 10% of query cost for each bucket so there are totally 8 buckets and there will be 80% query cost, this leads so much time (more than 1 hour). Also what does the warning sign (Operator used tempDb to spill data during execution with sill level 1)in sort operator means?

Is there any other way to reduce this sort operator query cost or anyway to rewrite the logic? anyway help would be much appreciated. thanks in advnace

Actual code :

declare @RunDateLocal date = '20180709' 


;with cteSchedule AS
(SELECT  schedule.*
    FROM (select ContractIDKey,ScheduleDateKey, @RunDateLocal as compareDay,row_number ()over ( partition by loadedon,ContractIDKey order by loadedon,ContractIDKey,ScheduleDateKey)rownum
    from FactSchedules
    where  LoadedOn = DATEADD(D,1,@RunDateLocal) and ScheduleDateKey>= @RunDateLocal) as schedule
 WHERE schedule.rownum = 1
)

select FB.ContractIDKey,

(select case when ScheduleDateKey < DATEADD (DAY,7,compareday)
                            Then ISNULL(AIA.Closingbalance,0)
                        else 0 end
                        from cteschedule where rownum = 1 and cteSchedule.ContractIDKey = FB.ContractIDKey)  as Interest_0To7_Days,

(select case when ScheduleDateKey >= DATEADD (DAY,7,compareday) AND ScheduleDateKey <= DATEADD (MONTH,1,compareday) 
                            Then ISNULL(AIA.Closingbalance,0)
                        else 0 end
                        from cteschedule where rownum = 1 and cteSchedule.ContractIDKey = FB.ContractIDKey)  as Interest_7To30_Days,

(select case when ScheduleDateKey > DATEADD (MONTH,1,compareday) AND ScheduleDateKey <= DATEADD (MONTH,3,compareday) 
                            Then ISNULL(AIA.Closingbalance,0)
                        else 0 end
                        from cteschedule where rownum = 1 and cteSchedule.ContractIDKey = FB.ContractIDKey)  as Interest_1To3_Months,

(select case when ScheduleDateKey > DATEADD (MONTH,3,compareday) AND ScheduleDateKey <= DATEADD (MONTH,6,compareday)
                            Then ISNULL(AIA.Closingbalance,0)
                        else 0 end
                        from cteschedule where rownum = 1 and cteSchedule.ContractIDKey = FB.ContractIDKey)  as Interest_3To6_Months,

(select case when ScheduleDateKey > DATEADD (MONTH,6,compareday) AND ScheduleDateKey <= DATEADD (MONTH,12,compareday)
                            Then ISNULL(AIA.Closingbalance,0)
                        else 0 end
                        from cteschedule where rownum = 1 and cteSchedule.ContractIDKey = FB.ContractIDKey)  as Interest_6To12_Months,

(select case when ScheduleDateKey > DATEADD (YEAR,1,compareday) AND ScheduleDateKey <= DATEADD (YEAR,3,compareday)
                            Then ISNULL(AIA.Closingbalance,0)
                        else 0 end
                        from cteschedule where rownum = 1 and cteSchedule.ContractIDKey = FB.ContractIDKey)  as Interest_1To3_Years,

(select case when ScheduleDateKey > DATEADD (YEAR,3,compareday) AND ScheduleDateKey <= DATEADD (YEAR,5,compareday)
                            Then ISNULL(AIA.Closingbalance,0)
                        else 0 end
                        from cteschedule where rownum = 1 and cteSchedule.ContractIDKey = FB.ContractIDKey)  as Interest_3To5_Years,

(select case when ScheduleDateKey > DATEADD (YEAR,5,compareday) 
                            Then ISNULL(AIA.Closingbalance,0)
                        else 0 end
                        from cteschedule where rownum = 1 and cteSchedule.ContractIDKey = FB.ContractIDKey)  as Interest_5Over_years

from  FactBalances FB 
LEFT JOIN vAllDeposists_AIAAmount AIA ON AIA.ContractIDKey = FB.ContractIDKey

Answer 1

For the query in your cte, since LoadedOn will allways be the same, there is no need to partition or order by that column. Also, when partitioning by ContractIDKey, you don't need to order by that column.

There is no need to query the CTE or a temporary table 8 times. The query that you used for the original cte already did return only 1 record per ContractIDKey, so it is sufficient to (outer) join the cte once. I suggest to test a query like this (with cte or with temporary table):

DECLARE @RunDateLocal date = '20180709';

WITH cteSchedule AS
(SELECT  ContractIDKey, ScheduleDateKey
    FROM (select ContractIDKey, ScheduleDateKey, row_number() over (partition by ContractIDKey ORDER BY ScheduleDateKey) rownum
    from FactSchedules
    where  LoadedOn = DATEADD(D,1,@RunDateLocal) and ScheduleDateKey>= @RunDateLocal) as schedule
 WHERE schedule.rownum = 1
)

SELECT 
  FB.ContractIDKey,

  case
    when cte.ScheduleDateKey < DATEADD(DAY,7,@RunDateLocal)
    then ISNULL(AIA.Closingbalance, 0)
    else 0
  end as Interest_0To7_Days,

  case
    when cte.ScheduleDateKey >= DATEADD(DAY,7,@RunDateLocal) AND ScheduleDateKey <= DATEADD(MONTH,1,@RunDateLocal) 
    then ISNULL(AIA.Closingbalance,0)
    else 0
  end as Interest_7To30_Days,

  case
    when ScheduleDateKey > DATEADD(MONTH,1,@RunDateLocal) AND ScheduleDateKey <= DATEADD(MONTH,3,@RunDateLocal) 
    then ISNULL(AIA.Closingbalance,0)
    else 0
  end as Interest_1To3_Months,

  case
    when ScheduleDateKey > DATEADD(MONTH,3,@RunDateLocal) AND ScheduleDateKey <= DATEADD(MONTH,6,@RunDateLocal)
    then ISNULL(AIA.Closingbalance,0)
    else 0 end Interest_3To6_Months,

  case
    when ScheduleDateKey > DATEADD(MONTH,6,@RunDateLocal) AND ScheduleDateKey <= DATEADD(MONTH,12,@RunDateLocal)
    then ISNULL(AIA.Closingbalance,0)
    else 0
  end as Interest_6To12_Months,

  case
    when ScheduleDateKey > DATEADD(YEAR,1,@RunDateLocal) AND ScheduleDateKey <= DATEADD(YEAR,3,@RunDateLocal)
    then ISNULL(AIA.Closingbalance,0)
    else 0
  end as Interest_1To3_Years,

  case
    when ScheduleDateKey > DATEADD(YEAR,3,@RunDateLocal) AND ScheduleDateKey <= DATEADD(YEAR,5,@RunDateLocal)
    then ISNULL(AIA.Closingbalance,0)
    else 0
  end as Interest_3To5_Years,

  case
    when ScheduleDateKey > DATEADD(YEAR,5,@RunDateLocal) 
    then ISNULL(AIA.Closingbalance,0)
    else 0
  end as Interest_5Over_years

FROM  FactBalances FB 
  LEFT JOIN cteSchedule cte ON cte.ContractIDKey = FB.ContractIDKey
  LEFT JOIN vAllDeposists_AIAAmount AIA ON AIA.ContractIDKey = FB.ContractIDKey

Answer 2

Have you tried materializing your data set in a temp table versus using the cte? A Common Table Expression will run the sql every time it's used, whereas a temp table will only run it once and reuse the same result set. That approach should cut down on the number of sorts (to 1).

Spills are often related to bad stats. Check the estimated vs actual execution plans to confirm this. Ditching the cte could help there too, as could rebuilding the statistics. Hard to say for sure without some poking.