SQL Server Optimization – Are Views Slowing Down an Update?

optimizationsql serverupdateview

I have a SQL Server database with an update sentence ran very often. I've seen on Activity Monitor that this update sentence trigger 27ms and I want to improve it.

I have checked execution plan and I have seen that the update sentence updates view's indices:
enter image description here

the update query is this:

UPDATE Code
    SET CommissioningFlag = @newCommFlagValue
                , Source = @source
                , UserName = @username
                , LastChange = CAST(SYSDATETIMEOFFSET() as nvarchar(50))
                , SentToNextLevel = 0
                , HelperCodeId = @helperCodeId
WHERE CodeId = @codeId

And the view highlighted script is:

CREATE VIEW dbo.PrintedCodesStatistics
WITH SCHEMABINDING
AS
    SELECT AggregationLevelId, COUNT_BIG(*) as CodesPrinted
    FROM dbo.Code
    WHERE   (CommissioningFlag != 255)
    GROUP BY AggregationLevelId

GO
CREATE UNIQUE CLUSTERED INDEX [IX_Printed_Statistics]
    ON [dbo].[PrintedCodesStatistics]([AggregationLevelId] ASC);

And finally, Code table script:

CREATE TABLE [dbo].[Code] (
    [CodeId]            INT            IDENTITY (1, 1) NOT NULL,
    [Serial]            NVARCHAR (20)  NOT NULL,
    [AggregationLevelId]  TINYINT      NOT NULL,
    [CommissioningFlag] TINYINT        NOT NULL,
    [... ]
    CONSTRAINT [PK_CODE] PRIMARY KEY CLUSTERED ([CodeId] ASC),
    CONSTRAINT [UC_CODE_SERIAL] UNIQUE NONCLUSTERED ([Serial] ASC)

Do the views slow-down the update query?

Best Answer

Indexed views contribute to the write overhead as much as regular indexes on the tables do, if not more.

Every time you write to the base tables, the index on the view has to be updated immediately.

As with regular indexes, there is a tradeoff: if your index slows down reasonably and gives a significant performance boost on reads, then keep it. If not, get rid of it.

Related Question