November 15, 2010 at 9:34 pm
Comments posted to this topic are about the item Indexed Views
November 16, 2010 at 3:03 am
A good introductory article, but I think people need to be careful when looking into using Indexed Views.
I recently added a view to a 3rd party application and thought it would be sensible to make it an Indexed View for performance reasons, so I created the view with the SCHEMABINDING option. However, the 3rd party application that updated the underlying base table stopped working and we had to remove SCHEMABINDING and the index from the view. I was told that SCHEMABINDING had made the base table read only, but I don't think this is the correct explanation. BOL didn't seem to suggest this could happen either.
November 16, 2010 at 5:19 am
jts_2003 (11/16/2010)
A good introductory article, but I think people need to be careful when looking into using Indexed Views.I recently added a view to a 3rd party application and thought it would be sensible to make it an Indexed View for performance reasons, so I created the view with the SCHEMABINDING option. However, the 3rd party application that updated the underlying base table stopped working and we had to remove SCHEMABINDING and the index from the view. I was told that SCHEMABINDING had made the base table read only, but I don't think this is the correct explanation. BOL didn't seem to suggest this could happen either.
SCHEMABINDING doesn't make the table data read-only, but it does prevent modification of the table structure if that would affect the view. So if the application is for whatever reason modifying the structure of the base table during normal processing, SCHEMABINDING can be a problem.
November 16, 2010 at 5:38 am
One of the applications that i was recently looking at had 32 indexes on the indexed view(1 clustered, 31 non clustered) :hehe:.
Last time i pulled report, it had 32000+ inserts where as only some of the indexes were actually getting used for reading. It was real bad setup. Though i agree, indexed views will increase read performance, however we must also consider the amount of writes happening to the base table.
November 16, 2010 at 7:31 am
If you have a table (let's say an Employee table) that has an index on one or more columns (let's say the Employee ID), is there any reason why you'd have an index tied to the same field(s) on a view that, for the most part, mirrors that base Employee table? If my applications are using views, should the index be only on the view and not the table? Unless you're building a special view, I've found it to be more beneficial to put indexes on the base tables in this context.
November 16, 2010 at 7:43 am
I recently added a view to a 3rd party application and thought it would be sensible to make it an Indexed View for performance reasons, so I created the view with the SCHEMABINDING option. However, the 3rd party application that updated the underlying base table stopped working and we had to remove SCHEMABINDING and the index from the view. I was told that SCHEMABINDING had made the base table read only, but I don't think this is the correct explanation. BOL didn't seem to suggest this could happen either.
I had a similar situation with a third party application -- when I looked at it closely, it turned out to be related to SET QUOTED IDENTIFIERS. This is not something you can fix at runtime, it has to do with the way their sprocs were saved/parsed.
You might want to check this out.
In my case, the vendor realized that their scripting process for writing upgrade code had left out the appropriate statement and they will eventually fix it. But first they had to understand/notice it <shrug>.
They also told me that they frown on schema-bound views because "what if they want to change their schema". There are two interesting things about this, for the purposes of our discussion:
*1 -- They also frown on our creating additional indexes on the base tables, and they don't create any. They also try to discourage any interop/integration efforts, for the same reasons. So this, globally considered, is a ridiculous attitude, IMHO. Beyond the obvious optimization reasons to do this, our dealing with the schema as it currently exists, in some manner -- whether for a report or other custom output, or interop with another enterprise app -- is not something that can be avoided because a vendor tells you it's going to cause an issue. No matter how many layers of abstraction you put on top of the base tables, if they change schema it's on your head to re-build your custom indexes, or whatever it takes. You can't just "not do it".
*2 -- I am really wondering if this is what your vendor meant by telling you the "base table was readonly". IOW, maybe somebody mis-repeated something to you, what they meant was "if you create a schema-bound view, it hampers our code because the structure of the table is readonly (not the data). For example, it's possible that they dynamically create and drop flag fields, or that the person who made the original statement was thinking long term, "the schema will be readonly when we upgrade".
Anyway, this may not be involved, but I thought I'd share.
>L<
November 16, 2010 at 9:09 am
Indexed views provide the most value when you are indexing on columns from multiple tables that are joined in the view--particularly if all of the columns you need to access can be stored in the index.
If all of the index segment columns are in a single base table, or even if the columns are clustered indexes in the base tables, you are generally better off putting the index on the base table.
Even in cases where there are multiple joined tables involved, indexed views do not always increase performance enough to warrant the write cost, inconvenience, and storage overhead.
My rule of thumb is to avoid indexed views unless there is a compelling benefit for a particular scenario...and sometimes there is a compelling benefit.
November 16, 2010 at 12:32 pm
Eh? No mention of the huge pitfall of indexed views?
If there is some sort of math in the view that could generate an ARITHABORT error, an indexed view has the capability to break inserts to the underlying tables. We tried this in our environment and found out about this the hard way. Microsoft points out the issue here - http://support.microsoft.com/kb/305333
I'd never make a view an indexed view if any sort of math is involved in the view unless the math has been carefully, carefully protected from arithabort errors.
In short, test it in a test db before you index your production views 🙂
Edit - spelling error
November 16, 2010 at 3:21 pm
Another easy mistake to make is to put a clustered index on the view that conflicts with the clustered index in the base tables. I now hold the world record for the shortest time to create a deadlock.
At least I got the chance to see some genuine output from the deadlock trace in SQL Profiles:-P
November 16, 2010 at 3:58 pm
indexed views have lots of drawbacks (i.e no left joins, subqueries etc etc) but in a highly normalised setup they are priceless when used correctly, and can solve some real performance issues. i couldnt live without them.
November 16, 2010 at 4:03 pm
i am however a little dissappointed at the article. i mean its a bit short and uninformative. ive only been on this site a few months now, but i think if you have an audience of sql dev's/dba's reading, you want to be a bit more in depth. personally i only want to see advanced stuff, and i can understand posting beginner articles for the less experienced, but even still, be a bit more informative in your articles.
thats just me...
November 16, 2010 at 9:05 pm
Good introductory article about Indexed view.
We need to be very careful while creating indexed view due to schema dependency. Once you have a indexed view, you can not modify the table structure which is referenced in the indexed view. In our product, we are giving patches whenever required or in new release, at that time, if we want to modify table schema due to whatever reason, we have to drop the indexed view first to modify the table structure.
Thanks
November 17, 2010 at 3:31 am
A disappointing article on an underrated feature.
Yes, indexed views can result in a huge savings on query execution times.
But indexed views also have their price. Someone who does not know that, might take this article as a pointer to create indexed views for every query ever used - and boy, would such a system crawl to slowdown when modifying data...
Each indexed view increases the cost of every data modification!
EDIT: I just noticed that there IS a short mention of this in the conclusion. But not as explicit as it should be. (The fact alone that I overlooked it at first is a tell-tale!)
November 17, 2010 at 9:20 am
Indexed views and triggers: two things to avoid if at all possible. Next up: Cursors are good! 😀
-------------------------------------------------------------------------------------------------
My SQL Server Blog
November 17, 2010 at 11:46 am
Here's a weird situation where having a clustered indexed view was a life saver --
At my last job, we had a table that stored notes for claims. This table was poorly designed, in that diary activities (i.e. things needing to be done on the claim) were mixed in with the notes. There were 40 million rows, of which only about 100,000 at any time were diary activities. I had no ability to change this as virtually everything the company did used that table and would have to be changed.
That table was clustered on claimid, which made sense for core application functionality, since the app brought up a claim and always looks at notes as an attribute of claims. No performance problems. However, over time, the business wanted a dashboard for adjusters that would show all of their diary actions in one view. This view would require looking at between several dozen to several hundred (maybe even a few thousand) rows in the notes table. However, those rows would be scattered throughout the table, and even though the index would tell you what rows you needed, the dashboard then required a crippling bookmark lookup to get the notes data needed. A covering index would in essence be a second copy of the whole table.
Solution: a clustered indexed view, clustered on adjusterid, where the query ONLY returned the diary activities. Yes, it stored the rows in full a second time on disk, but it was only 100,000 as opposed to 40 million rows. Performance wasn't ever a real problem because of how the business used them (typically only one user at a time in their notes) so no blocks or deadlocks, and update and delete time was acceptable. It was the only solution we could find to handle a situation where basically two clustered indexes were needed.
And no, starting from scratch, we'd have NEVER done that, at least not on my watch.
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply