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.
1 Like
Just an update here... I've created a demo on 100 MILLION rows of data in a table and I can query it for totals and other information 7 different ways from Sunday in about 18 MILLI seconds.
The key is to use the indexed views for "pre-aggregation" instead of final results. It truly does "depend" but the things you can do if you hold your mouth just right while building them is absolutely fantastic.
Using Indexed Views on regular queries can help but keep in mind that it can be a HUGE duplication of data. As the knight in the cave of the Holy Grail said, "chose wisely".