Click here to Skip to main content
15,563,248 members
Please Sign up or sign in to vote.
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
Updated 20-Feb-22 23:59pm

1 solution

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
 
Share this answer
 
v2

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900