-2

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 .

2
  • ...Upon further inspection it seems you replaced your 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 on extractreports.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 the extractreports.dbo.SourcingNotMappedPartsIDI1 table or let us know if you want further help with optimizing the query for when you use a temp table instead.
    – J.D.
    Mar 4 at 0:30
  • yes you are correct i try to use temp to make it quickly but still take 22 minute to update 500 k so are there are any solution to enhance that please Mar 4 at 9:56

1 Answer 1

2

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.