Thwarting some awful parameter sniffing

Question

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.

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