Significant delay in generation of View (Viewed Table) in SQL Server

I have a few Personnel Details tables in a database in SQL Server 2014. There is a view that pick up selected data from these tables by implementing a few joins.

One condition to select the row is if its columnn isActive = 1 in NominalRoll table. There is a report in SQL Server Reporting Server that calls this view and displays the PDF.

Until now the NominalRoll table had 1 million rows out of which 75% had isActive = 1. The report generation was taking up to 1 minute for display.

Recently due to some requirements, the isActive column has been updated and now only 5% of the rows have isActive = 1. Strangely enough, since then the report is taking up to 4 minutes for display even when the rows to be display have greatly reduced.

On viewing the ExecutionLog2 entries, it is found out that the time for fetching data from the view has significantly increased. Please help, it is a live system.

Answer

The view is not materialized by default. That means that when you query the view, the engine is executing its definition.

You can try few things here:

1. Materialized the view creating an index on it – the view data will be automatically updated when underlying data is changed and it acts as a table (so, it will looks like you are performing SELECT from a normal SQL table). Note, that a lot of stuffs are not supported in indexed views.
2. You are selecting only 5% of the rows filtering by isActive=1 – that’s sound like good candidate for creating filter index. It will help the engine to make better estimations about the data it is going to work with.

The above changes need a little bit more work and require DDL operations. If you are not allowed to change the schema you can share the view definition in order to get some help for optimizing the T-SQL. Actually, it is always best to start with optimizing the T-SQL, before going to DDL changes.