What is the most efficient way to optimize a filter based on parameters on a SQL Server stored procedure?

Question

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?

WARNING: Your code is dangerous. You are injecting parameters into your dynamic statement, not parametrising them. This is especially worse as you are giving someone malicious 8GB (or 4 billion characters) to play with. — Thom A
– Thom A ♦, Commented Oct 5, 2021 at 15:06
I suggest having a read of catch-all queries and An updated Kitchen Sink Example. — Thom A
– Thom A ♦, Commented Oct 5, 2021 at 15:07
This particular method for kitchen-sink queries is actually the most efficient, just you need to make sure to pass the actual data parameters all the way through, using sp_executesql, do not inject them — Charlieface
– Charlieface, Commented Oct 5, 2021 at 15:26
Side note: you should not use the sp_ prefix for your stored procedures. Microsoft has reserved that prefix for its own use (see Naming Stored Procedures), and you do run the risk of a name clash sometime in the future. It's also bad for your stored procedure performance. It's best to just simply avoid sp_ and use something else as a prefix - or no prefix at all! — marc_s
– marc_s, Commented Oct 5, 2021 at 15:28
"About the dot, it was a typo" But the sp_ prefix is also a problem and should not be used. — Thom A
– Thom A ♦, Commented Oct 5, 2021 at 15:41

Charlieface · Accepted Answer · 2021-10-05 18:40:44Z

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;

SQLpro · Accepted Answer · 2021-10-05 15:43:26Z

1

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...

answered Oct 5, 2021 at 15:43

SQLpro

5,3121 gold badge9 silver badges19 bronze badges

3 Comments

Thom A Over a year ago

There are plenty of other ways other than using 2 XML parameters. Especially when XML doesn't allow for strongly typed values.

SQLpro Over a year ago

I did not say that XML is the only way... I did say that the only way to optimize it is to parse...

Thom A Over a year ago

You say "you need to have 2 XML parameters", that implies that there aren't alternative options. There are, however, options where you don't "need" XML parameters at all.

Collectives™ on Stack Overflow

What is the most efficient way to optimize a filter based on parameters on a SQL Server stored procedure?

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related