question

ahmedsalah-1628 avatar image
0 Votes"
ahmedsalah-1628 asked ErlandSommarskog answered

How to enhance update table SourcingNotMappedParts to be faster ?

i work on sql server 2019 when update table [Parts].[SourcingNotMappedParts]
update still very slow
so how to enhance it

update statment i need to enhance it as below

 update s set s.PriorityLevel='I1'   FROM Z2DataCore.parts.SourcingNotMappedParts s 
 inner join extractreports.dbo.SourcingNotMappedPartsIDI1 g on g.SourcingNotMappedPartsID=s.SourcingNotMappedPartsID

estimated execution plan

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

actual execution plan

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

tables i create with indexes



     CREATE TABLE [dbo].[SourcingNotMappedPartsIDI1](
     [SourcingNotMappedPartsID] [int] NOT NULL
 ) ON [PRIMARY]
    
 GO
 /****** Object:  Index [SourcingNotMappedPartsIDI1_IDX]    Script Date: 3/5/2022 8:02:52 AM ******/
 CREATE UNIQUE CLUSTERED INDEX [SourcingNotMappedPartsIDI1_IDX] ON [dbo].[SourcingNotMappedPartsIDI1]
 (
     [SourcingNotMappedPartsID] ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 GO

 CREATE TABLE [Parts].[SourcingNotMappedParts](
   [SourcingNotMappedPartsID] [int] IDENTITY(1,1) NOT NULL,
   [GivenManufacture] [nvarchar](200) NULL,
   [CompanyId] [int] NULL,
   [SourceTypeID] [int] NULL,
   [GivenPartNumber_Non] [nvarchar](200) NULL,
   [VCompanyId] [int] NULL,
   [PriorityLevel] [nvarchar](10) 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 [IX_NotMapped_NonalphaPartCompany]    Script Date: 3/5/2022 7:40:36 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_SourceType]    Script Date: 3/5/2022 7:40:36 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



so how to make update faster



sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

According to the actual execution plan, the update operation took 5.7 seconds, which for 162000 is not disastrous. I can agree,. though, that "fantastic" is not the word I would use.

You are updating one table with the full input of another. That type of queries are difficult to optimize as such. However, I would write the query this way:

UPDATE s 
SET       s.PriorityLevel='I1'   
FROM  Z2DataCore.parts.SourcingNotMappedParts s 
WHERE s.PriorityLevel <> 'I1'   
  AND  EXISTS (SELECT *
              FROM extractreports.dbo.SourcingNotMappedPartsIDI1 g 
              WHERE g.SourcingNotMappedPartsID  = s.SourcingNotMappedPartsID)

I have replaced the join with an EXISTS subquery. But this is more an esthetic thing; I would not expect much difference in the plan or performance.

I have also added the condition s.PriorityLevel <> 'I1'. This reduces the number of rows that are updated, and this can help if you are running the above frequently.

The only other measure I can see these to make this run faster is to beef up your hardware, but the least with regards to the I/O subsystem. What sort of machine do you have?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.