When update table small number of rows it take too much time may be 11 minutes although I create indexes why this very slow?

Question

0.00/5 (No votes)

See more:

I work on sql server 2019 when update 20 rows or no rows it take 11 minute

I mean 20 rows or no rows by different on m.MaximumReflowTemperatureID <>r.z2valueid between two tables

why update is very slow although I update small number of rows or no rows updated

when update 20 rows or no rows different on value on table ManufactureMaximumReflowTemperatures take 11 minutes.

So How to handle that ?

my actual execution plan as below :

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

statment update that take too much time

statment update that take too much time

SQL

update r set  r.z2valueid=m.MaximumReflowTemperatureID  from [OperationsTablesDB].[dbo].[ManufactureMaximumReflowTemperatures] r
inner join z2datacore.parts.manufacturingdata m with(nolock) on m.partid=r.zpartid
where m.MaximumReflowTemperatureID <>r.z2valueid

this table I need updated [OperationsTablesDB].[dbo].[ManufactureMaximumReflowTemperatures]

have 14 milion rows

and another table join with it ManufacturingData have 15 milion rows

tables scripts sample

CREATE TABLE [dbo].[ManufactureMaximumReflowTemperatures](
     [ID] [int] NOT NULL,
     [zpartid] [int] NULL,
     [key] [varchar](50) NULL,
     [value] [varchar](60) NULL,
     [Z2ValueID] [int] NULL,
     [csfeatureid] [int] NULL,
     [csvalueid] [int] NULL,
 PRIMARY KEY CLUSTERED 
 (
     [ID] ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 ) ON [PRIMARY]
    
 GO
    




 ALTER TABLE [dbo].[ManufactureMaximumReflowTemperatures] ADD PRIMARY KEY CLUSTERED 
 (
     [ID] 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 [PRIMARY]
 GO
    
 CREATE NONCLUSTERED INDEX [featurenameandvalue_idx] ON [dbo].[ManufactureMaximumReflowTemperatures]
 (
     [csfeatureid] ASC,
     [Z2ValueID] 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 [PRIMARY]
 GO
    
 CREATE NONCLUSTERED INDEX [featurenames_idx] ON [dbo].[ManufactureMaximumReflowTemperatures]
 (
     [csfeatureid] 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 [PRIMARY]
 GO
    
 CREATE NONCLUSTERED INDEX [manufacturemax_idx] ON [dbo].[ManufactureMaximumReflowTemperatures]
 (
     [Z2ValueID] ASC,
     [value] 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 [PRIMARY]
 GO
    
 CREATE NONCLUSTERED INDEX [manufacturemaxvalues_idx] ON [dbo].[ManufactureMaximumReflowTemperatures]
 (
     [Z2ValueID] ASC,
     [csvalueid] 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 [PRIMARY]
 GO
 CREATE NONCLUSTERED INDEX [zpartid_idx] ON [dbo].[ManufactureMaximumReflowTemperatures]
 (
     [zpartid] 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 [PRIMARY]
 GO
 CREATE TABLE [Parts].[ManufacturingData](
     [LeadFinishId] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
     [PartID] [int] NOT NULL,
     [LeadFinishMaterial] [varchar](50) NULL,
     [CreatedDate] [datetime] NULL,
     [CreatedBy] [int] NULL,
     [ModifiedDate] [datetime] NULL,
     [Modifiedby] [int] NULL,
     [DeletedDate] [datetime] NULL,
     [DeletedBy] [int] NULL,
     [MaximumReflowTemperatureID] [int] NULL,
        
  CONSTRAINT [PK_PartID] PRIMARY KEY CLUSTERED 
 (
     [PartID] 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 [Parts].[ManufacturingData] ADD  CONSTRAINT [PK_PartID] PRIMARY KEY CLUSTERED 
 (
     [PartID] 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]

What I have tried:

SQL

select r.z2valueid,m.MaximumReflowTemperatureID  from [OperationsTablesDB].[dbo].[ManufactureMaximumReflowTemperatures] r
inner join z2datacore.parts.manufacturingdata m with(nolock) on m.partid=r.zpartid
where m.MaximumReflowTemperatureID <>r.z2valueid

when try above statment update and replace update with select it take same time 11 minutes

Posted 18-Feb-22 7:54am

ahmed_sa

Updated 20-Feb-22 22:59pm

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

_Asif_ · Answer 1 · 2022-02-20T22:59:00

Updates over large volume tables require a different approach. Usually, we don't understand the underlying complexity and treat both updates either over small or large volumes in a similar way.

Let's discuss your design first.

* You are creating primary key index twice (at creation time and then at modification time). The second alter index will return in an error.

* Your table ManufactureMaximumReflowTemperatures contains 7 fields and out of 7 fields, 6 are part of different indexes. This is a bad design

* You have 5 non clustered indexes on repeated columns
* featurenameandvalue_idx on ManufactureMaximumReflowTemperatures(csfeatureid, Z2ValueID)
* featurenames_idx on ManufactureMaximumReflowTemperatures(csfeatureid)
* manufacturemax_idx on ManufactureMaximumReflowTemperatures(Z2ValueID,value)
* manufacturemaxvalues_idx on ManufactureMaximumReflowTemperatures(Z2ValueID, csvalueid)
* zpartid_idx on ManufactureMaximumReflowTemperatures(zpartid)

Above indexes can be adjusted in one index ManufactureMaximumReflowTemperatures(zpartid, csfeatureid, key), removing all unnecessary indexes

* Each index can become a liability if has not been throroughly reviewed. Index can become overhead because for each insert, update and delete the apporaite indexes needed to be updated. In your case three indexes featurenameandvalue_idx, manufacturemax_idx and manufacturemaxvalues_idx needs to be updated for each update in ManufactureMaximumReflowTemperatures table.
The question is why. Usually, we don't create indexes on fields that get updated quickly for the same reason stated above. Index fields are meant to improve search so ideally should not be part of frequent updates.

* In plain English, your index is like update all ManufactureMaximumReflowTemperatures rows from all rows of manufacturingdata in such a way that zpart id is same as partid and z2valueid is not equal to MaximumReflowTemperatureID. Please check for the word "all". For large volume indexes updates needs to be precise, the subset needs to be closed not open.

* For large volume tables Updates needs to be based on primary index only because addition of one field from non clustered indexes will push index scan that leads to scan index for millions of entries. Update has to be like this

SQL

UPDATE ManufactureMaximumReflowTemperatures
SET  z2valueid = <value)
WHERE ID = 123; //Please note even an in caluse can cause problem at times.

You can modify your query in such a way that only 20 rows should be participating in the update. For example, your update query can be re-written in the following way.

SQL

; With CTE as
(
    select r.ID, m.MaximumReflowTemperatureID  
	from [OperationsTablesDB].[dbo].[ManufactureMaximumReflowTemperatures] r
	inner join z2datacore.parts.manufacturingdata m with(nolock) on m.partid=r.zpartid
	where m.MaximumReflowTemperatureID <> r.z2valueid	
)
update M
set z2valueid = C.MaximumReflowTemperatureID
from  ManufactureMaximumReflowTemperatures M  inner join CTE C on M.id = C.id

As you can see the entire update has been modified to use primary key column ID