Joining Views – How Many is too Many??? | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Joining Views – How Many is too Many???

Over the years I’ve read and experienced where joining more then 5 tables can lead to performance problems. This number can vary based upon the amount of data in each table, if and how indexes are used and the complexity of the query, but 5 has always been a good rule of thumb. Unfortunately I do not know what rule to apply in regards to joing views. A developer has experienced timeout problems periodically when opening a view in EM or when running the code which makes-up the view. I decided to look at the view and noticed it references tables and views, which reference more views, which in turn reference other views. In all the initial view references 5 tables and 8 views directly and indirectly, with some of the views containing function calls. What are your thoughts on how many views and tables are too many when it comes to joins and query performance. Thanks, Dave
I don’t think I have any "rules of thumb" in terms of numbers. My recommendation has always been that if it requires more than I can logically keep track of on a normal day, then it is too complex to be hitting an OLTP server and probably should be done using some type of data warehouse "like" tables where things are already brought together. Of course sometimes they listen and sometimes they "timeout" as in this case. (I just finished converting a bunch of views/queries for a customer and some of the views did things that were very redundanct and didn’t need to be done. So in order to get the "master" query to work in a timely manner I had to change it to use different underlying views. Hope it helps,
Dalton Blessings aren’t so much a matter of recieving them as they are a matter of recognizing what you have received.
I find hiting views as more intensive than the base tables. Indexed views are a Godsend! just my 2 cents. Mike Michael Berry
DBA
Hi ya, I’ve not had any specific problems with views as such, but the more joins the harder it is to optimise. function calls in views are likely to be an area of concern though… I’ve found functions to be really useful from a cool feature point of view, but a tendency to be a real pain in terms of performance I’ve had queries on decent size tables (100k or more) and around 10-15 base tables eventually hit via views, and have been able to get performance to a good level. index views are great if there is summarisation to be done, but I’d leave them alone if you have a similar number of resulting rows than the original base tables, as in the latter case, appropriate base table indexing is almost always possible Cheers
Twan
]]>