when update large table it take too much time to update



  • I work on sql server 2019

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

    https://www.brentozar.com/pastetheplan/?id=ry99b8Re9

    https://www.brentozar.com/pastetheplan/?id=r11YYOy-q

    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 NULL,
    [GivenManufacture] nvarchar NULL,
    [CompanyId] [int] NULL,
    [SourceTypeID] [int] NULL,
    [PartStatus] nvarchar NULL,
    [StockId] [int] NULL,
    [SourceUrl] nvarchar NULL,
    [PartId] [int] NULL,
    [GroupID] [int] NULL,
    [PartStatusID] [int] NULL,
    [MatchStatus] nvarchar NULL,
    [GivenPartNumber_Non] nvarchar NULL,
    [GivenManufacturer_Non] nvarchar NULL,
    [signatureID] [int] NULL,
    [VCompanyId] [int] NULL,
    [PriorityLevel] nvarchar 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 .



  • 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.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2