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?

To solve performance issues it would be helpful to post the execution plan. Here is a great way to do that. https://www.brentozar.com/pastetheplan/ In the meantime I would strongly suggest you don't splatter NOLOCK everywhere. It is NOT a magic go fast button, it has some very serious baggage with it. https://blogs.sentryone.com/aaronbertrand/bad-habits-nolock-everywhere/. Also might be worth reading about BETWEEN. https://sqlblog.org/2011/10/19/what-do-between-and-the-devil-have-in-common — Sean Lange, Jan 25 '19 at 19:43
Analyze the query plan, I guess you have some missing indexes that are causing slow execution. Execution plans for stored procedures are cached, this can be turned off by adding `WITH RECOMPILE` to SP header. — Karel Frajták, Jan 25 '19 at 19:44
@SeanLange putting nolock everywhere was requested by my supervisor, i do not have control over that. Also, would you like the execution plan of the query with hardcoded values or of the stored procedure? Because it is only slow as a stored procedure with parameters. https://www.brentozar.com/pastetheplan/?id=BkHaPktQE — Natan, Jan 25 '19 at 19:48
The problem is probably that you are using not the code itself, but you apply a function first so any indexes are ignored and you get a table scan instead (making it unsearchable, see https://www.sqlconsulting.com/archives/understanding-search-arguments/). It might be a lot faster if you had a lookup table with divisions, companies and sites, so it can actually lookup the value '09001' in the index. — PeterDeV, Jan 25 '19 at 19:49
@SeanLange The problem is not with nolock, or between, it is definitely with the Left and Right functions. — Natan, Jan 25 '19 at 19:50
I did NOT suggest the problem was NOLOCK or BETWEEN. But those nolock hints are very likely a bad idea. Understand sometimes you have to. And suggested reading about between because it causes people lots of pain, especially with dates. If it is fast in SSMS and slow as a procedure it is almost certainly bad parameter sniffing as you seem to have guessed. The left and right are not an issue here because they are looking at a parameter. Functions in the where clause around columns are bad, not a big deal around parameters. — Sean Lange, Jan 25 '19 at 20:06
@Sean Lange, I stand corrected, I misread the code. The query itself could just be looking for Company_Code or Division_Code in an index, so I agree it has to be parameter sniffing and that it has nothing to do with Search Arguments. — PeterDeV, Jan 25 '19 at 20:13
You can determine right away whether it's a parameter sniffing problem...redefine the procedure with RECOMPILE...which keeps SQL from keeping a query plan on the procedure. If it flies...there's the issue...and recompilation probably disappears into the background as a cost item. — Clay, Jan 25 '19 at 20:29
I tried with recompile aswell, the query did not get any faster. @SeanLange I don't understand, if you say the left and right are not the issue, what could be the issue? — Natan, Jan 25 '19 at 20:55
Gail Shaw has a great series of blog posts on this topic [here](https://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/). Make sure you check out parts 2 and 3 also. She does a great job explaining this issue and offers several ways of resolving it. — Sean Lange, Jan 25 '19 at 20:59
Post an image of the actual query plan if you can...bound to point somewhere ;-) — Clay, Jan 25 '19 at 21:43
@SeanLange if parameter sniffing is the problem (which I also do think it is now), how come using the local variable solution seems to do nothing? I've read the three part article you've linked but my query still runs as slow as before. — Natan, Jan 28 '19 at 15:00
I can't answer that because I don't know what the new code looks like. I have seen (and done myself) in the past trying to deal with sniffing and used local variables but forgot the change the query itself. Maybe that happened here? Can you post the updated query so we can take a look? — Sean Lange, Jan 28 '19 at 15:07

Alexander Volok · Answer 1 · 2019-01-25T20:33:23.827

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.

Since the OP is using left and right to look at parameters so that is very unlikely to be any issue here. — Sean Lange, Jan 25 '19 at 20:09
I think parameter sniffing is the issue, but all listed measure can be applied — Alexander Volok, Jan 25 '19 at 20:19

score 0 · Answer 2 · answered Jan 25 '19 at 20:39

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

SQL query running slowly - parameter sniffing

2 Answers2