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

  • 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

    Wednesday, August 5, 2020 1:23 AM

Answers

  • 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.




    • Edited by Echo Liuz Thursday, August 6, 2020 7:30 AM
    • Marked as answer by engahmedbarbary Saturday, August 8, 2020 12:00 AM
    Thursday, August 6, 2020 7:13 AM

All replies

  • 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.








    • Edited by Echo Liuz Wednesday, August 5, 2020 3:15 AM
    Wednesday, August 5, 2020 2:35 AM
  • 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, esquel@sommarskog.se

    Wednesday, August 5, 2020 10:36 AM
  • 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

    Wednesday, August 5, 2020 12:27 PM
  • 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

    Wednesday, August 5, 2020 12:28 PM
  • 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)
    

    • Proposed as answer by Naomi N Wednesday, August 5, 2020 5:31 PM
    Wednesday, August 5, 2020 5:14 PM
  • this is execution plan so what I do

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

    Wednesday, August 5, 2020 11:45 PM
  • still after add cluster index it very slow

    are there are any thing else can I do 

    my execution plan above

    Thursday, August 6, 2020 12:52 AM
  • 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.




    • Edited by Echo Liuz Thursday, August 6, 2020 2:31 AM
    Thursday, August 6, 2020 2:28 AM
  • 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

    Thursday, August 6, 2020 4:35 AM
  • 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

    Thursday, August 6, 2020 4:38 AM
  • 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.




    • Edited by Echo Liuz Thursday, August 6, 2020 7:30 AM
    • Marked as answer by engahmedbarbary Saturday, August 8, 2020 12:00 AM
    Thursday, August 6, 2020 7:13 AM