3

When I update 20 rows or no rows, it takes 11 minutes.

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

Why is my update so slow, even though I update a small number of rows, or even no rows?

How to handle that?

My actual execution plan:

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

Statement update that takes too long:

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 the above statement by replacing update with select, it takes the same time, 11 minutes.

The table I need updated [OperationsTablesDB].[dbo].[ManufactureMaximumReflowTemperatures] has 14 million rows and the other table on the join has 15 milion rows.

Sample tables script:

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]
1
  • You need to support your selection(from) with non-clustered index and it would be much faster. Please create index on z2valueid(table ManufactureMaximumReflowTemperatures) and partid(table manufacturingdata ). Update stats afterwards and check the performance. Feb 18, 2022 at 19:27

1 Answer 1

4

These two points from your post are going to be your biggest contribution factors.

have 14 milion rows

where m.MaximumReflowTemperatureID <>r.z2valueid

For starters, anytime you compare two columns in a table, regardless if equality or inequality comparisons, SQL Server is going to have to compare every row in the table to see if two columns meet the condition. It's not like a scenario where you're doing an index seek to find a row with ID = 5. In your case, the value you would be comparing to would be changing row by row. Therefore, there is no way to SEEK this data.

Add to the fact that you have 14 million rows in one table, and 15 million rows in the other.

Picture your query as a SELECT, but without the <> condition.

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

Run that select and see how long it takes, and how many rows it returns. Maybe grab an execution plan while you're at it. Now picture SQL Server having to pull all those rows, every time you run your update, even if there is no unequal values. That is because it has to look at both columns, side by side, before it knows if it needs to update that row.

Comparing two columns will almost always be a painful query. However, the following indexes could at least make it more tolerable.

CREATE NONCLUSTERED INDEX [zpartid_idx]
    ON [dbo].[ManufactureMaximumReflowTemperatures] ([zpartid])
    INCLUDE (z2valueid)

CREATE NONCLUSTERED INDEX [manufacturingdata_partid]
    ON [dbo].[manufacturingdata] ([partid])
    INCLUDE (MaximumReflowTemperatureID)

With these indexes, you'll hopefully be pulling smaller indexes into memory, I.E. fewer reads. This is because each of these indexes has two columns each, instead of all columns of the base tables. This should help your query run faster. Without these, you're most liklely doing clustered scans.

Also, make sure to take out the NOLOCK hint.

4
  • Even non-equality could pose performance issue - m.MaximumReflowTemperatureID <>r.z2valueid Feb 18, 2022 at 19:29
  • 1
    Yes, It seems I missed that, I was more focused on Index part. My bad! Feb 18, 2022 at 19:31
  • 1
    Yeah, the index can still help. If SQL is loading two column nonclustered indexes into memory instead of full clustered indexes, it'll have to read less data into memory. Feb 18, 2022 at 19:32
  • 1
    +1 for your detailed response. True, my suggestion in the comment was for that to the question. Feb 18, 2022 at 19:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.