locked
View on Views peformance problems RRS feed

  • Question

  • Hi,

    I have a generic question on Views on Views.  Do they create performance problems and is it wise not to use them? Uising SQL Server 2008. I know that in practice if there is a problem is on a case by case basis  and it doesn't mean that the view on a view creates the issue. Resolving the view to the actual code it may not increase performance anyway and it could be an issue with a missing index or similar. But do complicated views usually run slower than the original sql code (if I was to replace it?). In fact I can see some advantages like create an index on a view  for increased performance.

    Does anyone have any advice whether views on views shouldn't be used widely within a complicated database?  Thank you.

    Panos


    • Edited by panlondon Friday, September 18, 2015 10:19 AM
    Friday, September 18, 2015 10:17 AM

Answers

  • You can think of a non-schema bound view as a "macro", exactly as if you had typed it yourself. It is a little more complicated, but that is the simplest description.

    Views do not inherently cause a performance issue.  However, what you do in the view, cast, substring, etc can.  Also if you have views joining to views joining to views which all join the same tables, it can get complicated for the query analyzer to create a "good" plan and can cause issues.  As Dan said, it can lead to extensive compile times. 

    I use views widely and extensively.  I have even run into the 255 table join issue using views.  There are cases where a view is not a good option and requires instancing the data.


    Friday, September 18, 2015 12:31 PM
    Answerer

All replies

  • Does anyone have any advice whether views on views shouldn't be used widely within a complicated database?  Thank you.


    In a perfect world, there shouldn't be a problem with nesting views from a performance perspective.  SQL Server should be able to generate an optimal plan regardless of view complexity and level of view nesting.  Unfortunately, this isn't always the case in practice.  You'll need to test with your actual schema to determine if performance is optimal or not for your specific situation.  Indexed views can provide a performance in some cases as long as the query complies with the many restrictions for indexing views and code that modifies the tables has the proper SET options.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Friday, September 18, 2015 12:18 PM
    Answerer
  • You can think of a non-schema bound view as a "macro", exactly as if you had typed it yourself. It is a little more complicated, but that is the simplest description.

    Views do not inherently cause a performance issue.  However, what you do in the view, cast, substring, etc can.  Also if you have views joining to views joining to views which all join the same tables, it can get complicated for the query analyzer to create a "good" plan and can cause issues.  As Dan said, it can lead to extensive compile times. 

    I use views widely and extensively.  I have even run into the 255 table join issue using views.  There are cases where a view is not a good option and requires instancing the data.


    Friday, September 18, 2015 12:31 PM
    Answerer
  • Hello,

    The following contributions explain performance issues when using nested views on complex queries:

    http://www.sqlservercentral.com/blogs/2cents/2010/04/05/nested-views-causing-query-performance-woes/


    http://sqlmag.com/sql-server/what-are-your-nested-views-doing



    Hope this helps.



    Regards,

    Alberto Morillo
    SQLCoffee.com

    Friday, September 18, 2015 12:40 PM
    Answerer