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

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

CodePudding user response：

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.

CodePudding user response：

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