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

Transact-SQL
,
1

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

2

did you check wait types?
any blocking?

3

what are the indexes on Parts.Nop_PartsFamilyAttribute? That's estimating at 3.5 m rows. What is the actual plan?

4

thank you for reply
I create non-cluster index on key on table part family attribute but still take too much time same time
10 minutes
and this is script for table

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](max) 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
5

im sure take too much time issue done when join to table part family attribute
but really what i do
I create non cluster index on key
but still take too much time about as previous time
so what i do to solve this issue please

6

few things to try:

  1. why are Value and PLID nvarchar(max)? That could also be an issue since they are used in the joins
  2. what happens when you remove OR and just use fmat.Value=c.PLID? Just want to see if this OR is causing the issue.
  3. then after you tried 1, how many records in Parts.Nop_PartsFamilyAttribute have fmat.[Key]=20281007? Wondering if reversing that key