SQL Server Performance

Views

Discussion in 'T-SQL Performance Tuning for Developers' started by Raulie, Jan 15, 2004.

  1. Raulie New Member

    I got a view that joins 7 tables. Would spliting the view increase performance. For instance createing a view with only 3 tables and then joining the view with the other 4 tables, or does it even matter.
  2. joechang New Member

    if you look at the execution plan, you will see that sql server effectively inlines views (are you a c programmer?)
    basically, the view is for your convenience,
    sql server will convert it to the full join with all required tables for your query.
    if your view has 7 tables, but only 5 are required for a specific query, sql won't use the other 2
  3. Raulie New Member

    Thanks Joe, by the way I really found your articles on query execution plans interesting I spent all night reading them.
  4. Raulie New Member

    By the way why did you ask if I was a C programmer???
  5. joechang New Member

    one of the options in c programming is to inline a function at compile, instead of actually implementing a function call,
    so when i used the term "inline", it would have a clear meaning to a c person, but other people may not understand,
    anyways, sql will replace the view with the query from the view
  6. pavankan New Member

    So, am I right in saying a View is totally free.. in terms of performance

    in other words, it adds nothing to the performance load

    or using a view will not (atleast generally) degrade performance at all

    Sunny Kanaparthy is a software developer with the University of New Orleans, LA, USA.

  7. Luis Martin Moderator

  8. Chappy New Member

    Non indexed views are merely an abstraction tool and can help simply queries to be more manageable. Of course, views have other uses (including integrity enforcement) but lets assume we are talking about a view which will only be SELECTed from.

    > or using a view will not (atleast generally) degrade performance at all

    While this is true, what you must be aware of is that by nesting views, you may well end up with a query which performs worse than, if it had been written out in longhand and optimised.
    It is important to think how the query will look once the view expanded like joe says, whenever you use a view in a query (especially if you nest 2 or 3 of them), because the expanded query may turn out to be suboptimal even though the views are individually ok.


Share This Page