When update table small number of rows it take too too much time how to handle ?

Question

Post reply

When update table small number of rows it take too too much time how to handle ?

ahmed_elbarbary.2010

Hall of Fame

Points: 3118

February 18, 2022 at 6:58 pm

#3985542

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

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

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

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,
      [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]

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004366 More actions · Answer 1

It looks to me like you're forming a ton of accidental cross joins in the form of many-to-many joins because of the insufficient join criteria of partid = partid.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 2

Since the SELECT takes 11 minutes like the UPDATE, it sounds to me like the data lookup process is what is so slow. I would look at your execution plan (actual and estimated) to see where the performance problems are and a lot of those are happening during the sort operations.

One thing you could try that may (or may not) help would be to run the query on a single thread (maxdop 1). It MAY be the parallelism causing some self blocking (I have seen that before).

Failing that, reducing the number of indexes on the table you are updating will improve performance and you MAY get a performance boost by fixing the statistics on ManufacturingData as your estimated rows is vastly different from actual. With your indexes on ManufactureMaximumReflowTemperatures, I see one that can likely go away without causing any impact (note test this on a test system first!) - featurenames_idx as that column is already in a different index that would cover any queries that use it - featurenameandvalue_idx.

I would probably evaluate if all of those indexes are required as each new index increases space on disk as well as INSERT, UPDATE, and DELTE times. The fewer indexes you have on a table, the better the overall performance. If the indexes are not used or infrequently used or offer minimal performance benefit, it may make sense to remove them.

Another thing to check would be while the query is running, is there any blocking? I personally would not use the NOLOCK hint, but others may disagree.

Something that may help (likely will) is adding an index on ManufacturingData.MaximumReflowTemperatureID.

With all of the advice, I recommend testing it on a test system to see if it helps or hurts. If it helps, test other queries too. Don't want to fix 1 problem only to create 100 new performance issues.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Ed B Hall of Fame Points: 3666 More actions · Answer 3

Are your statistics outs of date on the Parts.ManufacturingData table? PartID is the primary key and is the join, but the estimated rows for the clustered index scan is 33 million while the actual rows is 15 million. The estimated and actual are very similar for the other clustered index scan. I don't think there is a Cartesian product, because the PartID is unique in the ManufacturingData table. The most rows it can join to is the total rows in the other table.

If the select query takes as long as the update, then I would look at the execution plan of the select query. The index updates are unlikely to be the cause, because the select query takes just as long.

This may be overly simplistic and you may get a far more specific answer, but I would try updating the statistics or rebuilding the clustered index on the ManufacturingData table and then try the select query again.