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

fredy 21 Reputation points
2020-12-13T01:08:59.747+00:00

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!

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,654 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,551 questions
{count} votes

Accepted answer
  1. Tom Cooper 8,466 Reputation points
    2020-12-13T04:29:24.227+00:00

    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

    2 people found this answer helpful.

3 additional answers

Sort by: Most helpful
  1. Erland Sommarskog 100.8K Reputation points MVP
    2020-12-13T11:10:53.843+00:00

    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)
    
    0 comments No comments

  2. MelissaMa-MSFT 24,176 Reputation points
    2020-12-14T06:41:19.627+00:00

    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

    0 comments No comments

  3. tibor_karaszi@hotmail.com 4,301 Reputation points
    2020-12-14T11:49:45.177+00:00

    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.

    0 comments No comments