Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Joining on views in queries instead of tables Expand / Collapse
Author
Message
Posted Friday, May 2, 2014 9:56 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, August 25, 2014 10:45 AM
Points: 977, Visits: 325
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



Post #1567097
Posted Friday, May 2, 2014 10:03 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 4:06 AM
Points: 1,778, Visits: 4,658
Hard to say without further knowledge, normally I would consider this a good and normal practice.
Post #1567099
Posted Friday, May 2, 2014 10:03 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, August 29, 2014 2:32 PM
Points: 13,302, Visits: 12,168
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1567100
Posted Friday, May 2, 2014 10:16 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Saturday, August 30, 2014 4:51 PM
Points: 11,264, Visits: 13,023
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1567105
Posted Sunday, May 4, 2014 1:44 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 10:43 PM
Points: 39, Visits: 388
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.
Post #1567318
Posted Sunday, May 4, 2014 1:03 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:58 AM
Points: 42,832, Visits: 35,964
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1567355
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse