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

Question

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

Answer 1

Hi engahmedbarbary,

Query take 3 minute to display 900 rows ,it is very slow.Do you have any index?If not,you can try to set the index first.After add the statement ,query becomes slow ,please also provide your execution plan  so that I can find the reason for the slow execution.

You can try to restart sql server, maybe it can solve the problem temporarily.But to fundamentally solve the problem,  still need to optimize the statement.

There is a similar issue, which may help you:fixing a slow running sql query

Best Regards 
Echo   

---

""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
For more information, please refer to the sticky post.

Answer 2

Conditions with OR is not the optimizer's best game. It often helps to rewrite the query with UNION ALL.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 3

try this 

SELECT Value as PLID, CodeType, COUNT(DISTINCT PartID) [#partsHasCodes]
FROM (
 SELECT fmat.Value as PLID,c.CodeType,  tr.PartID
 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)
 UNION ALL
 SELECT fmat.Value as PLID,c.CodeType,  tr.PartID
 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)
) a
GROUP BY Value,CodeType

get the execution plan, then build good indexes

---

jchang

Answer 4

Hi

(select .CodeTypeId,CodeType,isnull(PLID,0) #TempPlAndCodeType) tempPandcodetype

Join from this table and remove isnull from inner join 

This may reduce your time

Thanks and regards

Answer 5

You are joining to a temp table, which I assume does not have an index and is huge.

Create an index on the temp table after you create it:

create clustered index idx on #TempPlAndCodeType (CodeTypeId, PLID)

Answer 6

this is execution plan so what I do

https://www.brentozar.com/pastetheplan/?id=HJXDEp_bv

Answer 7

still after add cluster index it very slow

are there are any thing else can I do 

my execution plan above

Answer 8

Hi engahmedbarbary,

Your execution plan shows that you have established a non-clustered index, 
but index scan means that the execution plan does not use index lookup.
In other words, your index is not appropriate.You can try to index on other columns.

Experts have given suggestions for optimizing statement, you can try them all.

Besides,please  provide an execution plan with  added 

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

Please also share us  your table structure (CREATE TABLE …) and some sample data(INSERT INTO …). So that we’ll get a right direction and make some test.

Best Regards   
Echo

---

""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
For more information, please refer to the sticky post.

Answer 9

thank you for reply

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

exactly now i dont use it

i depend on codetype

Answer 10

from image above

how to create index on columns exist on image 

can you tell me how to create index on it

to test it

Answer 11

Hi engahmedbarbary,

create index ：

CREATE [CLUSTERED | NONCLUSTERRED]
INDEX index_name ON table_name (column_name)

When creating a table, the primary key column will create a clustered index by default, 
and a table can only have one clustered index, so please check whether the table has a clustered index.
If there is already, then you can only create a non-clustered index, it can have more than one.

Best Regards 
Echo   

---

""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
For more information, please refer to the sticky post.