主页 / dba / 问题 / 307708
Accepted
ahmed elbarbary
Asked: 2022-02-19 10:43:19 +0800 CST

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

  • 772

当我更新 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]
sql-server t-sql

1 个回答

  1. Best Answer

    您帖子中的这两点将成为您最大的贡献因素。

    有 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 提示。

    • 4

相关问题