Stable indexed views on big active tables.

If an indexed view is a small subset (a few thousand rows and a handful of columns) of a table that is: a) very long (a few million rows), and b) very wide (over 100 columns), and c) subject to frequent inserts and uptdates (~30,000 per week) but no deletes though FWIW AND the rows in the indexed view only change rarely (one or two inserts/updates per day), will the index interfere with writes to the underlying table?

In other words, in writing an insert or update to the underlying table, is SQL Server going to have to devote a lot of resources to checking WHETHER the indexed view needs to change, regardless of whether it ultimately does change?
more ▼

asked Aug 12, 2010 at 10:13 PM in Default

GPO gravatar image

2.9k 35 38 42

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first
Hah, well it depends really on the complexity of the view. It's difficult to say for sure without doing a lot of testing - especially because the optimiser in 2000, 2005, 2008 will make different decisions, but also the optimiser in standard .v. enterprise will make different decisions in those editions (for example, indexed views aren't used in standard unless you specify WITH (NOEXPAND)).
more ▼

answered Aug 13, 2010 at 12:54 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.5k 61 65 87

We might be in luck. The query is pretty straightforward. No joins, no functions in the WHERE clause, no transformations in the SELECT. I have to say it's been a revelation from a reporting perspective. My queries are 120 times faster. Just didn't want inconvenience anyone.
Aug 13, 2010 at 01:19 AM GPO
I think, to be honest, the index wouldn't be much more expensive than an index on a table then. I can't stand by that though - I'd have to do some reasonably extensive testing to be sure.
Aug 13, 2010 at 03:57 AM Matt Whitfield ♦♦

Without doing testing, I strongly suspect that the index would come with overhead for all write operations. Even if no changes are made to the index at all, the SQL Engine still has to take the time to determine if it needs to make changes to the engine.

With that said, I second Matt's thought that it would not be more expensive than an index on the table itself.

As a general rule, the trade off between write performance and read performance is a good one to make and I make extensive use of indexed views myself.
Aug 13, 2010 at 06:14 AM TimothyAWiseman
(comments are locked)
10|1200 characters needed characters left
With "test, test, test" should go "monitor, monitor, monitor" post-implementation. Keep an eye on things. If you've got possible concerns about the performance of any Writes to the table, you should watch out for it. Get a baseline before you make the change, and then monitor performance afterwards.
more ▼

answered Aug 14, 2010 at 01:03 AM

ThomasRushton gravatar image

ThomasRushton ♦
34.2k 18 20 44

(comments are locked)
10|1200 characters needed characters left
Thanks all for your thoughtful input (as usual). Again the underlying theme is "test, test, test"! I'll get back if I discover anything interesting.
more ▼

answered Aug 14, 2010 at 12:55 AM

GPO gravatar image

2.9k 35 38 42

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Aug 12, 2010 at 10:13 PM

Seen: 1167 times

Last Updated: Aug 12, 2010 at 10:13 PM