Wouldn't it be easier to schedule an agent job to rebuild/populate a table in the database(s) - and have a view of that table for the application to access?

If your...

Yes , backups are compressed. Before deleting data it was taking around 5 hours ( this is for the all the DBs in the instance), now it is taking...

Just curious - but what if you switched the join to the date lookup table from an inner join to a cross apply?

cross apply (
Select *
From dbo.TSys_Lookup_Date...

In SSRS:

=Year(Today.AddMonths(-1))

=Today.AddMonths(-1).Year

• This reply was modified 5 years, 7 months ago by Jeffrey Williams.

That is a lot of code - personally I would refactor this to a single query using either CTE's or CROSS/OUTER APPLY where I could.  As to your problem...it is...

Is this a stand-alone system where you have both the database engine and reporting services installed?  Is this server hosted in Azure by chance?

Since this is pre-production is it in...

Have you verified the anti-virus software has been setup to exclude SQL data files, log files, the SSRS folders and web services?

What is the auto growth setting for the transaction log?  What is the recovery model for the database - I assume it...

Do you have any concerns about the space being used for that database?  If not - then I see no reason to perform the shrink.  Is the vendor recommending this...

You can use the built-in STRING_SPLIT function:

 --==== Sample data
Declare @testTable Table (UserName varchar(20), UserLanguage varchar(255))
 Insert Into @testTable (UserName, UserLanguage)
 Values ('Person 1', 'English,Spanish,French')
  ...

Not that it matters for this but just to be clear this is not something I made and if we figure out whats wrong with it I can't make...

FYI...

Per recommendation I created 3 temp tables  by breaking up the 3 queries and adding an INSERT command to create temp tables. I then compared the 3 temp tables...

You are also going to need to change every stored procedure and function that declares a variable or parameter as datetime.  With that said - I don't think you should...

Is this what you are looking for?  https://mssqltrek.com/2020/02/12/always-on-availability-groups-rolling-upgrades/

And...the added features that are available in Enterprise Edition would also be well worth the extra cost.  Data-driven subscriptions would be a large advantage to reporting solutions.