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

我在 sql server 2019 上工作，我在選擇top 5 行時遇到問題，它需要太多時間。

表上的行數Z2DataCore.parts.SourcingNotMappedParts70 百萬行。

當執行語句選擇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 BYandHAVING子句，這兩個子句都發生在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]

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