SQL Indexed Views : what happens if data has changed

Question

Hi. I undestand what indexed view are but i wonder what exactly happens when the data in underlaying table(s) has been changed? Is entire view cache discarded od just changed rows?

I have very complex query on several tables(+5) which is used for searching hotels availability(+100k records) and IMHO indexed view could improves performance significantly. But the data(few rows) are changed frequently(room was sold, free rooms altered in admin atc.) and if the view would be rebuild each time when just one row was changed, would be bottle neck for my app.

Or any ideas how to "cache" very coplex queries?

Thank you.

Answer 1

From the technet documentation:

Unlike ordinary indexes, a single row insert into any of the participating tables may cause multiple row changes in an indexed view. This is because the single row may join with multiple rows of another table. The same is true for updates and deletes. Consequently, the maintenance of an indexed view may be more expensive than maintaining an index on the table.

Source:

http://technet.microsoft.com/en-us/library/cc917715.aspx#XSLTsection124121120120

i.e. it sounds as though rows are updated where relevant, without necessarily having to update/recreate the view in its entirety.

Answer 2

I am assuming you are running sql server. 100k+ rows is not a lot. If you are joining 5ish tables I would suggest that you made sure there are covering indexes on the columns you are joining before considering implementing indexed views. Use the query analyzer to find the bottle neck in your query directly. If you are having performance problems on so small tables there are probably nasty table scans in there.