Stored-Procedures
何時從表中選擇前 5 行需要太多時間?
我在 sql server 2019 上工作,我在選擇
top 5
行時遇到問題,它需要太多時間。表上的行數
Z2DataCore.parts.SourcingNotMappedParts
70 百萬行。當執行語句選擇
top 5
它需要太多時間超過15 minutes
。那麼如何讓它更快
選擇有問題的語句
SELECT top 5 GivenPartNumber_Non,vcompanyid into #GetSupplierAndOther FROM Z2DataCore.parts.SourcingNotMappedParts with(nolock) Where PriorityLevel in ('A3','A4') and vcompanyid is not null and sourcetypeid=484456 group by GivenPartNumber_Non,vcompanyid having count(distinct sourcetypeid)=2
我估計的執行計劃
https://www.brentozar.com/pastetheplan/?id=r1EPmqFx5
注意:我嘗試在不使用的情況下選擇上面的列,
select into
但仍然很慢。範例表腳本和索引
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, [RevisionId] [bigint] NULL, [ExtractionDate] [date] NULL, [Taxonomy] [nvarchar](250) NULL, [PartStatus] [nvarchar](50) NULL, [Datasheet] [nvarchar](2000) NULL, [ROHS] [nvarchar](250) NULL, [StockId] [int] NULL, [SourceUrl] [nvarchar](2000) NULL, [Description] [nvarchar](2000) NULL, [CreatedBy] [int] NULL, [ModifiedBy] [int] NULL, [CreatedDate] [datetime] NULL, [ModifiedDate] [datetime] NULL, [Comment] [nvarchar](2000) NULL, [Reason] [nvarchar](2000) NULL, [PartId] [int] NULL, [GroupID] [int] NULL, [PartStatusID] [int] NULL, [ManufactureStatus] [int] NULL, [EditStatus] [int] NULL, [FamilyID] [int] NULL, [LookupId] [int] NULL, [ValidationReasonId] [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, [PCPartStatus] [nvarchar](50) 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 ALTER TABLE [Parts].[SourcingNotMappedParts] ADD CONSTRAINT [DF_SourcingNotMappedParts_CreatedDate] DEFAULT (getdate()) FOR [CreatedDate] GO ALTER TABLE [Parts].[SourcingNotMappedParts] ADD CONSTRAINT [DF_SourcingNotMappedParts_ModifiedDate] DEFAULT (getdate()) FOR [ModifiedDate] GO ALTER TABLE [Parts].[SourcingNotMappedParts] ADD CONSTRAINT [PK_Parts.SourcingNotMappedParts] PRIMARY KEY CLUSTERED ( [SourcingNotMappedPartsID] 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 [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 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 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 CREATE NONCLUSTERED INDEX [IDX_SourcingNotMappedParts_VCompanyId] ON [Parts].[SourcingNotMappedParts] ( [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
您的問題與
TOP 5
. 你有一個GROUP BY
andHAVING
子句,這兩個子句都發生在SELECT TOP
.GROUP BY
在產生減少的結果集之前,可能必須觸及數千行。出於這個原因,這並不是說您將 5 行傳遞給 theGROUP BY
並期望它對它們採取行動。此外,您只提供了一個估計的計劃。它估計將讀取 522 行。但是如果沒有實際的執行計劃,就無法知道實際讀取了多少行。
估計的計劃還具有鍵查找,因為您沒有涵蓋查詢中引用的所有列的索引。鍵查找通常不好,因為它們對每一行執行一次。在這種情況下,估計您的索引掃描將返回 522 行。對於這 522 行中的每一行,都會有一個不同的 Key Lookup。查看您目前的索引,我會考慮添加此索引,以希望在沒有 Key Lookup 的情況下為您提供 Index Seek。
CREATE NONCLUSTERED INDEX [IX_NotMapped_VCompanyId_sourcetypeid] ON [Parts].[SourcingNotMappedParts] ( [VCompanyId], [sourcetypeid], [PriorityLevel] ) INCLUDE ( [GivenPartNumber_Non], [sourcetypeid] )
此外,在此之後,您應該能夠刪除此索引。
DROP INDEX IDX_SourcingNotMappedParts_VCompanyId]