CODEWITHSUNDEEP
sqlsql-serverstored-proceduressql-execution-plan
waazi
waazi

Reputation: 25

Search from Multiple columns in Where clause for SQL Server

We have a stored procedure that is used to allow users to search in a table with 20 million records and 40 columns wide. There are about 20 different columns they can search on (any combination) from and all those columns are in the WHERE clause.

Furthermore each columns is checked for Null and needs to be able to search with just part of the data.

Here is an example

(
    @FirstName IS NULL
    OR (RTRIM(UPPER(FirstName)) LIKE RTRIM(UPPER(@FirstName)) + '%')
)
AND (@LastName IS NULL)

What is a best way to rewrite this stored procedure? Should I break this stored procedure into multiple small stored procedures? If so how? I will need to allow user to search

When I look at the execution plan, regardless of what columns are passed, it always does the index scan

Upvotes: 1

Views: 3548

Answers (3)

user3703582
user3703582

Reputation:

I had exactly this situation years ago, millions of rows and numerous filter parameters and the best method is to use dynamic sql. Construct a SQL statement based on the parameters that have values, then execute the SQL statement. (EXEC sp_executesql @sql)

The select clause of the sql statement is static but the from clause and the where clause is based on the parameters.

CREATE PROCEDURE dbo.DynamicSearch
    @FirstName     VARCHAR(20),
    @LastName      VARCHAR(20),
    @CompanyName   VARCHAR(50)

AS
BEGIN
DECLARE @SQL NVARCHAR(MAX) = N''
DECLARE @Select NVARCHAR(MAX) = N'SELECT ColA, ColB, ColC, ColD '
DECLARE @From NVARCHAR(MAX) = N'From Person'
DECLARE @Where NVARCHAR(MAX) = N''

IF @FirstName IS NOT NULL
    Begin
        Set @Where = @Where + 'FirstName = ''' + @FirstName + ''''
    End

IF @LastName IS NOT NULL
    Begin
        if len(@Where) > 0 
            Begin
                Set @Where = @Where + ' AND '       
            End

        Set @Where = @Where + 'LastName = ''' + @LastName + ''''
    End


IF @CompanyName IS NOT NULL
    Begin
        if len(@Where) > 0 
            Begin
                Set @Where = @Where + ' AND '       
            End

        Set @From = @From + ' inner join Company on person.companyid = company.companyid '
        Set @Where = @Where + 'company.CompanyName = ''' + @CompanyName + ''''
    End

Set @SQL = @Select + @From + @Where

EXECUTE sp_executesql @sql

END

Upvotes: 1

user3537535
user3537535

Reputation: 36

Another possibility, though the above is perhaps the most logical, is to create one or more "search" criteria tables into which you insert the users selections, and then perform LEFT JOINS against the search criteria. You would only be able to do this for cases in which there is an equivalency and preferably an small datatype such as int. For string comparisons, these kind of joins would be potentially dreadful as far as performance goes. The only problem with the above suggestion (dynamic-sql) is the possibility of plan cache bloat as each execution in which there is only one difference with an existing plan would cause a new plan to be generated.

Upvotes: 0

GarethD
GarethD

Reputation: 69789

To go down the dynamic SQL route you would use something like:

CREATE PROCEDURE dbo.SearchSomeTable 
    @FirstName  VARCHAR(20),
    @LastName   VARCHAR(20),
    @AnotherCol INT
AS
BEGIN

    DECLARE @SQL NVARCHAR(MAX) = N'SELECT SomeColumn FROM SomeTable WHERE 1 = 1',
            @ParamDefinition NVARCHAR(MAX) = N'@FirstName VARCHAR(20),
                                                @LastName VARCHAR(20),
                                                @AnotherCol INT';

    IF @FirstName IS NOT NULL
        @SQL = @SQL + ' AND FirstName = @FirstName';


    IF @LastName IS NOT NULL
        @SQL = @SQL + ' AND LastName = @LastName';

    IF @AnotherCol IS NOT NULL
        @SQL = @SQL + ' AND AnotherCol = @AnotherCol';

    EXECUTE sp_executesql @sql, @ParamDefinition, @FirstName, @LastName, @AnotherCol;

END

Otherwise you will need to use the OPTION (RECOMPILE) query hint to force the query to recompile each time it is run to get the optimal plan for the particular parameters you have passed.

Upvotes: 1

Related Questions