On Indexes and Views

Question

On Indexes and Views

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

TimothyAWiseman SSCrazy Eights Points: 8819 More actions · Answer 1

It is unlikely that you would break something, though it is possible since it will try to enforce things like unique constraints onto the base tables. Also, schemabinding may prevent schanges to the table schemas which could cause a problem during an upgrade.

The better question is what do they gain you? If you are frequently accessing it through SSMS or if you can change the GUI code so that it can take advantage of the indexed views, then the performance improvements can be extremely impressive. But, if you are primarily accessing the system through a closed source GUI that you cannot direct to take advantage of the indexed view, then it will do you little if any good.

(As a side note, Enterprise edition can use the index on the view if it is useful when you query against the base tables in certain cases, but it does not make a lot of since to create an indexed view with that in mind. If you want to improve access to the base tables themselves, it makes more sense to create the appropriate indexes on them. That is more of an incidental benefit if you are also using the index on the view to access the view.)

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/

Wayne West SSC-Insane Points: 22586 More actions · Answer 2

Thanks for the reply, Tim. I'd have to research the schema, off-hand I cannot say that all the tables have PKs, clustered or otherwise. I have two specific performance problems, one I can't remember the details of without researching it. The other involves two tables where I need a subset, and because of the table size, a filtered indexed view would (I think) give that report good performance.

In this case, the data is being read by an Access report, not through the GUI, so that concern doesn't apply.

-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

SqlOnMyMind SSCertifiable Points: 5062 More actions · Answer 3

Question:

I see the Pros of creating Indexed Views, but what are the Cons? Seems I read somewhere that the materialized view has to exist somewhere. Is that in memory or on a physical disk?

When loading data, will this slow down the load? Can the view be disabled during a load and then reconstructed (I'm sure that's not the best term to use)?

I am in the middle of a debate on whether we go this approach or a different approach. Any input would be greatly appreciated.

Thanks!

TimothyAWiseman SSCrazy Eights Points: 8819 More actions · Answer 4

Carla;

There are definitely numerous design considerations.

First, the view has to be schema bound. This makes it harder to change any objects the view depends on (you have to remove schema binding first, make the change, and then recreate it). In a mature database where the schema rarely changes this is of limited concern of course, but in a newly created database this can become tedious.

Next, as you said, the index has to be stored on disk. Depending on the size of the table and number of columns involved, this can be quite substantial. Also, it will slow down write operations on the tables involved. In most cases, this will not be a substantial amount, but it can be a consideration for tables that receive a lot of updates.

Then, if you are not in enterprise edition, you have to deal with the complexity of the query hints to actually use the index (this is handled by the engine in Enterprise Edition.) Also, it will add complexity if you use replication and both servers are not in Enterprise Edition (the engine in Enterprise edition will handle almost all of it for you, but in Standard Edition you have to do a lot more of it manually).

In many cases, the advantages to using an indexed view will greatly outweigh the disadvantages, but these are all things that must be considered.

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/

SqlOnMyMind SSCertifiable Points: 5062 More actions · Answer 5

Thanks for the reply.

I did set up a couple for testing, and since we have the Standard edition, I set up some queries with the hints. They worked great, greatly reducing the query time. But you pointed out a couple disadvantages, including the maintenance issues, especially since our database is still evolving. The jury is still out on which way we will go.

Thanks again.