SQL query running slowly - parameter sniffing

Question

I have a simple query where I return a list of orders by date range. This query is used in a report which feeds it parameters(Site, From Date, and To Date).

ALTER PROCEDURE [dbo].[Z_N_ECOM_ORDER_STATUS_DATERANGE]
    @Site VARCHAR(5),
    @FromDate DATETIME,
    @ToDate DATETIME
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        o.Company_Code,
        o.Division_Code,
        o.Control_Number,
        RTRIM(o.Customer_Purchase_Order_Number) AS Shopify_Num,
        CASE 
           WHEN p.PickTicket_Number IS NULL
              THEN i.PickTicket_Number 
              ELSE p.PickTicket_Number 
        END PickTicket_Number,
        i.Invoice_Number,
        o.Date_Entered,
        CASE
           WHEN ph.packslip IS NULL AND i.invoice_number IS NULL  
                AND P.pickticket_number IS NULL
              THEN 'Cancelled' 
           WHEN ph.packslip IS NULL AND i.invoice_number IS NULL 
                AND DATEADD(minute, 90, o.date_entered) > CURRENT_TIMESTAMP
              THEN 'Not Entered Yet'  
           WHEN ph.packslip IS NULL 
              THEN 'SHIPPED & UPLOADED' 
           ELSE RTRIM (z.status) 
        END Accellos_Status, 
        b.UPS_Tracking_Number Tracking_Number
    FROM
        [JMNYC-AMTDB].[AMTPLUS].[dbo].Orders o (nolock)
    LEFT JOIN
        [JMNYC-AMTDB].[AMTPLUS].[dbo].PickTickets p (nolock) ON o.Company_Code = p.Company_Code 
                                                         AND o.Division_Code = p.Division_Code 
                                                         AND o.Control_Number = p.Control_Number
    LEFT JOIN
        [JMNYC-AMTDB].[AMTPLUS].[dbo].Invoices i (nolock) ON o.Company_Code = i.Company_Code 
                                                      AND o.Division_Code = i.Division_Code 
                                                      AND o.Control_Number = i.Control_Number   
    LEFT JOIN
        [JMNYC-AMTDB].[AMTPLUS].[dbo].box b (nolock) ON o.Company_Code = b.Company_Code 
                                                AND o.Division_Code = b.Division_Code 
                                                AND i.PickTicket_Number = b.PickTicket_Number
    LEFT JOIN
        pickhead ph (nolock) ON p.PickTicket_Number = ph.packslip
    LEFT JOIN
        Z_Status z (nolock) ON ph.PROCSTEP = z.procstep
    WHERE 
        o.Company_Code = LEFT(@Site, 2)
        AND o.Division_Code = RIGHT(@Site, 3) 
        AND o.Customer_Number = 'ecom2x'
        AND o.Date_Entered BETWEEN @FromDate AND DATEADD(dayofyear, 1, @ToDate)
    ORDER BY 
        o.date_entered DESC
END

The problem with this query is that it takes way too long and the problem lines are

 WHERE 
     o.Company_Code = LEFT(@Site, 2)
     AND o.Division_Code = RIGHT(@Site, 3)

The format of the variable site is something like '09001' or '03001' where the left side is the company and the right side is the division

Because when I run this query with hard-coded values, it runs pretty much instantaneously. When I use the parameters, it takes minutes.

So I looked it up and I discovered about parameter sniffing. So I added the following line after the begin statement.

DECLARE @LocalSite VARCHAR(5) = CAST(@Site AS VARCHAR(5))

However, it still runs extremely slow.

My new where statement would be

WHERE 
    o.Customer_Number = 'ecom2x'
    AND o.Date_Entered BETWEEN @FromDate AND DATEADD(dayofyear, 1,  @ToDate)
    AND ((@LocalSite = '00000') OR (O.Company_Code = LEFT(@LocalSite, 2) AND O.Division_Code = RIGHT(@LocalSite, 3))) 
order by o.date_entered desc*

I also want the user to have the functionality of selecting all sites which will make the site variable be '00000' and thus it shouldn't run the company/division code check. This current where statement makes the query run very slow.

Does anyone know what I am doing wrong?

Answer 1

Can you try to avoid using LEFT() and RIGHT() by declaring few variables and assigning values to those variables and then using them in the SELECT statement?

a hint OPTIMIZED FOR UNKNOWN to avoid parameter sniffing:

option (OPTIMIZE FOR (@p1 UNKNOWN, @p2 UNKNOWN))

Where p1 and p2 those two variables mentioned above

I also want the user to have the functionality of selecting all sites which will make the site variable be '00000' and thus it shouldn't run the company/division code check. This current where statement makes the query run very slow.

This can be optimized by replacing current SELECT with IF statement that uses two SELECTs. If value is 00000 just avoid to check on company and division, else run the same select but with those extra checks

Another striking thing is querying linked server objects with further join to local tables. Consider to split this into a separate step, for instance by storing data in temporary table (not a table variable!) as intermediate result. Then temp table to be joined with local objects. This can improve accuracy of query plan because of better estimates.

Answer 2

Did you try taking left and right values of@site parameter in two different variables and using those variables in SP.

For eg.

Declare @compcode as varchar(2)
Declare @divcode as varchar(3)
Set @compcode=LEFT(@Site, 2)
Set @divcode=RIGHT(@Site, 3)

Your where condition

WHERE 
o.Company_Code = @compcode
AND o.Division_Code = @divcode