May 1, 2006 at 11:12 am
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
May 1, 2006 at 3:53 pm
Dave,
The application I inherited is very much the same - views referencing views which are built on more views - the typical view joins together 20 tables when you drill all the way through it......I can't change the nature of the app but I have been able to speed queries 8-10 times by eliminating most of the function calls - the calls were forcing the server into a row by row processing mode.........I found that I could convert most of them to a case statement in the select and dramatically improve performance.
Good luck,
Harley
May 2, 2006 at 2:22 am
hierachies of views usually hide poor database design or an attempt to produce a dss solution on top of an oltp database ( instead of a physical reporting database )
I've usually figured that anything over 16 tables in a join may be problematic ( I don't count a view as 1 table but the number of physical tables involved ). Beware of functions which may turn your queries into cursor based rather than set based queries, that said you can make considerable performance gains usuing table valued functions in place of views. You really need to examine the query plan and make sure you're not missing indexes.
You could also try derived tables, or even #temp tables to replace the views. I've found that quite often views are over complex ( for the actual query ) when used this way and simplicification can speed up processing.
I'd also try to discourage this type of development < grin >
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
May 2, 2006 at 8:12 am
I'm definitely trying to discourage it, but that will take time, especially since I've only been with this company for 9 months. At my previous employer the developers went to the other extreme and stayed away from views, which isn't always a good thing, but query performance was typically good. There problem was with the overuse of function calls, which is what I also see here. In regards to the query plan, I pointed it out to the developer, but I will leave it to him to cleanup since it contains 141 icons to review. If I had the time I would address it myself, but time is one thing I don't have at the moment.
Thanks, Dave
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply