question

manishverma-7371 avatar image
0 Votes"
manishverma-7371 asked manishverma-7371 answered

Nested View- Performance Issue

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

sql-server-transact-sqlazure-sql-database
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Could you have any updates?

0 Votes 0 ·
manishverma-7371 avatar image
0 Votes"
manishverma-7371 answered

replace all View to SP will faster execution.

Thanks all for suggestion

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AlbertoMorillo avatar image
0 Votes"
AlbertoMorillo answered AlbertoMorillo edited

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.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
1 Vote"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered

Hi @manishverma-7371,

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

manishverma-7371 avatar image
0 Votes"
manishverma-7371 answered EchoLiu-msft edited

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

thanks

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

If possible,it seems this would be better encapsulated as a stored procedure with parameters (or inline table-valued functions) than a bunch of nested views.

0 Votes 0 ·
TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered SaurabhSharma-msft commented

Just to add to what others have said.

Nested views are not "a problem". The performance issues with nested views are related to what the views actually do. In SQL Server, views are basically string macros (yes it is a little more complicated).

When you write code like:
SELECT ...
FROM view1
INNER JOIN view2...
LEFT OUTER JOIN view3
LEFT OUTER JOIN view4

The code which gets generated by the query optimizer is basically:

     SELECT ...
     FROM (SELECT .... ) as view1
     INNER JOIN (SELECT ...) as view2...
     LEFT OUTER JOIN (SELECT ...) as view3
     LEFT OUTER JOIN (SELECT ...) view4


This can create a very large query plan to parse and for the optimizer to try figure out how to make this work. If the views are simple this can be easy. If the views are complicated and/or use the same tables over and over, this becomes very complex and cause performance issues.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@manishverma-7371

Please do not forget to "Accept the answer" wherever the information provided helps you to help others in the community.

Thanks
Saurabh

0 Votes 0 ·