How to enhance update to be faster for small count of updated rows?



  • I work in SQL Server 2019. I'm facing an issue.

    When making update, it takes 20 seconds for 162 rows only.

    So how to enhance the update statement to be faster?

     Update FT SET
      ft.ValueName=avo.name
     FROM #package FT 
     inner join  parts.Nop_PackageAttribute PA   with(nolock)on PA.PackageID=ft.PackageID and PA.[Key]=FT.ZfeatureId 
     inner join  Nop_AcceptedValuesOption AVO with(nolock) ON convert(varchar(20),AVO.AcceptedValuesOptionID)=PA.Value 
     where FT.AcceptedValueID is not null
    

    Estimated execution plan:

    https://www.brentozar.com/pastetheplan/?id=H1jdK3Veq

    Actual execution plan:

    https://www.brentozar.com/pastetheplan/?id=BJ9Oi_Hxq

    Scripts sample:

     create table #package
     (
     id int PRIMARY KEY IDENTITY(1,1),
     ZfeatureId INT NULL,
     AcceptedValueID INT NULL,
     PackageID INT NULL,
     ValueName NVARCHAR(2000) default ''
     )
    

    Indexes I have on the #package table:

     create nonclustered index IDX_PackageID on #package(PackageID) include (ZfeatureId,AcceptedValueID , ValueName)
     create index acceptedvaluesidpackage_idx on #package(AcceptedValueID)
    

    Package attributes table:

    ALTER TABLE [Parts].[Nop_PackageAttribute] ADD  CONSTRAINT [PK_Nop_PackageAttribute] PRIMARY KEY CLUSTERED 
    (
        [PackageAttributeID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Customer]
    GO
    CREATE NONCLUSTERED INDEX [_dta_index_Nop_PackageAttribute_8_578153155__K2_K1_K3_4] ON [Parts].[Nop_PackageAttribute]
    (
        [PackageID] ASC,
        [PackageAttributeID] ASC,
        [Key] ASC
    )
    INCLUDE (   [Value]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Customer]
    

    CREATE NONCLUSTERED INDEX [IDX_Key] ON [Parts].[Nop_PackageAttribute]
    (
    [Key] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Customer]

    CREATE NONCLUSTERED INDEX [IDX_PakageID] ON [Parts].[Nop_PackageAttribute]
    (
    [PackageID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Customer]
    GO
    CREATE NONCLUSTERED INDEX [IX_Nop_PackageAttribute_Key] ON [Parts].[Nop_PackageAttribute]
    (
    [Key] ASC
    )
    INCLUDE ( [PackageID],
    [Value]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Customer]

    CREATE TABLE [Parts].[Nop_PackageAttribute](
    [PackageAttributeID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [PackageID] [int] NOT NULL,
    [Key] [int] NOT NULL,
    [Value] nvarchar NOT NULL,
    [CreatedDate] [datetime] NULL,
    [CreatedBy] [int] NULL,
    [ModifiedDate] [datetime] NULL,
    [ModifiedBy] [int] NULL,
    [DeletedDate] [datetime] NULL,
    [DeletedBy] [int] NULL,
    CONSTRAINT [PK_Nop_PackageAttribute] PRIMARY KEY CLUSTERED
    (
    [PackageAttributeID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Customer]
    ) ON [Customer] TEXTIMAGE_ON [PRIMARY]

    Nop_AcceptedValuesOption table:

    ALTER TABLE [dbo].[Nop_AcceptedValuesOption] ADD  CONSTRAINT [PK_Nop_AcceptedValuesOption] PRIMARY KEY CLUSTERED 
    (
        [AcceptedValuesOptionID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Customer]
    GO
    

    CREATE NONCLUSTERED COLUMNSTORE INDEX [dta_index_Nop_AcceptedValuesOption_5_1669580986__col_] ON [dbo].[Nop_AcceptedValuesOption]
    (
    [AcceptedValuesOptionID],
    [AcceptedValuesID],
    [Name],
    [DisplayOrder],
    [Description],
    [CreatedDate],
    [CreatedBy],
    [ModifiedDate],
    [ModifiedBy],
    [DeletedDate],
    [DeletedBy],
    [Is_Split],
    [AcceptedValuesOption_Value],
    [AcceptedValuesOption_Unit]
    )WITH (DROP_EXISTING = OFF) ON [Customer]

    CREATE NONCLUSTERED INDEX [_dta_index_Nop_AcceptedValuesOption_8_1074154922__K1_3] ON [dbo].[Nop_AcceptedValuesOption]
    (
    [AcceptedValuesOptionID] ASC
    )
    INCLUDE ( [Name]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Customer]
    GO
    CREATE NONCLUSTERED INDEX [_dta_index_Nop_AcceptedValuesOption_8_1074154922__K2_K4_1_3] ON [dbo].[Nop_AcceptedValuesOption]
    (
    [AcceptedValuesID] ASC,
    [DisplayOrder] ASC
    )
    INCLUDE ( [AcceptedValuesOptionID],
    [Name]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Customer]
    GO
    CREATE NONCLUSTERED INDEX [NonClusteredIndex-20160824-070515] ON [dbo].[Nop_AcceptedValuesOption]
    (
    [AcceptedValuesID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Customer]
    GO
    CREATE NONCLUSTERED INDEX [NonClusteredIndex-20160829-015901] ON [dbo].[Nop_AcceptedValuesOption]
    (
    [Name] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Customer]
    GO

    CREATE TABLE [dbo].[Nop_AcceptedValuesOption](
    [AcceptedValuesOptionID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [AcceptedValuesID] [int] NOT NULL,
    [Name] nvarchar NOT NULL,
    [DisplayOrder] [int] NOT NULL,
    [Description] varchar NULL,
    [CreatedDate] [datetime] NULL,
    [CreatedBy] [int] NULL,
    [ModifiedDate] [datetime] NULL,
    [ModifiedBy] [int] NULL,
    [DeletedDate] [datetime] NULL,
    [DeletedBy] [int] NULL,
    [Is_Split] [int] NULL,
    [AcceptedValuesOption_Value] [float] NULL,
    [AcceptedValuesOption_Unit] nvarchar NULL,
    [IsDeleted] [bit] NULL,
    CONSTRAINT [PK_Nop_AcceptedValuesOption] PRIMARY KEY CLUSTERED
    (
    [AcceptedValuesOptionID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Customer]
    ) ON [Customer]

    GO

    SET ANSI_PADDING ON
    GO

    ALTER TABLE [dbo].[Nop_AcceptedValuesOption] ADD CONSTRAINT [DF_IsDeleted] DEFAULT ((0)) FOR [IsDeleted]
    GO

    ALTER TABLE [dbo].[Nop_AcceptedValuesOption] CHECK CONSTRAINT [FK_Nop_AcceptedValuesOption_Nop_AcceptedValues]
    GO



  • Adding to Brendan’s answer, swap this conversion

    inner join  Nop_AcceptedValuesOption AVO with(nolock) 
    ON convert(varchar(20),AVO.AcceptedValuesOptionID)=PA.Value 
    

    to

    inner join  Nop_AcceptedValuesOption AVO 
    ON AVO.AcceptedValuesOptionID = try_cast(PA.Value as int)
    

    And you should be able to replace the non-clustered columnstore scan with 162 index lookups. If you don't get a nested loop plan, try inner loop join.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2