SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Simple view question regarding indexes


Simple view question regarding indexes

Author
Message
NineIron
NineIron
SSC Eights!
SSC Eights! (837 reputation)SSC Eights! (837 reputation)SSC Eights! (837 reputation)SSC Eights! (837 reputation)SSC Eights! (837 reputation)SSC Eights! (837 reputation)SSC Eights! (837 reputation)SSC Eights! (837 reputation)

Group: General Forum Members
Points: 837 Visits: 656
Do views take advantage of the underlying tables' indexes or does one need to add an index with the view being schema bound?
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39784 Visits: 38563
Yes, the queries used to define views will take advantage of the indexes on the underlying tables if they assist in the return of data from those tables.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
NineIron
NineIron
SSC Eights!
SSC Eights! (837 reputation)SSC Eights! (837 reputation)SSC Eights! (837 reputation)SSC Eights! (837 reputation)SSC Eights! (837 reputation)SSC Eights! (837 reputation)SSC Eights! (837 reputation)SSC Eights! (837 reputation)

Group: General Forum Members
Points: 837 Visits: 656
Thanx. So, what's the advantage of adding an index to a view?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88174 Visits: 45277
If the view has an index, SQL may not need to go to the base tables at all.

Bear in mind there are a massive set of restrictions around indexed views. Not all views can be indexes.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


NineIron
NineIron
SSC Eights!
SSC Eights! (837 reputation)SSC Eights! (837 reputation)SSC Eights! (837 reputation)SSC Eights! (837 reputation)SSC Eights! (837 reputation)SSC Eights! (837 reputation)SSC Eights! (837 reputation)SSC Eights! (837 reputation)

Group: General Forum Members
Points: 837 Visits: 656
Thanx.
tim_harkin
tim_harkin
Old Hand
Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)

Group: General Forum Members
Points: 349 Visits: 920
NineIron (4/17/2014)
Thanx. So, what's the advantage of adding an index to a view?


When you access a non-indexed view, SQL will expand the query that defines the view to access the data that is needed. Let's say you have a view with an aggregation in it. SQL will chug through and re-calculate the aggregation every time you access the view. If you index the view, the aggregation may not need to be recalculated every time.

There are lots of caveats and drawbacks too, but that is one use case.
NineIron
NineIron
SSC Eights!
SSC Eights! (837 reputation)SSC Eights! (837 reputation)SSC Eights! (837 reputation)SSC Eights! (837 reputation)SSC Eights! (837 reputation)SSC Eights! (837 reputation)SSC Eights! (837 reputation)SSC Eights! (837 reputation)

Group: General Forum Members
Points: 837 Visits: 656
Thanx.
Brandie Tarvin
Brandie Tarvin
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14771 Visits: 9000
FYI: Indexed Views don't do well on high-volume insert tables.

I also seem to recall that trying to update the schema of underlying tables used in Indexed Views (WITH SCHEMABINDING) is nigh-on impossible without first dropping the view.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
NineIron
NineIron
SSC Eights!
SSC Eights! (837 reputation)SSC Eights! (837 reputation)SSC Eights! (837 reputation)SSC Eights! (837 reputation)SSC Eights! (837 reputation)SSC Eights! (837 reputation)SSC Eights! (837 reputation)SSC Eights! (837 reputation)

Group: General Forum Members
Points: 837 Visits: 656
Thanx. My environment is basically reporting off of a DataWarehouse so, performance of the report/query is most important. Could I ask..........if the underlying table(s) is not indexed or poorly indexed (I have little control over this), will an indexed view work to speed up performance?
Brandie Tarvin
Brandie Tarvin
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14771 Visits: 9000
NineIron (4/18/2014)
if the underlying table(s) is not indexed or poorly indexed (I have little control over this), will an indexed view work to speed up performance?


Yes and no. The problem is that an indexed view uses cached data. So in that respect, yes. But when you have to refresh that data, it's still relying on the underlying tables. Plus it depends on what indexes you put in the view. There are lots of ways to screw up an index and make a query worse. I've done about half of them over the years. @=)

Of course, the best way to know the answer to any "performance saving solution" for sure is to test this in a Dev or sandbox server. See for yourself.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search