March 3, 2022 at 11:44 pm
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 ?
March 5, 2022 at 12:10 am
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
March 5, 2022 at 1:46 am
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
March 5, 2022 at 9:13 am
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
March 5, 2022 at 3:58 pm
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
March 5, 2022 at 5:19 pm
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.
March 10, 2022 at 7:00 am
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