對于下面的查詢，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
uj5u.com熱心網友回復：
是的，有一種方法可以改進它，正確使用引數，而不是使用字串連接。您的方法將為引數值的每個不同組合生成不同的查詢計劃，而不僅僅是每個不同的引陣列合，這將生成更多數量級的查詢計劃。
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 還提到的那樣，性能很可能取決于其他問題，例如索引。
uj5u.com熱心網友回復：
我不認為你在責備正確的事情，但如果你真的認為多個計劃導致你的 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
讓我們知道這對您有何影響；我很好奇您將實施什么索引以使其對所有可能的引陣列合都表現良好。
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/455696.html