更新大表時更新時間太長

March 4, 2022

我在 sql server 2019 上工作

更新表時Z2DataCore.parts.SourcingNotMappedParts 我發現更新時緩慢而長時間

更新語句需要 26.5 分鐘才能完成對 692488 行計數的更新，因此 How to make Faster 。

語句生成緩慢的過程如下：

UPDATE s 
SET s.PriorityLevel = 'I2'   
FROM Z2DataCore.parts.SourcingNotMappedParts s 
INNER JOIN extractreports.dbo.alldistSuppliersourceid g
   ON g.SourcingNotMappedPartsID = s.SourcingNotMappedPartsID

受影響的更新行數將是 692488

我需要更新的表 SourcingNotMappedParts 通常有 7100 萬行。

extractreports.dbo.SourcingNotMappedPartsIDI1我將從它獲取要更新的數據的表是 692488 。

表extractreports.dbo.SourcingNotMappedPartsIDI1只有一SourcingNotMappedPartsID列int datatype

表extractreports.dbo.SourcingNotMappedPartsIDI1只有一個索引如下

CREATE clustered INDEX SourcingNotMappedPartsIDI1_IDX ON extractreports.dbo.SourcingNotMappedPartsIDI1(SourcingNotMappedPartsID)



USE [Z2DataCore]
GO
/****** Object:  Table [Parts].[SourcingNotMappedParts]    Script Date: 3/4/2022 12:05:36 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Parts].[SourcingNotMappedParts](
 [SourcingNotMappedPartsID] [int] IDENTITY(1,1) NOT NULL,
 [SearchPart] [nvarchar](200) NULL,
 [GivenManufacture] [nvarchar](200) NULL,
 [CompanyId] [int] NULL,
 [SourceTypeID] [int] NULL,
 [PartStatus] [nvarchar](50) NULL,
 [StockId] [int] NULL,
 [SourceUrl] [nvarchar](2000) NULL,
 [PartId] [int] NULL,
 [GroupID] [int] NULL,
 [PartStatusID] [int] NULL,
 [MatchStatus] [nvarchar](200) NULL,
 [GivenPartNumber_Non] [nvarchar](200) NULL,
 [GivenManufacturer_Non] [nvarchar](200) NULL,
 [signatureID] [int] NULL,
 [VCompanyId] [int] NULL,
 [PriorityLevel] [nvarchar](10) NULL,
 [NotMappedCode] [int] NULL,
 CONSTRAINT [PK_Parts.SourcingNotMappedParts] PRIMARY KEY CLUSTERED 
(
 [SourcingNotMappedPartsID] 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
SET ANSI_PADDING ON
   
GO
/****** Object:  Index [IDX_MatchStatus_StatusID]    Script Date: 3/4/2022 12:05:37 AM ******/
CREATE NONCLUSTERED INDEX [IDX_MatchStatus_StatusID] ON [Parts].[SourcingNotMappedParts]
(
 [PartStatusID] ASC,
 [MatchStatus] 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
/****** Object:  Index [IDX_Part_status_ID]    Script Date: 3/4/2022 12:05:37 AM ******/
CREATE NONCLUSTERED INDEX [IDX_Part_status_ID] ON [Parts].[SourcingNotMappedParts]
(
 [PartStatusID] 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
SET ANSI_PADDING ON
   
GO
/****** Object:  Index [IDX_SourceURL]    Script Date: 3/4/2022 12:05:37 AM ******/
CREATE NONCLUSTERED INDEX [IDX_SourceURL] ON [Parts].[SourcingNotMappedParts]
(
 [SourceUrl] 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
SET ANSI_PADDING ON
   
GO
/****** Object:  Index [IDX_SourcingNotMappedParts_GroupID_SearchPart]    Script Date: 3/4/2022 12:05:37 AM ******/
CREATE NONCLUSTERED INDEX [IDX_SourcingNotMappedParts_GroupID_SearchPart] ON [Parts].[SourcingNotMappedParts]
(
 [GroupID] ASC,
 [SearchPart] ASC
)
INCLUDE (  [signatureID]) 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
/****** Object:  Index [IDX_SourcingNotMappedParts_PartId]    Script Date: 3/4/2022 12:05:37 AM ******/
CREATE NONCLUSTERED INDEX [IDX_SourcingNotMappedParts_PartId] ON [Parts].[SourcingNotMappedParts]
(
 [PartId] 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
/****** Object:  Index [IDX_SourcingNotMappedParts_SignatureID]    Script Date: 3/4/2022 12:05:37 AM ******/
CREATE NONCLUSTERED INDEX [IDX_SourcingNotMappedParts_SignatureID] ON [Parts].[SourcingNotMappedParts]
(
 [signatureID] ASC
)
INCLUDE (  [PartId]) 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
SET ANSI_PADDING ON
   
GO
/****** Object:  Index [IX_NotMapped_NonalphaPartCompany]    Script Date: 3/4/2022 12:05:37 AM ******/
CREATE NONCLUSTERED INDEX [IX_NotMapped_NonalphaPartCompany] ON [Parts].[SourcingNotMappedParts]
(
 [GivenPartNumber_Non] ASC,
 [VCompanyId] 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
/****** Object:  Index [IX_NotMapped_NotMappedCode]    Script Date: 3/4/2022 12:05:37 AM ******/
CREATE NONCLUSTERED INDEX [IX_NotMapped_NotMappedCode] ON [Parts].[SourcingNotMappedParts]
(
 [NotMappedCode] 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
SET ANSI_PADDING ON
   
GO
/****** Object:  Index [IX_NotMapped_PriorityLevel]    Script Date: 3/4/2022 12:05:37 AM ******/
CREATE NONCLUSTERED INDEX [IX_NotMapped_PriorityLevel] ON [Parts].[SourcingNotMappedParts]
(
 [PriorityLevel] 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
/****** Object:  Index [IX_NotMapped_SourceType]    Script Date: 3/4/2022 12:05:37 AM ******/
CREATE NONCLUSTERED INDEX [IX_NotMapped_SourceType] ON [Parts].[SourcingNotMappedParts]
(
 [SourceTypeID] ASC,
 [CompanyId] 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
SET ANSI_PADDING ON
   
GO
/****** Object:  Index [IX_NotMapped_VCompanyId_sourcetypeid]    Script Date: 3/4/2022 12:05:37 AM ******/
CREATE NONCLUSTERED INDEX [IX_NotMapped_VCompanyId_sourcetypeid] ON [Parts].[SourcingNotMappedParts]
(
 [VCompanyId] ASC,
 [SourceTypeID] ASC,
 [PriorityLevel] ASC
)
INCLUDE (  [GivenPartNumber_Non]) 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
SET ANSI_PADDING ON
   
GO
/****** Object:  Index [IX_SourcingNotMappedParts_VCompanyId]    Script Date: 3/4/2022 12:05:37 AM ******/
CREATE NONCLUSTERED INDEX [IX_SourcingNotMappedParts_VCompanyId] ON [Parts].[SourcingNotMappedParts]
(
 [VCompanyId] ASC
)
INCLUDE (  [CompanyId],
 [SourceTypeID],
 [StockId],
 [GivenPartNumber_Non],
 [PriorityLevel]) 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

那麼如何解決緩慢的問題呢？

我檢查了更新狀態，發現等待類型如下：

這與慢或不相關。

您更新的實際執行計劃也表明您的extractreports.dbo.alldistSuppliersourceid表上沒有索引，因為它目前正在對其使用表掃描操作。
它至少應該有一個聚集索引。如果該聚集索引在您的查詢目前加入的欄位上有意義SourcingNotMappedPartsID（取決於您按該列查詢的頻率），那麼您可以像這樣創建它：
USE extractreports;

CREATE CLUSTERED INDEX IX_alldistSuppliersourceid_ ON dbo.alldistSuppliersourceid (SourcingNotMappedPartsID);
如果SourcingNotMappedPartsID是唯一的，extractreports.dbo.alldistSuppliersourceid那麼您還可以在UNIQUE關鍵字之後指定CREATE關鍵字。
否則，如果另一個欄位可以作為上的聚集索引extractreports.dbo.alldistSuppliersourceid，則使用上面的腳本替換該列來創建聚集索引，您可以在其上創建一個額外的非聚集索引，如下所示：
USE extractreports;

CREATE NONCLUSTERED INDEX IX_alldistSuppliersourceid_ ON dbo.alldistSuppliersourceid (SourcingNotMappedPartsID);
同樣，UNIQUE如果它也適用，請使用關鍵字。
那個指數至少應該在一定程度上幫助你的表現。

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

更新大表時更新時間太長

相關問答

何時從表中選擇前 5 行需要太多時間？

為什麼對索引列的不等式搜尋會給出恆定掃描

實際行和估計行差異很大

在事務中的過程中添加可選更新的最佳方法是什麼？

通過過濾聚集索引和附加列來查詢具有 200+ 百萬行的表非常慢

為什麼是小號eemingliy_小號和和米一世nG一世是Seemingly未在帶有 OR 的 LEFT JOIN 上使用的合適索引