What is the most efficient way to optimize a filter based on parameters on a SQL Server stored proce-CodePudding

Right now I have something like:

CREATE PROCEDURE [dbo].[sp_GetFilteredInformation]
    @pItem nvarchar(max) = NULL, 
    @pCity nvarchar(max) = NULL,
    @pSerialN nvarchar(max) = NULL, 
    @pPromise datetime = NULL,
    @pSalesOrder nvarchar(max) = NULL, 
    @pLineNumber int = NULL

    DECLARE @vQuery nvarchar(max)

    IF (@pItem IS NOT NULL) 
    BEGIN 
        SET @vQuery  = 'AND ITEM LIKE ''   @pItem   ''' 
    END

    IF (@pCity IS NOT NULL) 
    BEGIN 
        SET @vQuery  = 'AND CITY LIKE ''   @pCity   ''' 
    END

... and so on, so in the end I'll have

SELECT * 
FROM TABLE 
WHERE 1 = 1   @vQuery

I think this is going to work, but it doesn't seems efficient to me. Is there a way to optimize this process and filter information with multiple parameters, with the option of some of them being null?

CodePudding user response：

The only way to optimize a generic filter is to parse all items and condition of filter in a SQL dynamic query.

To do that, you need to have 2 XML parameters :

The one that list the columns to be reached, in the form : date_begintown
The other one with datatype and values, like : <val @type=date>2021-09-31<val @type=string>Paris

Then with these two strings that contains only the columns for searched values, you can build a query that will have a specific and optimizable WHERE predicate...

CodePudding user response：

The most efficient method to do this type of kitchen-sink query is actually the way you are doing it now, except that you should properly parameterize each filter.

This is because a single plan will be cached for every possible combination of filters. This means that whenever that combination of filters is used again, even with different values, a cached plan will be used.

Whereas if you use OPTION(RECOMPILE), a new plan is generated on every run. And OPTION(OPTIMIZE FOR UNKNOWN) will usually just get you an overall not-that-great plan.

So you parameterize it with sp_executesql, like this

CREATE PROCEDURE [dbo].[sp_GetFilteredInformation]
    @pItem nvarchar(max) = NULL, 
    @pCity nvarchar(max) = NULL,
    @pSerialN nvarchar(max) = NULL, 
    @pPromise datetime = NULL,
    @pSalesOrder nvarchar(max) = NULL, 
    @pLineNumber int = NULL

DECLARE @vQuery nvarchar(max) = '
SELECT *
FROM YourTable
WHERE 1=1
';

IF (@pItem IS NOT NULL) 
    SET @vQuery  = 'AND ITEM LIKE @pItem
';

IF (@pCity IS NOT NULL) 
    SET @vQuery  = 'AND CITY LIKE @pCity
';

-- etc

-- for testing you can use PRINT @vQuery

EXEC sp_executesql
    @vQuery,

  N'@pItem nvarchar(max), 
    @pCity nvarchar(max),
    @pSerialN nvarchar(max), 
    @pPromise datetime,
    @pSalesOrder nvarchar(max),
    @pLineNumber int',

    @pItem = @pItem,
    @pCity = @pCity,
    @pSerialN = @pSerialN,
    @pPromise = @pPromise,
    @pSalesOrder = @pSalesOrder,
    @pLineNumber = @pLineNumber;