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

Question

对于下面的查询，SQL 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 Bertrand所指出的，这也解决了任何包含单引号('(的参数值都会失败的问题。

然而，除此之外，正如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

让我们知道这对你来说是怎么回事；我很好奇你将实现什么索引，以使其在所有可能的参数组合中都表现良好。