|
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?
(comments are locked)
|
|
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 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 '10 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 '10 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 '10 at 06:14 AM
TimothyAWiseman
(comments are locked)
|
|
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.
(comments are locked)
|
|
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.
(comments are locked)
|
