SQL query but bad performance

  • Hello,

    I have the below query working properly by but very slow, the sub-query is the cause of slow but I don't what is the best way to replace it.

    SELECT TblStyle.StyleCode,TblColor.Ename + '('+TblColor.Code+')' Color,

    ISNULL (TblQCDecisionStatus.Ename, 'Need Sample') Status,TblQcbrandManagerColorDecision.CreationDate,
    TblQcbrandManagerColorDecision.Comment,
    supplier.Ename Supplier,
    SUM( TblQCPackingList.Quantity) TotalQuantity, --Sum of all sizes quantity of the same color
    CAST(TblQCInspectionRequest.Iserial AS NVARCHAR(20)) QCReport

    FROM TblQCInspectionRequest
    LEFT OUTER JOIN TblQcbrandManagerColorDecision ON TblQcbrandManagerColorDecision.TblQCInspectionRequest = TblQCInspectionRequest.Iserial
    LEFT OUTER JOIN TblQCDecisionStatus ON TblQCDecisionStatus.Iserial = TblQcbrandManagerColorDecision.TblQCDecisionStatus
    LEFT OUTER JOIN TblQCNeedSample ON TblQCNeedSample.TblQCInspectionRequest = TblQCInspectionRequest.Iserial

    INNER JOIN TblStyle ON tblstyle.Iserial = TblQCInspectionRequest.TblStyle
    INNER JOIN TblColor ON TblColor.Iserial = TblQcbrandManagerColorDecision.TblColor
    OR (TblColor.Iserial = TblQCNeedSample.TblColor
    AND TblColor.Iserial NOT IN (SELECT tblcolor FROM TblQcbrandManagerColorDecision WHERE TblQcbrandManagerColorDecision.TblQCInspectionRequest = TblQCNeedSample.TblQCInspectionRequest))

    INNER JOIN TblQCPackingList ON TblQCPackingList.TblQCInspectionRequest = TblQCInspectionRequest.Iserial
    AND ((TblQCPackingList.TblQCRequestColors=TblQcbrandManagerColorDecision.TblColor AND TblQCPackingList.TblQCInspectionRequest = TblQcbrandManagerColorDecision.TblQCInspectionRequest)
    OR (TblQCPackingList.TblQCRequestColors = TblQCNeedSample.TblColor AND TblQCPackingList.TblQCInspectionRequest = TblQCNeedSample.TblQCInspectionRequest))

    INNER JOIN TblContractHeader ON TblQCInspectionRequest.ContractCode = TblContractHeader.Code
    INNER JOIN retaildb.ccnew.dbo.TblSupplier supplier ON supplier.Iserial = TblContractHeader.SupplierIserial
    WHERE
    (( TblQcbrandManagerColorDecision.CreationDate >= dateadd(dd, 0, datediff(dd, 0, getdate()))
    and
    TblQcbrandManagerColorDecision.CreationDate < dateadd(dd, 0, datediff(dd, 0, getdate()) + 1)
    )
    OR TblQCNeedSample.CreationDate >= dateadd(dd, 0, datediff(dd, 0, getdate()))
    and
    TblQCNeedSample.CreationDate < dateadd(dd, 0, datediff(dd, 0, getdate()) + 1))
    -- AND TblQCInspectionRequest.Iserial = 2531

    GROUP BY TblColor.Iserial,
    TblStyle.StyleCode,TblColor.Ename,TblQCDecisionStatus.Ename,TblColor.Code ,TblQcbrandManagerColorDecision.CreationDate,
    TblQcbrandManagerColorDecision.Comment,
    supplier.Ename, TblQCInspectionRequest.Iserial

     

     

  • Using short aliases would make this easier to read.

    To make reasonable suggestions about indexes, stats etc we would really need to see the ACTUAL query plan.

    https://www.brentozar.com/pastetheplan/

    At a guess, NOT EXISTS is quite often better than NOT IN:

        INNER JOIN TblColor
    ON TblColor.Iserial = TblQcbrandManagerColorDecision.TblColor
    OR (
    TblColor.Iserial = TblQCNeedSample.TblColor
    AND NOT EXISTS
    (
    SELECT 1
    FROM TblQcbrandManagerColorDecision CD1
    WHERE CD1.TblQCInspectionRequest = TblQCNeedSample.TblQCInspectionRequest
    AND CD1.tblcolor = TblColor.Iserial
    )
    )

  • I agree with Ken that NOT EXISTS should run faster than NOT IN.

    Imagine you have a room full of people wearing various colour shirts, and you want to see if your shirt is a unique colour. A NOT IN requires you to check the colour of all shirts in the room before deciding if your shirt is unique. A NOT EXISTS will give its decision as soon as it finds a matching colour - it only checks all shirts if there are no matches.

    • This reply was modified 2 weeks, 6 days ago by  EdVassie.

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara


  • The first thing you should do is use the query plan to see if new indexes might help. This often helps. My best win on this was when I reduced a query from taking over 12 hours to taking seconds just by looking at the query plan and adding the right index.

    If  indexes do not do all that is needed, then try to reduce the complexity of the final query. A good way to reduce complexity is to do some pre-aggregation. Not all queries need pre-aggregation, but joins using NOT or OR can benefit from this. The query plan will help you decide if pre-aggregation might be of use.

    Look at how you can split the query into smaller units, with each unit populating it's own table. These tables can then be indexed to improve the performance of your main query.

    One way to package the pre-aggregation is to get whatever runs your query to instead call a stored procedure. You can then do all the needed pre-aggregation in the SP. There are many other ways to package pre-aggregation, such as indexed views, triggers, or separate table builds. You have to choose what is best in your situation.

    When I have done pre-aggregation, I have sometimes got sub-second response from queries that previously took minutes or hours. There have also been situations where pre-aggregation was no help at all.

    • This reply was modified 2 weeks, 6 days ago by  EdVassie.

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply