CODEWITHSUNDEEP
sql-serverreporting-services
adventureworks
adventureworks

Reputation: 422

SSRS reports are extremely slow with more than one SELECT statements

I have been browsing the stackoverflow for couple of days and no doubt I observed many similarities with my questions. But still I would like to share what I am facing in terms of making my SSRS report work.

My scenario is: I have 3 SELECT statements written in Text editor to to prepare 1 dataset. That dataset I use to populate fields on a tablix. Please note that these select statements are not so complicated. Although each 3 of these query has left join and respective WHERE conditions. All 3 SELECT queries are being inserted in a temporary table.

In WHERE conditions, one of the condition has one subquery to compare MAX(date) value. And one another condition has IIF(Expression, True, False) statement to evaluate between date range.

Below is the synopsis of my actual query.

Create #TempTable(
col1 nvarchar(50),
col2 nvarchar(50),
col3 datetime,
col4 nvarchar(15)
)

declare @date date
set @date = '2020-09-12';

insert into #TempTable(col1, col2, col3, col4)
select
col1, col2, col3, col4
from table1 t1
left outer join tbl1 on t1.Id = tbl1.T1Id and col3 = (select MAX() from tbl1 where tbl1.status = 0)
left join tbl2 on t1.Id = tbl2.T1Id
where 
tbl2.createddate between 
IIF((t1.date is null) or (CONVERT(DATE, DATEADD(MONTH, DATEDIFF(MONTH, 0, @date)-1, 0))),
    CONVERT(DATE, DATEADD(MONTH, DATEDIFF(MONTH, 0, @date)-1, 0)),
    t1.date
) and CONVERT(DATE, DATEADD (DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @date), 0)))

Similarly there are 2 more select statements that I have as above to prepare the dataset.

Hence, my doubt is can this subquery and IIF() statement be a reason to slow this report extremely down? and if so, I'd appreciate if I could get some guidelines here.

More interesting this is it does not return large number of rows. not more than 10 to 20 rows.

Upvotes: 0

Views: 210

Answers (1)

Randy in Marin
Randy in Marin

Reputation: 1143

This is not an answer, but I can't comment yet. Run the query in SSMS and look at the execution plan. Even if you have indexes, functions around a column will prevent the use of the index. Even if zero records are returned, the table has to be scanned. Perhaps the logic in the IIF can be changed to uncover the columns. Even if you can't, you might be able to add something that's redundant that's uncovered. Even if the added stuff is not selective enough to get you just what you need, but it might avoid scanning all the table and evaluating the IIF logic on every record.

What are the requirements? There might be another way to get what you need. Reverse engineering the date logic hurts the brain. Also, I don't see the AND for the between.

A simple example of a covered index might be to add 1 day to the column and compare to getdate(). The proper way would be to compare the column to getdate() - 1.

Type matters too. A query that compared a char(1) column to N'' took more resources than than the dozen busy applications using the server. Changing it to a non-unicode '' value resulted in zero logical reads most of the time - the filtered index could be used. It was a rare example of two extremes - from the worst to the best.

Upvotes: 1

Related Questions