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

I'm working in SQL Server 2019 and facing an issue where when making an update, it takes 20 seconds for only 162 rows.

-
Estimated execution plan

-
Actual execution plan

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

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]
(

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.

StackExchange Database Administrators Q#307942, answer score: 10