對於少量更新的行，如何提高更新速度？

我正在使用 SQL Server 2019 並面臨一個問題，即在進行更新時，僅 162 行需要 20 秒。

• 預計執行計劃
• 實際執行計劃

Update FT SET
   ft.ValueName=avo.name
FROM #package FT 
inner join parts.Nop_PackageAttribute PA with(nolock)
   on PA.PackageID=ft.PackageID
       and PA.[Key]=FT.ZfeatureId 
inner join Nop_AcceptedValuesOption AVO with(nolock)
   ON convert(varchar(20),AVO.AcceptedValuesOptionID)=PA.Value 
where FT.AcceptedValueID is not null

腳本範例

create table #package
(
id int PRIMARY KEY IDENTITY(1,1),
ZfeatureId INT NULL,
AcceptedValueID INT NULL,
PackageID INT NULL,
ValueName NVARCHAR(2000) default ''
)

我在#package 表上的索引

create nonclustered index IDX_PackageID on #package(PackageID) include (ZfeatureId,AcceptedValueID , ValueName)
create index acceptedvaluesidpackage_idx on #package(AcceptedValueID)

包屬性表

ALTER TABLE [Parts].[Nop_PackageAttribute] ADD  CONSTRAINT [PK_Nop_PackageAttribute] PRIMARY KEY CLUSTERED 
(
   [PackageAttributeID] 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 NONCLUSTERED INDEX [_dta_index_Nop_PackageAttribute_8_578153155__K2_K1_K3_4] ON [Parts].[Nop_PackageAttribute]
(
   [PackageID] ASC,
   [PackageAttributeID] ASC,
   [Key] ASC
)
INCLUDE (   [Value]) 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]

CREATE NONCLUSTERED INDEX [IDX_Key] ON [Parts].[Nop_PackageAttribute]
(
   [Key] 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]


CREATE NONCLUSTERED INDEX [IDX_PakageID] ON [Parts].[Nop_PackageAttribute]
(
   [PackageID] 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
CREATE NONCLUSTERED INDEX [IX_Nop_PackageAttribute_Key] ON [Parts].[Nop_PackageAttribute]
(
   [Key] ASC
)
INCLUDE (   [PackageID],
   [Value]) 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]


CREATE TABLE [Parts].[Nop_PackageAttribute](
   [PackageAttributeID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
   [PackageID] [int] NOT NULL,
   [Key] [int] NOT NULL,
   [Value] [nvarchar](max) NOT NULL,
   [CreatedDate] [datetime] NULL,
   [CreatedBy] [int] NULL,
   [ModifiedDate] [datetime] NULL,
   [ModifiedBy] [int] NULL,
   [DeletedDate] [datetime] NULL,
   [DeletedBy] [int] NULL,
CONSTRAINT [PK_Nop_PackageAttribute] PRIMARY KEY CLUSTERED 
(
   [PackageAttributeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Customer]
) ON [Customer] TEXTIMAGE_ON [PRIMARY]

Nop_AcceptedValuesOption 表

ALTER TABLE [dbo].[Nop_AcceptedValuesOption] ADD  CONSTRAINT [PK_Nop_AcceptedValuesOption] PRIMARY KEY CLUSTERED 
(
   [AcceptedValuesOptionID] 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 NONCLUSTERED COLUMNSTORE INDEX [_dta_index_Nop_AcceptedValuesOption_5_1669580986__col__] ON [dbo].[Nop_AcceptedValuesOption]
(
   [AcceptedValuesOptionID],
   [AcceptedValuesID],
   [Name],
   [DisplayOrder],
   [Description],
   [CreatedDate],
   [CreatedBy],
   [ModifiedDate],
   [ModifiedBy],
   [DeletedDate],
   [DeletedBy],
   [Is_Split],
   [AcceptedValuesOption_Value],
   [AcceptedValuesOption_Unit]
)WITH (DROP_EXISTING = OFF) ON [Customer]

CREATE NONCLUSTERED INDEX [_dta_index_Nop_AcceptedValuesOption_8_1074154922__K1_3] ON [dbo].[Nop_AcceptedValuesOption]
(
   [AcceptedValuesOptionID] ASC
)
INCLUDE (   [Name]) 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
CREATE NONCLUSTERED INDEX [_dta_index_Nop_AcceptedValuesOption_8_1074154922__K2_K4_1_3] ON [dbo].[Nop_AcceptedValuesOption]
(
   [AcceptedValuesID] ASC,
   [DisplayOrder] ASC
)
INCLUDE (   [AcceptedValuesOptionID],
   [Name]) 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
CREATE NONCLUSTERED INDEX [NonClusteredIndex-20160824-070515] ON [dbo].[Nop_AcceptedValuesOption]
(
   [AcceptedValuesID] 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
CREATE NONCLUSTERED INDEX [NonClusteredIndex-20160829-015901] ON [dbo].[Nop_AcceptedValuesOption]
(
   [Name] 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

CREATE TABLE [dbo].[Nop_AcceptedValuesOption](
   [AcceptedValuesOptionID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
   [AcceptedValuesID] [int] NOT NULL,
   [Name] [nvarchar](500) NOT NULL,
   [DisplayOrder] [int] NOT NULL,
   [Description] [varchar](250) NULL,
   [CreatedDate] [datetime] NULL,
   [CreatedBy] [int] NULL,
   [ModifiedDate] [datetime] NULL,
   [ModifiedBy] [int] NULL,
   [DeletedDate] [datetime] NULL,
   [DeletedBy] [int] NULL,
   [Is_Split] [int] NULL,
   [AcceptedValuesOption_Value] [float] NULL,
   [AcceptedValuesOption_Unit] [nvarchar](20) NULL,
   [IsDeleted] [bit] NULL,
CONSTRAINT [PK_Nop_AcceptedValuesOption] PRIMARY KEY CLUSTERED 
(
   [AcceptedValuesOptionID] 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

ALTER TABLE [dbo].[Nop_AcceptedValuesOption] ADD  CONSTRAINT [DF_IsDeleted]  DEFAULT ((0)) FOR [IsDeleted]
GO

ALTER TABLE [dbo].[Nop_AcceptedValuesOption] CHECK CONSTRAINT [FK_Nop_AcceptedValuesOption_Nop_AcceptedValues]
GO

那麼，如何增強更新語句更快呢？

添加到布倫丹的答案，交換這個轉換

inner join  Nop_AcceptedValuesOption AVO with(nolock) 
ON convert(varchar(20),AVO.AcceptedValuesOptionID)=PA.Value 

到

inner join  Nop_AcceptedValuesOption AVO 
ON AVO.AcceptedValuesOptionID = try_cast(PA.Value as int)

並且您應該能夠用 162 個索引查找替換非聚集列儲存掃描。如果您沒有獲得嵌套循環計劃，請嘗試inner loop join.

您應該避免在WHERE子句中圍繞列包裝函式。通過環繞 a CONVERT，AcceptedValuesOptionIDSQL Server 必須在表中的每一行上執行該函式，然後才能知道值是否等於PA.Value。

話雖如此，這很可能是您WHERE條款中導致您的緩慢的部分。

convert(varchar(20),AVO.AcceptedValuesOptionID)=PA.Value

我看到發生了兩件事。

1. 正在使用非聚集索引掃描檢索此表中的數據。
2. 然後執行雜湊匹配，然後將其與其他兩個表的結果結合起來。

看看是否有另一種方法可以連接到該表，其中連接兩側的列是相同的數據類型。

另一種選擇可能是更改上的數據類型AVO.AcceptedValuesOptionID。但是，更改現有表中的數據類型是有風險的。您需要確保在此過程中不會破壞其他查詢。

此外，如果您知道始終PA.value可以包含 INT 數據的特定場景，您可以考慮創建一個臨時表並將這些行子集插入到臨時表中。您將使用該列作為 INT 數據類型創建此臨時表，然後在原始聯接中使用臨時表，而不是基表。

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