sql-server - 通过减少生成的唯一查询计划来优化动态 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 还提到的那样，性能很可能取决于其他问题，例如索引。