TSQL WITH clause slow query

Transact-SQL
1

HI All,

I have a slow running WITH clause query that hope some query HINTS or something else? may improve.
The Code below puts £Income into £Buckets of Capital & interest.

As the subsets of Bucket & Income data grows the query exponentially increases in runtime.

By reviewing the query below - can someone think of a method to speed the query?

Many thanks

;with bucket as (
SELECT  vdrse.RowID
	,vdrse.DealID as bucketDealID
	,vdrse.repayment_date
	,vdrse.RepaymentAmount as bucketAmount
	,vdrse.payment_seqID as BucketSeqID
	,vdrse.sum_of_payments as BukRunSum
	,vdrse.interest_on_repayment
	,vdrse.int_perc_split
	,vdrse.principle_on_repayment
	,vdrse.princ_perc_split
	,vdrse.RowInsertDateTime
	,vdrse.tDealsRepaymentsScheduleEditTS
FROM vwDealsRepaymentsScheduleEdit AS vdrse
INNER JOIN vwDealsRepayment AS vdr ON vdrse.DealID = vdr.DealID
WHERE (vdr.AccMgdFlag = 1)
),incoming as (
SELECT  [RowID]
      ,[DealID] as incomeDealID
      ,[repayment_date]
      ,[RepaymentAmount] as IncomingAmount
      ,[payment_seqID] as IncomingSeqID
      ,[sum_of_payments] as ActPayRunSum
      ,[tDealsRepaymentsActualTS]
  FROM [FedCapDB].[dbo].[vwDealsRepaymentsActual] 
),result as (
    select i.[RowID],i.[repayment_date],bucketDealID,IncomingSeqID,BucketSeqID,int_perc_split,princ_perc_split
        ,case when bucketAmount<IncomingAmount then 0 else bucketAmount-IncomingAmount end bucketAmount
        ,case when bucketAmount>IncomingAmount then 0 else IncomingAmount-bucketAmount end IncomingAmount
        ,case when bucketAmount>IncomingAmount then IncomingAmount else bucketAmount end InBucket
    from bucket b 
        inner join incoming i on i.incomeDealID=b.bucketDealID and i.IncomingSeqID=1
    where b.BucketSeqID=1
    union all
    select [RowID],[repayment_date],bucketDealID,IncomingSeqID,BucketSeqID,int_perc_split,princ_perc_split
        ,case when bucketAmount<IncomingAmount then 0 else bucketAmount-IncomingAmount end bucketAmount
        ,case when bucketAmount>IncomingAmount then 0 else IncomingAmount-bucketAmount end IncomingAmount
        ,case when bucketAmount>IncomingAmount then IncomingAmount else bucketAmount end InBucket
        from (
            select 
                i.[RowID],i.[repayment_date],b.bucketDealID,i.IncomingSeqID,b.BucketSeqID,b.int_perc_split,b.princ_perc_split
                ,case when r.BucketSeqID=b.BucketSeqID then r.bucketAmount else b.bucketAmount end bucketAmount
                ,case when r.IncomingSeqID=i.IncomingSeqID then r.IncomingAmount else i.IncomingAmount end IncomingAmount
            from result r
            inner join bucket b on b.bucketDealID=r.bucketDealID and b.BucketSeqID=r.BucketSeqID+(case when r.bucketAmount=0 then 1 else 0 end)
            inner join incoming i on i.incomeDealID=r.bucketDealID and i.IncomingSeqID=r.IncomingSeqID+(case when r.IncomingAmount=0 then 1 else 0 end)
            ) Prev
)

SELECT 
      bucketDealID as DealID
	,IncomingSeqID as DealRepayID
	,BucketSeqID as DealRepayBucketID
	,[RowID] as [DealsRepaymentsActual_RowID]
	,[repayment_date] as [paid_date]
	,InBucket AS AmountBucket
	,int_perc_split
	,princ_perc_split
	--,int_perc_split * InBucket as int_amt_bck
	--,princ_perc_split * InBucket as int_amt_bck
	,ROUND((int_perc_split * InBucket),2) AS int_amount_bucket
	,ROUND((princ_perc_split * InBucket),2) AS princ_amount_bucket
	,ROUND((int_perc_split * InBucket),2) + ROUND((princ_perc_split * InBucket),2) as check_sum --should = AmountBucket
FROM result r
ORDER BY bucketDealID
	,IncomingSeqID
	,BucketSeqID
2

you are defining a cte then joining it to itself?
do you have proper indices on the join columns?

is selecting from 1 view with no filters, is that what you want to do?

3

thanks yosiasz

Yes - is this slowing it?
No Indices on all the join columns. I think these joins need some help :laughing: - can you define Indicies on CTE?

The views are filtered to only contain rows required :slight_smile:

4

I'm thinking about your view and using noexpand. Maybe you can benefit from it.

What is EXPAND VIEWS and NOEXPAND ? - SQL Server Q&A from the SQL Server Central community

If you have a function in your view then this is a good example:
Using WITH (NOEXPAND) to Get Parallelism with Scalar UDFs in Indexed Views - Brent Ozar Unlimited®

Offical docs:
Table Hints (Transact-SQL) - SQL Server | Microsoft Docs

5

thanks, my issue has been fixed.