WHEN Add optional parameters on join query become very slow so How to solve slow performance?

Transact-SQL
,
1

I work on SQL server 2012 I face issue when add this statement it take 9 minutes to display 900 rows only

SELECT fmat.Value as PLID,c.CodeType,
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)
GROUP BY fmat.Value,c.CodeType

without add this statement below

AND (c.PLID is null OR fmat.Value=c.PLID)

query take 3 minute to display 900 rows and after add

AND (c.PLID is null OR fmat.Value=c.PLID)
it take 9 minutes

Are there are any way to make query above to be optional without take too much time

PLID IS optional if it have value then get his value and code type

if PLID not have value then select data based on code type

so why it take too much time

2

Does #TempPlAndCodeType have an indices?

3

one idea is to do ... AND (c.PLID is null OR fmat.Value=c.PLID) seperately ..

another idea ...
or break this down also
first AND (c.PLID is null seperately ..
then fmat.Value=c.PLID) seperately ..

SELECT
c.PLID as GetNulls
, fmat.Value as PLID
, c.CodeType
, 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)
GROUP BY fmat.Value,c.CodeType

select
* into #temp
from
#partsHasCodes
where
GetNulls is NULL

select * into #temp123 from #partsHasCodes
where fmat.Value=c.PLID

anorther thing i see is to change to
case when c.PLID is not null then fmat.Value=c.PLID

4

It might also be an idea to try Paul White's recursive method of getting distinct values:

Difficult without test data but maybe something like:

WITH BaseQuery
AS
(
	SELECT FMAT.[Value] AS PLID
		,C.CodeType
		,TR.PartID
	FROM Parts.TradeCodes TR
		JOIN Parts.Nop_Part PT
			ON TR.PartID = PT.PartID
		JOIN Parts.Nop_PartsFamilyAttribute FMAT
			ON  PT.PartFamilyID = FMAT.PartsFamilyID
				AND FMAT.[Key] = 20281007
		JOIN #TempPlAndCodeType C
			ON TR.CodeTypeId = C.CodeTypeID
				AND (C.PLID IS NULL OR FMAT.[Value] = C.PLID)
)
,RecursiveDistinct
AS
(
	SELECT TOP (1) PLID, CodeType, PartID
	FROM BaseQuery
	ORDER BY PLID, CodeType, PartID

	UNION ALL

    SELECT PLID, CodeType, PartID
    FROM
    (
        SELECT B.PLID, B.CodeType, B.PartID
			,ROW_NUMBER() OVER (ORDER BY B.PLID, B.CodeType, B.PartID) AS rn
		FROM BaseQuery B
			JOIN RecursiveDistinct R
				ON B.PLID >= R.PLID
					AND B.CodeType >= R.CodeType
					AND B.PartID > R.PartID
    ) AS D
    WHERE rn = 1
)
SELECT PLID, CodeType
	,COUNT(1) AS [#partsHasCodes]
FROM RecursiveDistinct
GROUP BY PLID, CodeType
OPTION (MAXRECURSION 0);
5

Or maybe:

WITH BaseQuery
AS
(
	SELECT FMAT.[Value] AS PLID
		,C.CodeType
		,TR.PartID
		,ROW_NUMBER() OVER (PARTITION BY FMAT.[Value], C.CodeType ORDER BY PartID) AS rn
	FROM Parts.TradeCodes TR
		JOIN Parts.Nop_Part PT
			ON TR.PartID = PT.PartID
		JOIN Parts.Nop_PartsFamilyAttribute FMAT
			ON  PT.PartFamilyID = FMAT.PartsFamilyID
				AND FMAT.[Key] = 20281007
		JOIN #TempPlAndCodeType C
			ON TR.CodeTypeId = C.CodeTypeID
				AND (C.PLID IS NULL OR FMAT.[Value] = C.PLID)
)
,RecursiveDistinct
AS
(
	SELECT PLID, CodeType, PartID
	FROM BaseQuery
	WHERE rn = 1

	UNION ALL

    SELECT PLID, CodeType, PartID
    FROM
    (
        SELECT B.PLID, B.CodeType, B.PartID
			,ROW_NUMBER() OVER (PARTITION BY B.PLID, B.CodeType ORDER BY B.PartID) AS rn
		FROM BaseQuery B
			JOIN RecursiveDistinct R
				ON B.PLID = R.PLID
					AND B.CodeType = R.CodeType
					AND B.PartID > R.PartID
    ) AS D
    WHERE rn = 1
)
SELECT PLID, CodeType
	,COUNT(1) AS [#partsHasCodes]
FROM RecursiveDistinct
GROUP BY PLID, CodeType
OPTION (MAXRECURSION 0);
6

thanks