When you have multiple optional parameters in a stored procedure...

by Jon 30. March 2010 09:47

If you have a number of optional parameters in a stored procedure (that are disabled with boolean logic) that will dramatically change the way the query works depending on the parameter it is best to add (WITH COMPILE) to the stored procedure, to ensure that the best query plan is always used.  The first time a stored procedure is executed, the parameters you have passed will determine the way it is executed.  If you first run the stored procedure against an empty database, used some duff test fields, or you have a report based stored procedure that works in a different way depending on parameters the first call may not be the ideal query plan.  By forcing the query to be recompiled for these optional varied you will massively boost performance.  This feature should be used sparingly, but there are often places where it needs to be essential (Some reporting stored procedures/stored procedures used to search)

ALTER Procedure [dbo].[Report_ExportReworkData]
    @ProductOption as nvarchar(50),
    @SerialNumber as nvarchar(50),
    @WorksOrder as nvarchar(50)

-- Make sure this gets compiled each time to avoid incorrect parmeter sniffing

    (len(@ProductOption) = 0 or WorksOrders.PartNo = @ProductOption) and
    (len(@WorksOrder) = 0 or WorksOrders.WorksOrderNo = @WorksOrder) and
    (len(@SerialNumber) = 0 or  MfgRecords.SerialNumber = @SerialNumber)

Tags: , , , , , , ,

development | SQL

Powered by BlogEngine.NET
Original Design by Laptop Geek, Adapted by onesoft, and finally some tiny tweaks by JonAlb