更新語句非常慢，即使有索引也需要 11 分鐘才能更新 20 或 0 行

當我更新 20 行或沒有行時，需要 11 分鐘。

m.MaximumReflowTemperatureID <> r.z2valueid我的意思是兩個表之間有20 行或沒有行不同。

為什麼我的更新如此緩慢，即使我更新了少量的行，甚至沒有更新行？

如何處理？

我的實際執行計劃：

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

語句更新耗時過長：

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

當通過用 select 替換 update 來嘗試上述語句時，它需要相同的時間，11 分鐘。

我需要更新的表[OperationsTablesDB].[dbo].[ManufactureMaximumReflowTemperatures]有 1400 萬行，連接上的另一個表有 1500 萬行。

範例表腳本：

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]

您文章中的這兩點將成為您最大的貢獻因素。

有 1400 萬行

其中 m.MaximumReflowTemperatureID <>r.z2valueid

對於初學者，無論何時比較表中的兩列，無論是相等還是不等比較，SQL Server 都必須比較表中的每一行以查看兩列是否滿足條件。這不像您正在執行索引查找以查找 ID = 5 的行的情況。在您的情況下，您要比較的值將逐行更改。因此，沒有辦法尋找這些數據。

再加上一個表中有 1400 萬行，另一個表中有 1500 萬行。

將您的查詢想像為一個 SELECT，但沒有 <> 條件。

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

執行該選擇並查看它需要多長時間以及它返回多少行。也許在你做的時候抓住一個執行計劃。現在想像一下 SQL Server 必須在每次執行更新時提取所有這些行，即使沒有不相等的值。這是因為它必須並排查看兩列，然後才能知道是否需要更新該行。

比較兩列幾乎總是一個痛苦的查詢。但是，以下指標至少可以使其更容易忍受。

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

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

有了這些索引，您將有望將更小的索引拉入記憶體，從而減少 IE 的讀取次數。這是因為這些索引中的每一個都有兩列，而不是基表的所有列。這應該有助於您的查詢執行得更快。沒有這些，您最有可能進行集群掃描。

另外，請務必取出 NOLOCK 提示。

引用自：https://dba.stackexchange.com/questions/307708