June 9, 2010 at 9:25 am
We have queries that have a lot of views and one view is calling at least 2 to 3 views. These views cannot be indexed. I am seeing some performance issues, but not sure if it because of nested views design.
Are views a good idea if they cannot be indexed.
Thanks,
Kjain
June 9, 2010 at 9:37 am
If you're joining a non-indexed view against another non-indexed view, you're probably going to notice performance degredation. You'd probably be better off creating just one view that joins all the tables that you want data from. If it has to be nested, nest the view's query instead of nesting a view inside of it.
Sometimes, unfortunately, you can't create general purpose views because of performance and/or security. It's best to just build your view to suit a specific need.
--J
June 9, 2010 at 11:23 am
You have to remember that views (except for indexed views) are just the underlying query. There is no special construct or nifty thing going on with them. Sometimes, the optimizer can figure out that while you're requesting data from a view, the data requested only needs to join a couple of tables instead of all the tables within the view and you'll see a different execution plan. But as things get more complicated and you begin joining views, you'll see the execution plans become more and more complex to the point where you see very little optimization at all. If you need to construct a different query against the data, use the base tables to build that query. Performing a join on views is about the worst choice you can make when working with views.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 10, 2010 at 10:39 am
Thank you all for your replies.
Kjain
June 10, 2010 at 11:04 am
I remember when I was studying for my MCTS, one of the practice questions that came up a lot was about 3 users who needed similar data from the same group of tables, and how to responsibly set up views. The answer pretty much always involved setting up three views. Anything else gave access to unneeded (and possibly sensitive) data to each of the users. Furthermore, with large data sets, unneeded data equals more time.
Personally, I come from a programming background and my tendancy is to program this to be as extensible as possible. Generally, this involves stacking several layers of abstraction on top of each other. Unfortunately, this doesn't work particularly well with SQL Server. SQL Server just can't deal with layers of abstraction the way a compiled language can. The optimized needs specific, concise information in order to pick the optimum path to the correct data. Often, this means you need to operate on a single layer. This means sometimes you'll have to commit the cardinal sin of programming - copying and pasting code. In a database context, its not necessarily that bad. I've seen two copies of the same code evolve in two totally different directions over time. T-SQL scripts need to be specific, not abstract.
--J
June 10, 2010 at 11:10 am
... If you really need reusable code, the programmers friend is the inline table-value function. These are almost always evaluated inline as though you had copy-pasted the code directly into the script. Still, returning too many unneccessary values in a TVF may cause the optimizer to make bad decisions. You still want to keep everything as lean as possible...
--J
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply