通过减少生成的唯一查询计划来优化动态SQL存储过程

Question

对于下面的查询，Server将根据传递的参数创建唯一的查询计划，是否有方法优化以下查询以减少查询计划的数量并优化查询。

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

Answer 1

是的，有一种方法可以改进它，正确使用参数，而不是使用字符串连接。您的方法将为参数的值的每个不同组合生成不同的查询计划，而不仅仅是每个不同的参数组合，这将生成数量级更多的查询计划。

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;

正如Aaron指出的那样，这也解决了包含单引号(')的任何参数值都将失败的问题。

然而，除此之外，正如Aaron还提到的，性能很可能是其他问题，如索引。

Answer 2

我不认为你是在责怪正确的事情，但是如果你真的认为是多个计划导致了CPU的激增，那么很容易将它转变为一个单一的计划策略：

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

让我们知道它是如何为您工作的；我很好奇您要实现什么索引，以使它在所有可能的参数组合中都能很好地执行。