We're strongly considering Indexed Views. Anyone here had any experience with them - please share - was it worth it?
In our case, SQL Server is joining the same tables over and over again and this is both time-consuming and taking up a lot of CPU. I know one disadvantage for Indexed views is they require additional time for Inserts, Updates and Deletes. In this case I believe the Select improvements will outweigh this. However, I understand that we need to drop the Indexed View when we do changes to the underlying columns or need to add columns to the indexed view. This is where I have the most concern.
Correction, if we need to change the underlying columns or need to add columns to the indexed view, we must drop the indexes of the view first, then recreate 'em afterwards. OK, that's not so bad, we can do that during a quiet time.
It sounds a little bit like you might be considering Indexed Views as if they were some sort of performance panacea. They're not. They can have a serious impact on Inserts, Updates, and Deletes. And, just like any index, they are a duplication of data.
I'm NOT saying to avoid them. I'm saying to apply them even more carefully than you would a "covering index" especially if they're the type that covers a query that isn't an aggregation (they CAN be fantastic for what I refer to as "pre-aggregation").
There are also a huge number of restrictions as to what the view can contain or not.