update small data is slow performance ?

Question

i work on sql server 2019 i face issue
when make update it take 20 second for 162 rows only
so how to enhance that please

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

execution plan as below

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

table

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

Answer 1

Hi @ahmed salah ,

Please read this article about many common sign meanings in execution plan:
https://www.red-gate.com/simple-talk/databases/sql-server/performance-sql-server/graphical-execution-plans-for-simple-sql-queries/
In your execution plan, the number of hash match is high, which sometimes indicates:
a missing or incorrect index
a missing WHERE clause
In addition, you should pay attention to update warning in your execution plan and Erland give his suggestion.
Then you can check the new execution plan.

Best regards,
Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
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.

Answer 2

You could try changing:

``
convert(varchar(20),AVO.AcceptedValuesOptionID)=PA.Value

to

AVO.AcceptedValuesOptionID = try_convert(int, PA.Value)

But even better you may want to review the overall table design. This kind of type conversions are a very good recipe for bad performance.

Answer 3

Review the execution plan, there are at least 2 scans and an expensive hash match; and this because of missing suitable indexes.