slow running on update large table sql server

  • I work on sql server 2019

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

    estimated execution plan

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

    actual execution plan

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

    and update statment take 22 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='I1'   FROM Z2DataCore.parts.SourcingNotMappedParts s 
    inner join extractreports.dbo.SourcingNotMappedPartsIDI1 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 update please ?

     

     

     

  • Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • update s 
    set s.PriorityLevel='I1'
    FROM Z2DataCore.parts.SourcingNotMappedParts s
    WHERE (s.PriorityLevel <> 'I1' OR s.PriorityLevel IS NULL)
    AND EXISTS (select * from extractreports.dbo.SourcingNotMappedPartsIDI1 g
    where g.SourcingNotMappedPartsID=s.SourcingNotMappedPartsID)

    Does PriorityLevel  have to be NULLable?

    Removing " OR s.PriorityLevel IS NULL" will eliminate repeating scan of the index.

    Does it have to be nvarchar?

    If the longest status string is, say, 3 char long, and there are no non-Latin characters in there, than change it to a fixed length data type, CHAR(3) if follow my assumption.

    At least you won't need to rewrite the clustered index (meaning - the whole table) on every update.

    _____________
    Code for TallyGenerator

  • on top of what Sergiy said do have a look at the indexes as well - some can likely be removed as follows - and this will remove one of the updates index updates from that query

    the query on the plan is not the same you supplied above.

    update s set s.PriorityLevel='I2' FROM Z2DataCore.parts.SourcingNotMappedParts s

    inner join #alldistSuppliersourceid g on g.SourcingNotMappedPartsID=s.SourcingNotMappedPartsID

    do try and create a clustered index on this temp table

    create clustered index #alldistSuppliersourceid_ix1 on #alldistSuppliersourceid

    (SourcingNotMappedPartsID

    )

     

     

     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

    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

    First column(s)
    VCompanyId
    Common columns
    SourceTypeId
    PriorityLevel
    GivenPartNumber_Non

    columns in 1 index only
    CompanyID
    StockID

    drop index IX_SourcingNotMappedParts_VCompanyId and add the following columns to index IX_NotMapped_VCompanyId_sourcetypeid as include
    CompanyID
    StockID

    It is also likely that index IDX_Part_status_ID can be dropped as index IDX_MatchStatus_StatusID contains the same column as first column of that index


    test both including other queries that may be using either of the indexes mentioned above
  • i make what you say above and remove index not needed

    this is my estimated execution plan after remove indexes no needed

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

    and this is actual execution plan after remove indexes no needed

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

    so what i will do to more enhance and make update faster

  • this new plan does is not using the same query as the original actual plan you supplied - neither do the tables seem to be the same (record count is totally off)

    also this new one seems to have been executed onto a table that has far fewer indexes than those you supplied.

     

    please ensure you do supply accurate data and plans, as well as that you are executing the same query if you wish us to be able to help you.

  • ahmed_elbarbary.2010 wrote:

    i make what you say above and remove index not needed

    this is my estimated execution plan after remove indexes no needed

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

    and this is actual execution plan after remove indexes no needed

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

    so what i will do to more enhance and make update faster

     

    Did you try my query?

    What plan does it produce?

    _____________
    Code for TallyGenerator

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply