2

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?

1

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.

  • So, I think that it is better to remove all the views, or do I need to remove only the indices? – VansFannel Sep 29 '16 at 9:48
  • It's the indexes that might slow you down, not the views. Dropping the indexes or keeping them is a decision based on overall performance, evaluated on the whole workload. – spaghettidba Sep 29 '16 at 10:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.