when update large table it take too much time to update

By Posted on

Question :

I work on sql server 2019

when update table Z2DataCore.parts.SourcingNotMappedParts i found slow and long time when update

estimated execution plan

actual execution plan

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 :

check wait type status

so are this related to slow or not related .

Answer :

Your updated actual execution plan is also indicating you have no indexes on your extractreports.dbo.alldistSuppliersourceid table because it is currently using a Table Scan operation against it.

It should at the minimum have a clustered index. If that clustered index makes sense to be on the field your query is currently joining on SourcingNotMappedPartsID (depending on how often you query by that column) then you could create it like so:

USE extractreports;

CREATE CLUSTERED INDEX IX_alldistSuppliersourceid_ ON dbo.alldistSuppliersourceid (SourcingNotMappedPartsID);

If SourcingNotMappedPartsID is unique in extractreports.dbo.alldistSuppliersourceid then you can also specify the UNIQUE keyword after theCREATE keyword.

Otherwise if another field makes sense to be the clustered index on extractreports.dbo.alldistSuppliersourceid, then use the above script, substituting out that column, to create the clustered index and you can create an additional nonclustered index on it instead like so:

USE extractreports;

CREATE NONCLUSTERED INDEX IX_alldistSuppliersourceid_ ON dbo.alldistSuppliersourceid (SourcingNotMappedPartsID);

Again, using the UNIQUE keyword if it’s applicable as well.

That one index should at least help your performance to a degree.

Leave a Reply

Your email address will not be published. Required fields are marked *