SQL Server Performance Forum – Threads Archive
ViewsI 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.
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
Thanks Joe, by the way I really found your articles on query execution plans interesting I spent all night reading them.
By the way why did you ask if I was a C programmer???
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
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.
In articles tab, there is some about views.
One of them: http://www.sql-server-performance.com/indexed_views.asp Luis Martin
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.