Update table extremely slow when the table has over 1000 indexed views

Question

We have one big table with category column and we created an indexed view for each category to speed up query performance. It does work very well.
During testing, we found when the number of category is over 1000, the update/delete and insert performance will be extremely slow. The query plan also needs long time to display.
I understand this is a bad design. Before I make design change, I want to find out if there is a way to speed up update/delete and insert query performance when base table has been referenced by over 1000 indexed views. I searched the SQL server documents (https://learn.microsoft.com/en-us/sql/relational-databases/views/create-indexed-views?view=sql-server-ver15) As a result, DML query performance can degrade significantly, or in some cases, a query plan cannot even be produced. In such scenarios, test your DML queries before production use, analyze the query plan and tune/simplify the DML statement.
The problem is my test query is very simple: update bigtable set value=1 where id=1 and category=1
This is the indexed view statement: CREATE VIEW [dbo].[V1] WITH SCHEMABINDING AS SELECT ID, Value From [dbo].[BigTable] WHERE Category=1;
But It took over 7 seconds to complete!
Notes: I used 5000 categories for testing.
Any suggestions are welcome!

Answer 1

You are right, this is a bad design. And when you have a bad design, you want to fix the design, not look for some method to make the bad design run faster. In this case, I think the design change is simple. First drop all those indexed views. Then create one indexed view like

CREATE VIEW [dbo].[V] WITH SCHEMABINDING AS SELECT Category, ID, Value From [dbo].[BigTable];

CREATE UNIQUE CLUSTERED INDEX [IDX_V]
   ON [dbo.].[V] (Category, ID);

(Note that for the clustered index I'm assuming that the clustered indexes you currently have on your views have only one key, that is ID. If the key to those indexes is ID, Value, then make the key of this new index Category, ID, Value).

You will have to test, of course. But I'm pretty sure that this will run your sample query very fast no matter how many categories you have and no matter which category you want to update.

Tom

Answer 2

I agree with Tom, this is a bad design, and you need to fix it.

Indexed views can be useful, but 1000 of them on a single table? Even 10 is a high number here! And it is not only a matter of the cost for updating, but there is also a cost for the optimizer to match queries against the view, and it decide to give them a miss. (Although you can use the NOEXPAND hint to shortcut.)

As you describe the problem, I don't see the need for a single indexed view. The design that Tom suggested can be replaced with a single non-clustered index:

CREATE UNIQUE INDEX Category_ID_ix ON BigTable(Category, ID) INCLUDE (Value)

Answer 3

HI @fredy ,

Thank you so much for posting here.

As mentioned in Designing Indexed Views,it has information on what to consider when designing/implimenting indexed views. > Indexed views work best when the underlying data is infrequently updated. The maintenance of an indexed view can be greater than the cost of maintaining a table index.

Totally agreed with other experts, you could consider to create the indexes on tables instead of views.

Before you make design change, you have limited choices.

You could have a try with below when you perform a SELECT statement.

One: Using the NOEXPAND view hint

SELECT Column1, Column2, ... FROM Table1, View1 WITH (NOEXPAND) WHERE ...  

Two: Using the EXPAND VIEWS query hint

SELECT Column1, Column2, ... FROM Table1, View1 WHERE ...  
OPTION (EXPAND VIEWS)  

Please refer more details in Improving Performance with SQL Server 2005 Indexed Views and check whether this is helpful to you.

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table

Answer 4

Indexed views are typically when you have GROUP BY. In your case, you don't have GROUP BY in the view definition, so just create a regular index directly on the table, as Erland suggests.