Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've got this fairly simple reporting procedure that searches through inventory transaction data (stuff being received, moving around the warehouse, etc). The whole thing is listed below - I've only anonymized the procedure name. It's pulling data from Dynamics GP (explaining the awful table/field names), and an internal data warehouse.

CREATE PROCEDURE InvTrans
    @strItem varchar(20) = NULL,
    @strSN varchar(20) = NULL,
    @strStartDate date,
    @strEndDate date
WITH RECOMPILE
AS

SET @strItem = ISNULL(@strItem, '') + '%'
SET @strSN = ISNULL(@strSN, '') + '%'

SELECT TOP 10000
    d.unitcost AS cost,
    d.unitprice AS price,
    d.qty AS quantity, 
    RTRIM(d.itemnmbr) as item, 
    RTRIM(iv.ITEMDESC) AS itemdesc,
    d.txdate,
    RTRIM(d.serltnum) AS serltnum,
    d.doctype,
    RTRIM(d.docnumbr) AS docnumbr,
    RTRIM(d.refnumbr) AS refnumbr,
    d.glpostdt,
    RTRIM(d.username) AS username,
    d.lnseqnbr,
    RTRIM(d.from_place) AS from_place,
    RTRIM(d.to_place) AS to_place
FROM dw..dw_invtrans d
    INNER JOIN IV00101 iv
        ON d.itemnmbr = iv.ITEMNMBR
WHERE d.itemnmbr LIKE @strItem
    AND d.serltnum LIKE @strSN
    AND d.txdate BETWEEN CAST(@strStartDate AS datetime) AND CAST(@strEndDate AS datetime)
ORDER BY d.itemnmbr, d.txdate

There's obviously parameter sniffing going on, that much I can tell. The dw_invtrans table has 4,711,806 rows. There are non-clustered indexes on (itemnmbr, txdate) and (serltnum, txdate).

If I execute the procedure like this, it takes 17-20 seconds:

EXEC InvTrans @strItem = '', @strSN = '23-A', @strStartDate = '1/1/1999', @strEndDate = '2/21/2012'

If I execute it like this, it take less than 1 second, and returns the same results:

EXEC InvTrans @strItem = NULL, @strSN = '23-A', @strStartDate = '1/1/1999', @strEndDate = '2/21/2012'

As far as I can tell, when SQL Server sees one of the two item or serial number parameters is null, it generates an execution plan that ignores that filter completely (since the query uses it for a LIKE, it's assuming it won't match any rows, but the procedure turns it into '%'). If either of those parameters is an empty string, it considers that filter (which will end up matching every row, since it turns into '%'), thus hash-matching the small number of rows with a matching serial number to a totally unfiltered dw_invtrans (4.7 million rows).

What would be the best trick to have the execution plan ignore either of the two filters if it's null or a blank string? Bear in mind that I'm going to bind this to Reporting Services - normally I'd just have it dynamically select which query to run inside the procedure, but that often screws up Reporting Services' output column enumeration since it runs the procedure with FMTONLY on.

share|improve this question
    
As an aside, your BETWEEN logic is faulty (unless your txdate column does not include time, ever, in which case it should be DATE not DATETIME). Usually better to use >= @Start AND < DATEADD(DAY, 1, @End). –  Aaron Bertrand Feb 21 '12 at 15:25
    
That's correct, txdate NEVER includes time, since it's taken from a Dynamics GP column (which almost never uses time in its 'datetime' columns). This data warehouse table was created way before the 'date' data type existed (possibly as early as SQL Server 6.5). I would have at least used 'smalldatetime', but I didn't work here 13 years ago. :) –  db2 Feb 21 '12 at 15:52
    
when using set statistics time on, how much cpu time does each of the two alternatives take? –  cairnz Feb 21 '12 at 18:27

1 Answer 1

The sniff comes from the NULL or the empty string. The addition of % is ignored.
You can fix this one of 2 ways:

  1. Use parameter masking, by using another variable internally, say @IstrItem
    SET @IstrItem = ISNULL(@strItem, '') + '%'

  2. Add OPTIMIZE FOR UNKNOWN hint to the query

Other observations:

  • Why not an index on all 3 columns? One of these is probably best
    serltnum, txdate, itemnmbr or txdate, serltnum, itemnmbr
  • Do datatypes and length match? eg txdate is datetime, itemnmbr is varchar(20) etc
    If not, indexes will be ignored
  • So many non-key columns will require a key lookup, or the indexes are being ignored
    See Simple Talk's covering indexes article for more
share|improve this answer
    
I tried using another internal variable for the parameters, but that causes index seeks for both parameters regardless of input, like passing in ''. Data types in the table are actually char(31) and char(21) for itemnmbr and serltnum respectively. It's still using the correct indexes, though. The index join seems to perform well when both parameters are specific enough. I'm not worried about the key lookups, since there are usually few rows returned. Hadn't heard of OPTIMIZE FOR UNKNOWN, I'm going to read up on that one. –  db2 Feb 21 '12 at 14:37
    
Interesting. If I use OPTIMIZE FOR UNKNOWN, it seeks the index on txdate and filters that based on @strItem and @strSN. Then it sorts and merge-joins that result to IV00101. This makes any execution with that particular date range (13 years) take 4 seconds. –  db2 Feb 21 '12 at 14:48
    
BTW: "index seek" is good. Also, try making the internal variables the exact datatype as the columns and see what happens –  gbn Feb 21 '12 at 15:27
    
Index seeks are usually good, except in this case where it will happily "seek" all 4.7 million rows in the table. :) –  db2 Feb 21 '12 at 15:55
    
@db2: seek is a drill into data. "all rows" is a scan –  gbn Feb 21 '12 at 15:59

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.