Optimize dynamic SQL stored procedure by reducing the unique query plans generated

user_17495827 0
Viewed 150

For the below query, SQL Server is creating a unique query plan depending on the parameter which is passed, Is there any way to optimize the below query to reduce the number of query plans and optimize the query.

CREATE PROCEDURE [dbo].[Foo_search]
    @ItemID INT, 
    @LastName VARCHAR(50), 
    @MiddleName VARCHAR(40), 
    @FirstName VARCHAR(50) 
AS 
BEGIN 
    DECLARE @Sql NVARCHAR(max) 

    SELECT @Sql = N 'Select ID, FirstName, FamilyName, MiddleName, MaidenName, Email,  From Employees Where DeletedOn Is Null ' + 
        CASE WHEN @LastName IS NULL OR @LastName = '' THEN '' ELSE ' And FamilyName=''' + @LastName + ''' ' END + 
        CASE WHEN @MiddleName IS NULL OR @MiddleName = '' THEN '' ELSE ' And MiddleName=''' + @MiddleName + ''' ' END + 
        CASE WHEN @FirstName IS NULL OR @FirstName = '' THEN '' ELSE ' And FirstName=''' + @FirstName + ''' ' END + 
        CASE WHEN @ItemID IS NOT NULL AND @ItemID > 0 THEN ' And ItemID=' + CONVERT(VARCHAR(10), @ItemID) + ' ' ELSE ' ' END 

    EXEC Sp_executesql @sql 
END
stored-procedures tsql sql-server
2 Answers

Yes there is a way you can improve it, use the parameters correctly, rather than using string concatenation. Your method will generate a different query plan for every different combination of values of parameter, rather than just every different combination of parameters, which will generate orders of magnitude more query plans.

DECLARE @Sql nvarchar(max) = N'
Select ID, FirstName, FamilyName, MiddleName, MaidenName, Email
From Employees
Where DeletedOn Is Null
'
    + CASE WHEN @LastName <> '' THEN ' And FamilyName = @LastName' ELSE '' END
    + CASE WHEN @MiddleName <> '' THEN ' And MiddleName = @MiddleName' ELSE '' END
    + CASE WHEN @FirstName <> '' THEN ' And FirstName = @FirstName' ELSE '' END
    + CASE WHEN @ItemID > 0 THEN ' And ItemID = @ItemID' ELSE '' END;

EXEC sp_executesql
  @Sql,
  N'@LastName varchar(50), @MiddleName varchar(40), @FirstName varchar(50)',
  @LastName = @LastName,
  @MiddleName = @MiddleName,
  @FirstName = @FirstName;

And as Aaron Bertrand points out, this also fixes the issue where any parameter value containing a single quote (') will fail.

Beyond this however, as Aaron also mentions, the performance is most likely down to other issues such as indexing.

user_11274280

I don't think you're blaming the right thing, but if you really think that multiple plans are causing your CPU spikes, it's easy to change this back to a single plan strategy:

ALTER PROCEDURE dbo.Foo_search
    @ItemID INT, 
    @LastName VARCHAR(50), 
    @MiddleName VARCHAR(40), 
    @FirstName VARCHAR(50) 
AS 
BEGIN 
  Select ID, FirstName, FamilyName, MiddleName, MaidenName, Email
  From dbo.Employees 
  Where DeletedOn Is Null
    AND (FamilyName = @LastName OR @LastName IS NULL)
    AND (MiddleName = @MiddleName OR @MiddleName IS NULL)
    AND (FirstName = @FirstName OR @FirstName IS NULL)
    AND (ItemID = @ItemID OR @ItemID IS NULL);
END

Let us know how that works out for you; I'm curious what index you're going to implement to make that perform well for all possible parameter combinations.

user_613050
Related