Views - When to use them? 1

[robertfah]

I've got an app that used several hundred stored procedures and am not familiar with views and how they work. A friend recommended that I use Views (Indexed) to help cut down on query time, especially for SP's that do nothing more than a simple select statement. So, I guess my question is; when is it best to use Views and should I consider implementing them?

Thanks!

 

[gmmastros]

Hmmm...... Using an indexed view to speed up selects, huh?

I would never write a view that does a simple select on a single table. It doesn't really make sense. Instead of writing a view that does a simple select on a single table, just add an index to to base table.

Think about it this way. The only time this would help is when your query isn't already using an index on the base table. But putting an index on the view, it will speed up THAT view. By putting an index on the base table, it will speed up that query and possibly others.

The path to using views is a slippery slope. Sure, views are easy to create, easy to use, hide implementation details of often used queries, etc...

The problem with views is when you OVER-USE them. I've heard of (but not seen) situations where views use other views which use other views.... 30 or more views deep. YIKES! This caused performance problems. In this situation, I was told that the developer re-wrote the query to use only base tables and performance improved by more than a factor of 10.

Please don't misunderstand me and think that I dislike views. I do use them on occasion. I merely want to caution you about nesting views. A couple layers of nesting is fine, but don't go too deep.

Just to give you an idea, in my database...

200 tables
200 audit tables
2,400 stored procedures
57 views


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[robertfah]

Thanks for your insight on this George. As I've stated, I know very little about views and wanted to get some good advice. I agree about the nested view thing; a little too out of control and yes it does make more sense to Index a table for a simple select vs. creating a View.

Thanks for the explaination!

 

[pjw001]

Unless I am much mistaken, Views and Indexed Views are somewhat different.

Views are unlikely to improve performance. Indexed Views were introduced to improve performce.

I agree with George's comments on Views, but Indexed Views are a whole new ball game.

Firstly, they are only available on the higher end editions of SQL Server.

An Indexed View actually creates data in the database.

Take the statement
SELECT Code, SUM(Value)
FROM SalesHistory
WHERE Code = 'OurFirstProduct'


This could take quite a long time, depending on how big the table is.

If you create a suitable Indexed View, it will be as if a table exists (in fact a table will exist) that holds the fields selected above.

This will mean that the select above will only have to retrieve a single row from the database, regardless of how many SalesHistory rows are present.

The downside to this is that every time the SalesHistory table is updated the Indexed View data is also updated.