优化此 SQL 查询以提高性能

Question

ahmed elbarbary

Asked: 2022-03-04 15:40:46 +0800 CST2022-03-04 15:40:46 +0800 CST 2022-03-04 15:40:46 +0800 CST

更新大表时更新时间太长

772

我在 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

那么如何解决缓慢的问题呢？

我检查了更新状态，发现等待类型如下：

这与慢或不相关。

1 个回答

Voted

J.D. · Answer 1 · 2022-03-05T04:48:22+08:00

您更新的实际执行计划也表明您的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如果它也适用，请使用关键字。

那个指数至少应该在一定程度上帮助你的表现。

更新大表时更新时间太长

连接到 PostgreSQL 服务器：致命：主机没有 pg_hba.conf 条目

如何让sqlplus的输出出现在一行中？

选择具有最大日期或最晚日期的日期

如何列出 PostgreSQL 中的所有模式？

列出指定表的所有列

如何在不修改我自己的 tnsnames.ora 的情况下使用 sqlplus 连接到位于另一台主机上的 Oracle 数据库

你如何mysqldump特定的表？

使用 psql 列出数据库权限

如何从 PostgreSQL 中的选择查询中将值插入表中？

如何使用 psql 列出所有数据库和表？

更新大表时更新时间太长

1 个回答

相关问题