Order by slowly when use index hints

by 
SELECT TOP (@TotalRecord)
        CASE WHEN @IsGrouping = 1 THEN OrderMain.OrderID ELSE 0 END OrderID,
        CASE WHEN @IsGrouping = 1 THEN OrderMain.InvoiceReference ELSE '' END InvoiceReference, 
        CASE WHEN @IsGrouping = 1 THEN OrderMain.OrderAlias ELSE '' END OrderAlias, --sOrderAlias
        CASE WHEN @IsGrouping = 1 THEN OrderMain.Service  ELSE '' END Service,
        CASE WHEN @IsGrouping = 1 THEN Site.SiteCode ELSE '' END SiteCode, --sSiteCode
        CASE WHEN @IsGrouping = 1 THEN S2.CustomerShiptoCode ELSE '' END CustomerShiptoCode, --sCustomerShipToCode
        CASE WHEN @IsGrouping = 1 THEN DispatchGroup.DispatchGroup ELSE '' END DispatchGroup, --sDispatchGroup
        CASE WHEN @IsGrouping = 1 THEN JS2.Sequence ELSE 0 END AS Seq1, --sSequence 
        CASE WHEN @IsGrouping = 1 THEN JS2.PodDateTime ELSE CAST(NULL AS datetime) END JobPodDateTime,
        CASE WHEN @IsGrouping = 1 THEN OrderMain.PodDateTime ELSE CAST(NULL AS datetime) END OrderPodDateTime,
        CASE WHEN @IsGrouping = 1 THEN JS2.JobStopStatus ELSE '' END JobStopStatus,
        CASE WHEN @IsGrouping = 1 THEN JS2.StopType ELSE '' END StopType, --sStopType
        CASE WHEN @IsGrouping = 1 THEN JS2.ArriveDateTime ELSE CAST(NULL AS datetime) END ArriveDateTime,
        CASE WHEN @IsGrouping = 1 THEN JS2.DepartDateTime ELSE CAST(NULL AS datetime) END DepartDateTime,
        CASE WHEN @IsGrouping = 1 THEN LEFT(CONVERT(VARCHAR(8), ISNULL(JS2.LateDateTime,JS2.ScheduledDateTime), 108), 5) ELSE '' END DueDateTime, --sDueTime
        CASE WHEN @IsGrouping = 1 THEN ISNULL(JS2.LateDateTime,JS2.ScheduledDateTime) ELSE CAST(NULL AS datetime) END DueDateTime1,
        CASE WHEN @IsGrouping = 1 THEN (CASE WHEN JS2.Sequence = 1 THEN S1.Name ELSE S2.Name END) ELSE '' END Name, --sDelivery
        OrderMain.OrderNumber,
        OrderMain.OrderDate,
        OrderMain.Auth, --sAuth,
        ISNULL(JS2.LateDateTime,JS2.ScheduledDateTime) DueDateAndTime,
        CASE WHEN ISNULL(JS2.JobStopStatus, '') = 'C' 
            THEN JS2.PodDateTime
            ELSE NULL
            END ActualDT

        FROM OrderMain WITH (Readuncommitted, Index = CustomerIDOrderDateOrderID)
        INNER JOIN DelimitedSplit(@ListCustomerID, ',') Cus
            ON Cus.Item = OrderMain.CustomerID
            AND OrderMain.OrderDate BETWEEN @FDate AND @TDate           
            AND OrderMain.OrderDate BETWEEN @FDate AND @TDate
            AND ((OrderMain.OrderType="R") or (OrderMain.OrderType IS NULL))
            AND OrderMain.OrderStatus in ('N','A','I','P')
            AND (@Service="" OR OrderMain.Service = @Service)
            AND (@OrderAlias="" OR OrderMain.OrderAlias = @OrderAlias)
            AND (@InvoiceReference="" OR OrderMain.InvoiceReference = @InvoiceReference)
            AND (@Auth="" OR OrderMain.Auth = @Auth)
        INNER JOIN Site WITH (READUNCOMMITTED) ON Site.SiteID = OrderMain.SiteID 

        INNER JOIN Job WITH (READUNCOMMITTED) on OrderMain.OrderID = Job.OrderID
        INNER JOIN DispatchGroup WITH (READUNCOMMITTED) ON DispatchGroup.DispatchGroupID = Job.DispatchGroupID 
            AND (@DispatchGroup = '' OR DispatchGroup.DispatchGroupID = @DispatchGroup)

        INNER JOIN JobStop JS1 WITH (READUNCOMMITTED) On Job.JobID = JS1.JobID AND JS1.Sequence="1"  
        INNER JOIN Stop S1 WITH (READUNCOMMITTED) on JS1.StopID = S1.StopID 

        INNER JOIN JobStop JS2 WITH (READUNCOMMITTED) on Job.JobID = JS2.JobID 
        AND (@IncludeStop1 = 1 OR JS2.Sequence <> 1)
        AND ((@IncludeAllStops = 0 AND JS2.Sequence 
            < (CASE WHEN (SELECT TOP 1 Sequence FROM JobStop JS3 WITH (READUNCOMMITTED) WHERE JS3.JobID = Job.JobID ORDER BY Sequence DESC) = 2 
                THEN 3
                ELSE (SELECT TOP 1 Sequence FROM JobStop JS3 WITH (READUNCOMMITTED) WHERE JS3.JobID = Job.JobID ORDER BY Sequence DESC) END))

        OR (@IncludeAllStops = 1 AND JS2.Sequence 
            <= (CASE WHEN (SELECT TOP 1 Sequence FROM JobStop JS3 WITH (READUNCOMMITTED) WHERE JS3.JobID = Job.JobID ORDER BY Sequence DESC) = 2 
                THEN 3
                ELSE (SELECT TOP 1 Sequence FROM JobStop JS3 WITH (READUNCOMMITTED) WHERE JS3.JobID = Job.JobID ORDER BY Sequence DESC) END)))

        INNER JOIN Stop S2 WITH (READUNCOMMITTED) on JS2.StopID = S2.StopID
            AND (@CustomerShiptoCode="" OR S2.CustomerShiptoCode = @CustomerShiptoCode)
            AND (@StopName="" OR S2.Name like @StopName + '%')
            
        ORDER BY 
            CASE WHEN @OrderBy = 1 THEN OrderMain.OrderNumber END,
            CASE WHEN @OrderBy = 1 THEN Job.DeliverySequence END,
            CASE WHEN @OrderBy = 1 THEN JS2.Sequence END,
            CASE WHEN @OrderBy = 2 THEN OrderMain.OrderDate END,
            CASE WHEN @OrderBy = 2 THEN OrderMain.OrderNumber END,
            CASE WHEN @OrderBy = 2 THEN Job.DeliverySequence END,
            CASE WHEN @OrderBy = 2 THEN JS2.Sequence END,
            CASE WHEN @OrderBy = 3 THEN S2.Name END,
            CASE WHEN @OrderBy = 3 THEN OrderMain.OrderNumber END,
            CASE WHEN @OrderBy = 3 THEN Job.Sequence END,
            CASE WHEN @OrderBy = 3 THEN JS2.Sequence END,
            CASE WHEN @OrderBy = 4 THEN Job.Sequence END,
            CASE WHEN @OrderBy = 4 THEN OrderMain.Auth END,
            CASE WHEN @OrderBy = 4 THEN OrderMain.OrderNumber END,
            CASE WHEN @OrderBy = 4 THEN Job.DeliverySequence END,
            CASE WHEN @OrderBy = 4 THEN JS2.Sequence END
       OPTION (recompile) 
END

  • My qurey is slow with CASE WHEN @OrderBy = 1…4 THEN JS2.Sequence END, about 15s.If remove order by JS2.Sequence then query done at 1s
    enter image description here

  • let me know why do not seek index althouth I created index for jobid vs sequence
    enter image description here

  • Instead of images, upload the actual execution plan to Paste The Plan and add the link to your question.

    – 

  • 2

    I would suggest that perhaps the ordering should be done in the presentation layer, rather than the SQL layer, considering that you you want it to be dynamic.

    – 

  • I’d suggest simplifying that ordering logic. For example, every options sorts by JS2.Sequence last – you could put that unconditionally at the end rather than having separate CASE expressions for each occurrence. Similarly they all seem to have OrderMain.OrderNumber – so again, make that unconditional. Then just special case those additional options that need to consider additional elements before/after that one.

    – 

  • I would suggest that if you’re going to pay the OPTION (RECOMPILE) tax on each execution you might as well go all out and conditionally generate dynamic SQL so that the planner/optimiser doesn’t have to evaluate each and every one of those CASE-WHEN expressions based on the scalar parameters.

    – 

  • You have the same isolation level on every single table. Why not just set the transaction level for the whole procedure? Or event better, don’t use that hint if you want accurate results. Dirty reads can and will return missing and/or duplicate rows at random times.

    – 

Leave a Comment