Select statement take 20 minute to return 1000 rows only why and how to minimize time?

Question

I work on SQL server 2012 Small query take too much time to return small amount of data
bout 1000 rows .

SELECT fmat.Value as PLID,c.CodeTypeId,
COUNT(DISTINCT tr.PartID) [#partsHasCodes]
into #partsHasCodes
FROM Parts.TradeCodes tr WITH(NOLOCK)
INNER JOIN Parts.Nop_Part pt WITH(NOLOCK) ON pt.PartID = tr.PartID
INNER JOIN Parts.Nop_PartsFamilyAttribute fmat WITH(NOLOCK) ON fmat.PartFamilyID=pt.PartsFamilyID AND fmat.[Key]=20281007
inner join #TempPlAndCodeType c on (c.CodeTypeId=tr.CodeTypeID) --AND (c.PLID is null OR fmat.Value=c.PLID)
WHERE (c.PLID is null OR fmat.Value=c.PLID)
GROUP BY fmat.Value,c.CodeTypeId

so How to solve issue
execution plan is
https://www.brentozar.com/pastetheplan/?id=ryCXs5jQv

what i try
create non cluster index on key field on table part family attribute but nothing changed
still take 20 minutes to run query above

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 NOT 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 [PRIMARY],
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 [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

Answer 1

Hi @ahmed salah ,

Has your problem been solved? if it has been solved,please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.
Thank you for understanding!

Best Regards
Echo

Answer 2

Hi @ahmed salah ,

Has your problem been solved? if it has been solved,please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.
Thank you for understanding!

Best Regards
Echo