tune a table valued function that is using recursive CTE

Question

tune a table valued function that is using recursive CTE

sqlfriend

SSC Guru

Points: 52781
More actions
March 16, 2021 at 10:54 pm
Go to Answer
#3858702
We have a function that calculates student truancy records by counting their absence in a moving 30 days window. It uses a recursive CTE to count any 30 days passed.
Then we use other table to join this table valued function, and it was extremely slow as records increased.
I tuned it to select the TVF into temp table, then other table can join the temp table, it increased some performance.
But still this is the most cost query in my program, any other things I can do to tune the performance?
CREATE FUNCTION [dbo].[MaxTruantCount_Any30Day] ()
RETURNS TABLE
AS
RETURN
WITH FirstLastAbsentDates AS
(
SELECT a.StudentId
,MIN(a.Att_Date) AS FirstDay
,MAX(a.Att_Date) AS LastDay
FROM TruantDay a
JOIN SchoolYear b
ON a.SchoolYearId = b.Id
AND GETDATE() BETWEEN b.StartDate AND b.EndDate
WHERE a.IsAbsent = 1
AND a.IsCleared = 0
GROUP BY a.StudentId
HAVING MAX(a.Att_Date) > MIN(a.Att_Date)
)
, DateRangeEndDays (StudentId, EndDay) AS
(
SELECT a.StudentId
,a.LastDay
FROM FirstLastAbsentDates a
UNION ALL
SELECT a.StudentId
,DATEADD(d, -1, c.EndDay) AS EndDay
FROM FirstLastAbsentDates a
JOIN DateRangeEndDays c
ON c.StudentId = a.StudentId
WHERE DATEADD(d, -1, c.EndDay) > a.FirstDay
)
, AbsenceCounts AS
(
SELECT a.StudentId
, a.EndDay
, COUNT(*) AS AbsenceCount
FROM DateRangeEndDays a
JOIN TruantDay b
ON b.StudentId = a.StudentId
WHERE b.IsAbsent = 1
AND b.IsCleared = 0
AND b.Att_Date BETWEEN DATEADD(d, -30, a.EndDay)
AND a.EndDay
GROUP BY a.StudentId, a.EndDay
)
, MaxAbsenceCounts AS
(
SELECT a.StudentId
, MAX(a.AbsenceCount) AS MaxAbsenceCount
FROM AbsenceCounts a
GROUP BY a.StudentID
)
, MaxAbsenceDate AS
(
SELECT a.StudentId
, MIN(b.EndDay) AS MaxAbsenceDate
, a.MaxAbsenceCount
FROM MaxAbsenceCounts a
JOIN AbsenceCounts b
ON a.StudentId = b.StudentId
AND a.MaxAbsenceCount = b.AbsenceCount
GROUP BY a.StudentId, a.MaxAbsenceCount
)
SELECT a.StudentId AS StudentId
,a.MaxAbsenceCount AS MaxAbsenceCount
,a.MaxAbsenceDate AS MaxAbsenceDate
FROM MaxAbsenceDate a
--order by a.StudentId
;
- This topic was modified 3 years, 8 months ago by sqlfriend.

Steve Collins

SSCrazy

Points: 2157
More actions
March 17, 2021 at 6:31 pm
Answer
#3859101
sqlfriend wrote:
Wow, that is so much faster. Thank you Steve. Now let me take a look to understand the code, is it the fnTally make it so much faster?
It's true fnTally is blazingly fast compared to other methods of generating series. These 2 lines of code serve the purpose of a calendar table:
```
dbo.fnTally(0, datediff(day, fl.FirstDay, fl.LastDay)) fn
cross apply (values (dateadd(day, fn.n, fl.FirstDay))) v(dt)
```
An actual calendar table would be just as fast or perhaps marginally faster. The original query counts 30 day totals by generating the series over and over. That was the slow part. Switching to a moving window is more efficient
```
sum(case when IsAbsent is null then 0 else 1 end) 
    over (order by dt rows between 30 preceding and current row) cum_cnt
```
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 1

That function can be rewritten to perform a lot better - but to do that we need sample data and tables to test, with expected results based on the sample data.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

sqlfriend SSC Guru Points: 52781 More actions · Answer 2

I attache the schema of sample data scrambled for testing

sqlfriend SSC Guru Points: 52781 More actions · Answer 3

try to attach file again

Attachments:

You must be logged in to view attached files.

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 4

I would replace the recursive CTE 'DateRangeEndDays' with a look up against a calendar table

DateRangeEndDays (StudentId, EndDay) AS
SELECT a.StudentId,c.CalendarDate
FROM FirstLastAbsentDates a
INNER JOIN MyCalendar c ON c.CalendarDate BETWEEN a.FirstDay AND a.LastDay
)

See here for details about a calendar table

https://www.sqlservercentral.com/steps/bones-of-sql-the-calendar-table

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Steve Collins SSCrazy Points: 2157 More actions · Answer 5

It seems the query is performing a partial cross join. The cross join could be replaced by a tally function and window (rows between 30 preceding and current row) function. As far as I can tell this query produces the same output

with
FirstLastAbsentDates(StudentId, EndDate, FirstDay, LastDay) AS (
    select a.StudentId, b.EndDate
           ,min(a.Att_Date)
           ,max(a.Att_Date)
    from dbo.testTruantDay a
         join dbo.testSchoolYear b ON a.SchoolYearId = b.Id
                                      and getdate() between b.StartDate AND b.EndDate
    where a.IsAbsent = 1
          and a.IsCleared = 0
    group by a.StudentId, b.EndDate
    having max(a.Att_Date) > min(a.Att_Date))
select fl.StudentId, oa.cum_cnt as MaxAbsenceCount, 
       isnull(oa.Att_Date, dateadd(day, 1, fl.FirstDay)) as MaxAbsenceDate
from FirstLastAbsentDates fl
     outer apply (select top(1) td.Att_Date,
                         sum(case when IsAbsent is null then 0 else 1 end) 
                             over (order by dt rows between 30 preceding and current row) cum_cnt
                  from dbo.fnTally(0, datediff(day, fl.FirstDay, fl.LastDay)) fn
                       cross apply (values (dateadd(day, fn.n, fl.FirstDay))) v(dt)
                       left join dbo.testTruantDay td on fl.StudentId=td.StudentId
                                                         and v.dt=td.Att_Date
                                                         and td.IsAbsent=1
                                                         and td.IsCleared=0
                  order by cum_cnt desc) oa
order by fl.StudentId;

StudentIdMaxAbsenceCountMaxAbsenceDate
700049912021-01-27 00:00:00.000
700052472021-01-29 00:00:00.000
700054022020-12-18 00:00:00.000
7000559212020-12-16 00:00:00.000
7000907222020-11-06 00:00:00.000
7002389112020-11-06 00:00:00.000
700384592020-12-18 00:00:00.000
700444332020-11-16 00:00:00.000
... 
(246 rows returned)

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 6

sqlfriend wrote:

I attache the schema of sample data scrambled for testing

What is the expected output from this data?

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

sqlfriend SSC Guru Points: 52781 More actions · Answer 7

Thanks, what is the benefit to use a calendar table, all we need is to know the first day and end day, If we create a calendar table, that has each day in the table, every year we need then to recreate the calendar table.