I work with SQL Server and face an issue that a query is very slow.
INSERT INTO [ExtractReports].[dbo].[TradeCodesDelete] (PartID, Code, CodeTypeID, SourceTypeID, RevisionID, ZPLID, PartLevel, CreatedDate, FlagDelete) ----select count(1) from [ExtractReports].[dbo].[TradeCodesDelete] ---update [ExtractReports].[dbo].[TradeCodesDelete] set FlagDelete=2 where FlagDelete=1 SELECT DISTINCT FT.PartId, TN.Code, FT.CodeTypeID, FT.SourceTypeID, FT.RevisionID, fm.[Value], FT.PartLevel, GETDATE(), 1 FROM Parts.Nop_Part pt WITH (NOLOCK) INNER JOIN Parts.Nop_PartsFamilyAttribute fm WITH (NOLOCK) ON pt.PartsFamilyID = fm.PartFamilyID AND fm.[Key] = 20281007 INNER JOIN ExtractReports.dbo.TPls pl WITH (NOLOCK) ON pl.ZPLID = fm.Value INNER JOIN Parts.TradeCodes FT WITH (NOLOCK) ON pt.PartID = FT.PartID AND FT.PartLevel = 0 INNER JOIN ExtractReports.dbo.TPLNewData TN WITH (NOLOCK) ON TN.PartID = FT.PartID AND TN.CodeTypeID = FT.CodeTypeID LEFT JOIN [ExtractReports].[dbo].[TradeCodesDelete] d ON d.partid = FT.partid AND d.codetypeid = FT.codetypeid AND d.partlevel = 0 WHERE TN.Code <> FT.Code AND FT.MappingDoneFlag = 0 AND d.partid IS NULL;
I check the execution plan for a reason of slow query performance.
This is my execution plan: https://www.brentozar.com/pastetheplan/?id=SJCzRrmht
- Index seek is high at 57% - how to reduce it?
- Hash match inner join is high at 40% - how to reduce it?
- Compute scalar is at 1% - how to reduce it?
Can anyone help me?
Table script as below :
CREATE TABLE [Parts].[Nop_PartsFamilyAttribute] ( [PartFamilyAttributeID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL, [PartFamilyID] [int] NOT NULL, [Key] [int] NOT NULL, [Value] [nvarchar](2200) NULL, [CreatedDate] [datetime] NULL, [CreatedBy] [int] NULL, [ModifiedDate] [datetime] NULL, [Modifiedby] [int] NULL, [DeletedDate] [datetime] NULL, [DeletedBy] [int] NULL, CONSTRAINT [PK_Nop_PartsFamilyAttribute30] PRIMARY KEY CLUSTERED ([PartFamilyAttributeID] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Customer], CONSTRAINT [UK_PartFamilyID_Key30] UNIQUE NONCLUSTERED ([PartFamilyID] ASC, [Key] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Customer] ) ON [Customer]
what I try :
create index pf_idx on Parts.Nop_PartsFamilyAttribute([Key]) include ([Value])
but nothing changed still index seek 57
and hash match 40
and compute scalar cost 1
so what I do to solve issue of Execution plan ?