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.