mslid 上的非集群索引查找速度很慢

我在 SQL Server 中遇到了一個問題，即非集群索引查找性能不佳。

下面是實際的執行計劃 https://www.brentozar.com/pastetheplan/?id=Sk3-4JGAK

如何提高性能？

下面是表格定義

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,
    [Revision_Id] [int] NULL,
    [BaseMaterialID] [int] NULL,
    [MSLID] [int] NULL,
    [MSLSource_Revision_id] [int] NULL,
    [MaximumReflowTemperatureID] [int] NULL,
    [ReflowTemperatureSource_Revision_Id] [int] NULL,
    [MaximumWaveTemperatureID] [int] NULL,
    [WaveTemperatureSource_Revision_ID] [int] NULL,
    [ReflowSolderTimeID] [int] NULL,
    [WaveSolderTimeID] [int] NULL,
    [NumberOfReflowCycleID] [int] NULL,
    [LeadFinishPlatingID] [int] NULL,
    [Comment] [varchar](100) NULL,
    [LeadfinishSourceTypeID] [int] NULL,
    [MSlSourceTypeID] [int] NULL,
    [ReflowTemperatureSourceTypeID] [int] NULL,
    [BasedOnID] [int] NULL,
    [LeadFreeProcessCapabilityID] [int] NULL,
    [BaseMaterialRevisionID] [int] NULL,
    [BaseMaterialSourceTypeID] [int] NULL,
    [UnderplatingRevisionID] [int] NULL,
    [UnderplatingSourceTypeID] [int] NULL,
    [ShelfLifeCondition] [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

index seek used as below

CREATE NONCLUSTERED INDEX [IDX_MSLID] ON [Parts].[ManufacturingData]
(
    [MSLID] 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 [Customer]
GO

USE [Z2DataCore]
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]
GO

除了其他正確答案之外，您還可以嘗試另一種方法來提高性能，這可能是創建索引視圖，如下所示：

CREATE VIEW parts.manufacturingdata_Indexed
WITH SCHEMABINDING AS
select partid,mslid from parts.manufacturingdata m with(nolock)
where mslid is  not null

創建上述視圖後，創建索引如下：

CREATE UNIQUE CLUSTERED INDEX IX_manufacturingdata
   ON parts.manufacturingdata_Indexed
    (partid,mslid);

創建上述視圖和索引後，您可以將查詢重寫為：

insert into ExtractReports.dbo.manufactureparts
(select * from parts.manufacturingdata_Indexed)

我將查詢更改為insert into而不是select * into程式碼的可重用性。在 的情況下select into，您總是需要刪除基礎表。

請讓我們知道這是否有幫助。

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