There was a big improvement to the performance of scalar valued functions in SQL Server 2019.

But you need to make sure the improvement is switched on to get the benefit,...

• This reply was modified 8 months, 3 weeks ago by  Jonathan AC Roberts.

If you need the count to be NULL when there aren't 5 preceding days then this query will do the trick:

declare @DaysToLookBack int = 5

select a.company, 
...

declare @DaysToLookBack int = 5

select *
  from [dbo].[TestTable1] a
 cross apply (select count(*) Count
            ...

There aren't 3 values less than 88 within 5 days of (including the reference date) 2024-02-28

2024-02-28 minus 4 days is 2024-02-24.

So there are only  2 rows less than that (11,...

• This reply was modified 8 months, 3 weeks ago by  Jonathan AC Roberts.

The gaps in dates shouldn't be an issue.

The data you provided isn't the data you put in your amendment of the query.

The results from the test data you provided look...

Has TRADE_DATE got a time part on it? If so is it not always midnight?

Can you supply some test data?

declare @DaysToLookBack int = 5

select *
  from [dbo].[TestTable1] a
 cross apply (select COUNT(*) Count
            ...

I would just leave it all in one table.

How many rows has this table got?

• This reply was modified 8 months, 4 weeks ago by  Jonathan AC Roberts.

Could you change the method of deleting rows so that you drop the clustered index on the view, then delete the thousands of rows, then reinstate the clustered index?

Also check...

You could see how the performance is if you drop the clustered index, after dropping the clustered index the view will just behave like a normal view.

Check what indexes are...

What index have you got on that view?

I'm not sure why the index recommendation has INCLUDE columns on it because it has to delete the row from the table.

Is there an FK to tblRecords_Unfiltered on tblAlert_Owners_Unfiltered? If...

How many rows are in tblAlert_Owners_Unfiltered?

It's probably going so slow as you have no indexes on the FK column on the child tables.

Set up data

drop table if exists #a
go
;WITH MyCTE([sessionnumber],[maxsession],[rownumber],[startdate],[startweekday],[enddate],[endweekday],[starttime],[endtime],[NomineeName])
AS
(
SELECT '1','8','1','7-Apr-24','Sunday','8-Apr-24','Monday','10:00 PM','1:00 AM','Name 1' UNION ALL
SELECT '1','8','2','7-Apr-24','Sunday','8-Apr-24','Monday','10:00 PM','1:00 AM','Name 2' UNION ALL
SELECT '1','8','3','7-Apr-24','Sunday','8-Apr-24','Monday','10:00 PM','1:00 AM','Name 3' UNION ALL
SELECT '1','8','4','7-Apr-24','Sunday','8-Apr-24','Monday','10:00...