I work on sql server 2019
when update table Z2DataCore.parts.SourcingNotMappedParts
i found slow and long time when update
Update statement takes 26.5 minute to finish update for count of rows 692488 so How to make Faster .
statment generate slow process as below :
UPDATE s
SET s.PriorityLevel = 'I2'
FROM Z2DataCore.parts.SourcingNotMappedParts s
INNER JOIN extractreports.dbo.alldistSuppliersourceid g
ON g.SourcingNotMappedPartsID = s.SourcingNotMappedPartsID
count rows affected for updated will be 692488
table SourcingNotMappedParts that i need to update it have 71 milion rows as general .
table extractreports.dbo.SourcingNotMappedPartsIDI1
i will get data from it to update is 692488 .
table extractreports.dbo.SourcingNotMappedPartsIDI1
have only column SourcingNotMappedPartsID
with int datatype
table extractreports.dbo.SourcingNotMappedPartsIDI1
have only one index as below
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
so how to solve issue of slow please?
i checked updated status i found wait type as below :
so are this related to slow or not related .
extractreports.dbo.SourcingNotMappedPartsIDI1
with a temp table#alldistSuppliersourceid
which certainly would result in a different execution plan, given your DDL scripts show an applicable index exists onextractreports.dbo.SourcingNotMappedPartsIDI1
but that same index does not exist on your temp table. Please either post the correct actual execution plan for when you use theextractreports.dbo.SourcingNotMappedPartsIDI1
table or let us know if you want further help with optimizing the query for when you use a temp table instead.