Nested View- Performance Issue

Question

Hi All,

i got a issue to solve issue of performance of a Main view calling in SP, main view calling nested view i.e

Main View- Calling View D
View D- Calling View C
View C- Calling View B
View B calling - View A
View A - Calling Base Table

main view get aggregated measures , now performance of Main View is too slow .

please suggest how to remove these view and get data soon as possible.

what is best practices in this case

Thanks

Answer 1

replace all View to SP will faster execution.

Thanks all for suggestion

Answer 2

Since we don't see the code, we cannot say that much.

A view itself is just a macro and does not call anything. SQL Server expands the view text, and works with the expanded query.

A common problem with nested views is that the same table may be included many times, when a single time had been enough.

I would suggest that you first rewrite the query with a number of CTEs:

; WITH A AS (
    SELECT .... FROM BaseTable
), B AS (
   SELECT ... FROM A
), etc

Once you have verified that this query has the same result as the original view, you can start to analyse it to see if there are things you can simplify.

The query with the CTEs will not run any faster than the view query. A CTE is basically just a view that is local to the query, and again, it's a macro that SQL Server expands into the query. The whole with the CTE is that it makes it easier to trim down the query in complexity.

Answer 3

Nested views are one of the most difficult programming objects to troubleshoot performance on SQL Server because they make query plans more complex and you can read in this articile about more reasons.

My suggestion is the following: start moving the query on view A as subquery into the View definition of view B. Test it. Make sure the query on view B has all the indexes needed. Once you merge view A & view B, merge view B with view C. In the end, you can keep view D only, once you merge C & D. But for me, consider to convert that view D (with all the others views merged into it) to just one stored procedure.

Procedures are better in many ways on most scenarios. You can use parameters to filter data with them. Each view can have only one query.

Answer 4

Hi @manish verma ,

You could solve the problem simply by "decomposing" the nested view and including its full SQL definition inside the calling query. Just doing this alone would take the query back to its quick performance.

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 5

Thanks a lot for your all answer, l will test approach then finally know which one best, looks like we have multiple option,

thanks