<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by Sarvesh Singh
»
Indexed Views
29 posts, Page 1 of 3
1
2
3
»
»»
Indexed Views
Rate Topic
Display Mode
Topic Options
Author
Message
ss-457805
ss-457805
Posted Monday, November 15, 2010 9:34 PM
Old Hand
Group: General Forum Members
Last Login: Thursday, May 16, 2013 8:14 AM
Points: 356,
Visits: 1,658
Comments posted to this topic are about the item
Indexed Views
blog: http://sarveshsingh.com
Twitter: @sarveshsing
Post #1021198
jts_2003
jts_2003
Posted Tuesday, November 16, 2010 3:03 AM
Ten Centuries
Group: General Forum Members
Last Login: Today @ 1:46 PM
Points: 1,060,
Visits: 4,167
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.
Post #1021324
sknox
sknox
Posted Tuesday, November 16, 2010 5:19 AM
Ten Centuries
Group: General Forum Members
Last Login: Today @ 9:31 AM
Points: 1,039,
Visits: 1,356
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.
http://msdn.microsoft.com/en-us/library/ms187956.aspx
Post #1021390
ps.
ps.
Posted Tuesday, November 16, 2010 5:38 AM
SSCrazy
Group: General Forum Members
Last Login: Thursday, May 02, 2013 2:32 AM
Points: 2,236,
Visits: 3,620
One of the applications that i was recently looking at had 32 indexes on the indexed view(1 clustered, 31 non clustered)
.
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.
Pradeep Singh
Post #1021405
sixthzenz
sixthzenz
Posted Tuesday, November 16, 2010 7:31 AM
SSC Journeyman
Group: General Forum Members
Last Login: Thursday, April 11, 2013 2:49 PM
Points: 92,
Visits: 341
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.
Post #1021511
Lisa Slater Nicholls
Lisa Slater Nicholls
Posted Tuesday, November 16, 2010 7:43 AM
Old Hand
Group: General Forum Members
Last Login: Saturday, May 18, 2013 1:35 PM
Points: 301,
Visits: 473
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<
Post #1021523
David Rueter
David Rueter
Posted Tuesday, November 16, 2010 9:09 AM
SSC Rookie
Group: General Forum Members
Last Login: Wednesday, May 15, 2013 11:22 PM
Points: 44,
Visits: 357
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.
Post #1021565
getoffmyfoot
getoffmyfoot
Posted Tuesday, November 16, 2010 12:32 PM
Old Hand
Group: General Forum Members
Last Login: Tuesday, February 12, 2013 1:09 PM
Points: 335,
Visits: 391
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
Post #1021746
David.Poole
David.Poole
Posted Tuesday, November 16, 2010 3:21 PM
SSCrazy
Group: General Forum Members
Last Login: Today @ 9:35 AM
Points: 2,749,
Visits: 1,407
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
LinkedIn Profile
Post #1021827
asiraky
asiraky
Posted Tuesday, November 16, 2010 3:58 PM
Forum Newbie
Group: General Forum Members
Last Login: Tuesday, December 07, 2010 4:13 AM
Points: 6,
Visits: 13
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.
Post #1021844
« Prev Topic
|
Next Topic »
29 posts, Page 1 of 3
1
2
3
»
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
