How to reduce high cost index seek and hash match inner join when analysis execution plan issue?

Transact-SQL
, ,
1

I work on sql server 2014 I have issue on my execution plan below

index seek is high 57 what this mean and how to solve it

and how mean hash match inner join 40 AND HOW TO REDUCE
also compute scalar 1 so what this mean also

can any one 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]

​

2

I won't call myself an expert but when I read the plan from right to left the problem is in the fm and pl part. The fm.[Value] isn't an integer right? I try to get a better plan by first selecting that part and continue after that select by creating an CTE.

As you didn't provide any table creation or something I cannot test if this is correct but you can try to see if it's a better plan. I hope you'll get the idea :slight_smile:

My next step after this would be to eliminate this TN.Code <> FT.Code by using NOT EXISTS.

WITH FilterPart AS
(
SELECT DISTINCT Pt.PartID, fm.[value]
FROM Parts.Nop_Part pt with(nolock)
INNER JOIN Parts.Nop_PartsFamilyAttribute fm with(nolock)
ON pt.PartsFamilyID=fm.PartFamilyID
INNER JOIN ExtractReports.dbo.TPls pl with(nolock) ON pl.ZPLID=CONVERT(INT,fm.[Value])
WHERE fm.[Key]=20281007
)
SELECT DISTINCT
FT.PartId,
TN.Code,
FT.CodeTypeID,
FT.SourceTypeID,
FT.RevisionID,
fp.[Value],
FT.PartLevel,
getdate(),
1
FROM
FilterPart fp
INNER JOIN Parts.TradeCodes FT
ON fp.PartID=FT.PartID AND FT.PartLevel=0 AND FT.MappingDoneFlag=0
INNER JOIN ExtractReports.dbo.TPLNewData TN
ON TN.PartID = FT.PartID AND TN.CodeTypeID = FT.CodeTypeID
LEFT OUTER 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 d.partid IS NULL

1 Like
3

To add to Roger's observations, the plan indicates 4 million rows from the Parts.Nop_PartsFamilyAttribute table. The only way to avoid a hash match is to refine the WHERE clause to reduce the rows needed from that table. It's already using an index seek to read it, but the total row count is too high to make other JOIN types practical.

You may also try updating the statistics on all the tables in the query, that may produce a better plan.

1 Like
4

I suggest adding column Value as a second key to the index:
[Parts].[Nop_PartsFamilyAttribute].[_dta_index_Nop_PartsFamilyAttribute_30_7_531753943__K3_2_4]

So the index would then be ( key, value ) rather than just ( key ). Hopefully that will allow SQL to use a MERGE join instead.

The only time you might not want to do that is if the value changes fairly frequently.

Other than that, would need to see full DDL, including all indexes, for all tables to make other recommendations.

1 Like
5

how to create key and value index
it is very big
can you please tell me how to create index key and value

6 
CREATE UNIQUE NONCLUSTERED INDEX [Nop_PartsFamilyAttribute__IX_Key_Value] 
    ON dbo.Nop_PartsFamilyAttribute ( Key, Value, PartFamilyAttributeID ) 
    WITH ( FILLFACTOR = 95 ) ON [Customer];