May 2, 2014 at 9:56 am
Iām working on a legacy DB and I notice that several of the procedures do joins on views. Some of these views were created on very wide tables to reduce the number of columns returned and reference just the one table. Other views do joins against multiple tables. The query plans of the procedures indicate that, most of the time but not always, the views are using the indexes of the underlying tables. Can someone comment on the performance implications of using views as opposed to the tables themselves.
TIA
John Deupree
May 2, 2014 at 10:03 am
Hard to say without further knowledge, normally I would consider this a good and normal practice.
š
May 2, 2014 at 10:03 am
John Deupree (5/2/2014)
Iām working on a legacy DB and I notice that several of the procedures do joins on views. Some of these views were created on very wide tables to reduce the number of columns returned and reference just the one table. Other views do joins against multiple tables. The query plans of the procedures indicate that, most of the time but not always, the views are using the indexes of the underlying tables. Can someone comment on the performance implications of using views as opposed to the tables themselves.TIA
A view will use the same indexes. There may be something in the view or the queries joining to them that prevents some indexes from being used. From just a performance perspective there is no difference between using a view instead of a table.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 ā Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 2, 2014 at 10:16 am
The biggest issue I've seen with using views is that sometimes a view may be accessing tables not needed for the results for that specific query or doing other operations that may not be needed for that specific query.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 4, 2014 at 1:44 am
I have come across examples where views make it easier to introduce performance problems. Especially when columns are an outcome of a function or case statement etc. A view might be a good instrument to collect combined data that is often accessed but it hides the physical structure of the tables.
Recently I tuned a query that got data from view with a filter on a column that was actually an outcome of a calculation. Rewriting using only the tables got it speedy.
So I would say views are a good instrument in the hands of experts and require proper disclaimers for usage.
May 4, 2014 at 1:03 pm
John Deupree (5/2/2014)
Can someone comment on the performance implications of using views as opposed to the tables themselves.
Depends on how complex the views are. Simple views that just narrow and/or filter a table, fine. Couple of joins, probably fine. Lots and lots of joins, aggregation, views upon views, you're asking for performance problems. Partially from the complexity making it harder for the optimiser to find a good plan, partially from the time required to inline all the views and simplify and partially because the optimiser may decide to materialise an interim resultset due to the aggregations, sorting, etc.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply