Multiple execution plans for one stored procedure depending on used parameters

Question

Is possible to store multiple execution plans for one SQL procedure depending on used parementers? I mean I have one SQL procedure. There are some typical patterns of calling this procedure (some input parameters are left NULL) and I don't want then plan be recalculated every single call.

Answer 1

The answer is Yes, you can have multiple execution plans for a single stored procedure based on input parameters. This is known as parameter sniffing.

If there is a set of parameters that produces a good enough plan that all invocations of the procedure perform well, one option is to use the optimize for hint:

OPTIMIZE FOR ( @variable_name { UNKNOWN | = literal_constant } [ , ...n ] )

This will avoid using the approach which requires a recompile of the proc for all calls.

Answer 2

You can write some sp, one for each case, and then call them from your main sp as conditional call:

create proc...
if @param1 is null exec sp_1 @param2, param3

But if you have too many cases, it's not a good workaround.

In this case you can implement your query as dynamic code constructing it only with not null parameters, see the example here: Implementing search_orders with a Parameterised Query by Erland sommarskog, the code is below.

This way the query is not recompiled every time, but there will be exactly so many plans as many different queries can be constructed combining only not null parameters (just what you want)

CREATE PROCEDURE search_orders_1                                   --  1
                 @orderid     int          = NULL,                 --  2
                 @fromdate    datetime     = NULL,                 --  3
                 @todate      datetime     = NULL,                 --  4
                 @minprice    money        = NULL,                 --  5
                 @maxprice    money        = NULL,                 --  6
                 @custid      nchar(5)     = NULL,                 --  7
                 @custname    nvarchar(40) = NULL,                 --  8
                 @city        nvarchar(15) = NULL,                 --  9
                 @region      nvarchar(15) = NULL,                 -- 10
                 @country     nvarchar(15) = NULL,                 -- 11
                 @prodid      int          = NULL,                 -- 12
                 @prodname    nvarchar(40) = NULL,                 -- 13
                 @employeestr varchar(MAX) = NULL,                 -- 14
                 @employeetbl intlist_tbltype READONLY,            -- 15
                 @debug       bit          = 0 AS                  -- 16
                                                                   -- 17
DECLARE @sql        nvarchar(MAX),                                 -- 18
        @paramlist  nvarchar(4000),                                -- 19
        @nl         char(2) = char(13) + char(10)                  -- 20
                                                                   -- 21
SELECT @sql =                                                      -- 22
    'SELECT o.OrderID, o.OrderDate, od.UnitPrice, od.Quantity,     -- 23
            c.CustomerID, c.CompanyName, c.Address, c.City,        -- 24
            c.Region,  c.PostalCode, c.Country, c.Phone,           -- 25
            p.ProductID, p.ProductName, p.UnitsInStock,            -- 26
            p.UnitsOnOrder, o.EmployeeID                           -- 27
     FROM   dbo.Orders o                                           -- 28
     JOIN   dbo.[Order Details] od ON o.OrderID = od.OrderID       -- 29
     JOIN   dbo.Customers c ON o.CustomerID = c.CustomerID         -- 30
     JOIN   dbo.Products p ON p.ProductID = od.ProductID           -- 31
     WHERE  1 = 1' + @nl                                           -- 32
                                                                   -- 33
IF @orderid IS NOT NULL                                            -- 34
   SELECT @sql += ' AND o.OrderID = @orderid' +                    -- 35
                  ' AND od.OrderID = @orderid' + @nl               -- 36
                                                                   -- 37
IF @fromdate IS NOT NULL                                           -- 38
   SELECT @sql += ' AND o.OrderDate >= @fromdate' + @nl            -- 39
                                                                   -- 40
IF @todate IS NOT NULL                                             -- 41
   SELECT @sql += ' AND o.OrderDate <= @todate'  + @nl             -- 42
                                                                   -- 43
IF @minprice IS NOT NULL                                           -- 44
   SELECT @sql += ' AND od.UnitPrice >= @minprice'  + @nl          -- 45
                                                                   -- 46
IF @maxprice IS NOT NULL                                           -- 47
   SELECT @sql += ' AND od.UnitPrice <= @maxprice'  + @nl          -- 48
                                                                   -- 49
IF @custid IS NOT NULL                                             -- 50
   SELECT @sql += ' AND o.CustomerID = @custid' +                  -- 51
                  ' AND c.CustomerID = @custid' + @nl              -- 52
                                                                   -- 53
IF @custname IS NOT NULL                                           -- 54
   SELECT @sql += ' AND c.CompanyName LIKE @custname + ''%''' + @nl -- 55
                                                                   -- 56
IF @city IS NOT NULL                                               -- 57
   SELECT @sql += ' AND c.City = @city' + @nl                      -- 58
                                                                   -- 59
IF @region IS NOT NULL                                             -- 60
   SELECT @sql += ' AND c.Region = @region' + @nl                  -- 61
                                                                   -- 62
IF @country IS NOT NULL                                            -- 63
   SELECT @sql += ' AND c.Country = @country' + @nl                -- 64
                                                                   -- 65
IF @prodid IS NOT NULL                                             -- 66
   SELECT @sql += ' AND od.ProductID = @prodid' +                  -- 67
                  ' AND p.ProductID = @prodid' + @nl               -- 68
                                                                   -- 69
IF @prodname IS NOT NULL                                            --70
   SELECT @sql += ' AND p.ProductName LIKE @prodname + ''%''' + @nl-- 71
                                                                   -- 72
IF @employeestr IS NOT NULL                                        -- 73
   SELECT @sql += ' AND o.EmployeeID IN' +                         -- 74
                  ' (SELECT number FROM dbo.intlist_to_tbl(@employeestr))' + @nl
                                                                   -- 76
IF EXISTS (SELECT * FROM @employeetbl)                             -- 77
   SELECT @sql += ' AND o.EmployeeID IN (SELECT val FROM @employeetbl)' + @nl
                                                                   -- 79
SELECT @sql += ' ORDER BY o.OrderID' + @nl                         -- 80
                                                                   -- 81
IF @debug = 1                                                      -- 82
   PRINT @sql                                                      -- 83
                                                                   -- 84
SELECT @paramlist = '@orderid     int,                             -- 85
                     @fromdate    datetime,                        -- 86
                     @todate      datetime,                        -- 87
                     @minprice    money,                           -- 88
                     @maxprice    money,                           -- 89
                     @custid      nchar(5),                        -- 90
                     @custname    nvarchar(40),                    -- 91
                     @city        nvarchar(15),                    -- 92
                     @region      nvarchar(15),                    -- 93
                     @country     nvarchar(15),                    -- 94
                     @prodid      int,                             -- 95
                     @prodname    nvarchar(40),                    -- 96
                     @employeestr varchar(MAX),                    -- 97
                     @employeetbl intlist_tbltype READONLY'        -- 98
                                                                   -- 99
EXEC sp_executesql @sql, @paramlist,                               -- 100
                   @orderid, @fromdate, @todate, @minprice,        -- 101
                   @maxprice,  @custid, @custname, @city, @region, -- 102
                   @country, @prodid, @prodname, @employeestr, @employeetbl