SQL Server 2017 儲存過程和 TVF 性能問題

我有一個 SQL Server 2017 企業版實例，其中儲存過程大約需要。五分鐘執行。查看儲存過程程式碼後，我可以看到在 SELECT 列表和儲存過程主體的謂詞 WHERE 子句中多次引用了一個內聯標量 UDF。

我建議擁有程式碼的應用程序團隊應該重構他們的儲存過程，不要使用他們採用的內聯 UDF，並用 TVF 替換。在他們這樣做的同時，我注意到應用程序數據庫的數據庫兼容性級別仍然為 100，因此在通過數據遷移助手執行數據庫以檢查已棄用的功能和重大更改後，我將其提升到最新的 140 級別。

在將 UDF 替換為 TVF 並將數據庫兼容性級別從 100 提高到 140 之後，性能大大提高了，儲存過程現在可以在一分鐘內執行，但性能仍然不是我想要的。我希望有人能夠就我遺漏的任何明顯的事情提出建議，或者指出我可以做的任何其他事情的正確方向，以進一步優化程式碼或讓它表現得更好？執行計劃在這裡：https ://www.brentozar.com/pastetheplan/?id=ByrsEdRpr

儲存過程和函式的程式碼如下，儲存過程由應用程序呼叫：“EXEC dbo.CAOT_GetApplicationQueue;1”

/****** Object: StoredProcedure [dbo].[CAOT_GetApplicationQueue] ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[CAOT_GetApplicationQueue]
(@userID VARCHAR(50)='', @showComplete CHAR(1)='N', @JustMyQueue BIT=0, @ChannelId VARCHAR(10) = NULL )
AS
BEGIN
SELECT App.pkApplication ,
COALESCE(ApplicationReference, AlternateApplicationReference) AS ApplicationReference ,
ApplicationDate ,
Name ,
Telephone ,
[Address] ,
Email ,
CIN ,
Dob ,
CreatedDate ,
BusinessPhone ,
PostCode ,
MobilePhone ,
[Action] ,
ActionStatus ,
branchNumber ,
AccountNumber ,
AccountType ,
act.accountDescription,
IsNull( appstatus.DESCRIPTION ,'-- CREATED --') As LastStatus,
IsNull(appstatus.DAYS,'0') DaysSinceLastStatus ,
DATEDIFF(d,ApplicationDate, GETDATE()) DaysCreated,
InitialUserID,
IsNull(appstatus.STATUS,'-- MADE --') APPLICATIONSTATUS
FROM dbo.CAOT_Application (NOLOCK) app
LEFT OUTER JOIN dbo.CAOT_AccountType (NOLOCK) act
ON app.AccountType = act.AccountTypecode
LEFT OUTER JOIN [CAOT_GetAllApplicationStatus]() appstatus
ON app.pkApplication = appstatus.[PKAPPLICATION]
WHERE (IsNull(appstatus.STATUSCODE,'MADE') NOT IN ('CANCELLED','DECLINED','COMPLETE','EXPIRED')
OR @showComplete='Y') AND
(@JustMyQueue = 0 OR InitialUserID = @userID) AND
(@ChannelId IS NULL OR ChannelID = @ChannelId OR (@ChannelId = 'CBU' AND ChannelID IS NULL AND isCAO='N'))
ORDER BY CASE WHEN InitialUserID = @userid THEN 10 ELSE 900 END, ApplicationDate DESC
END
GO

/****** Object: UserDefinedFunction [dbo].[CAOT_GetAllApplicationStatus] ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[CAOT_GetAllApplicationStatus]() RETURNS
@return TABLE
(
[PKAPPLICATION] [int] NOT NULL,
[PKAPPLICATIONEVENT] INT,
[EVENTCREATEDDATE] [DATETIME] NULL,
[KEY] VARCHAR(12) NULL,
[DESCRIPTION] VARCHAR(200) NULL,
[CODE] VARCHAR(20) NULL,
[DAYS] VARCHAR(20) NULL,
[STATUS] VARCHAR(200) NULL,
[STATUSCODE] VARCHAR(50) NULL
)

AS
BEGIN

Declare @AppStatus table
(
[PKAPPLICATION] [int] NOT NULL,
[PKAPPLICATIONEVENT] INT,
[EVENTCREATEDDATE] [DATETIME] NULL,
[KEY] VARCHAR(12) NULL,
[DESCRIPTION] VARCHAR(200) NULL,
[CODE] VARCHAR(20) NULL,
[DAYS] VARCHAR(20) NULL,
[STATUS] VARCHAR(200) NULL,
[STATUSCODE] VARCHAR(50) NULL
)

INSERT INTO @AppStatus

SELECT
fkApplication,
ev.pkApplicationEvent As pkApplicationEvent,
ev.CreateDate As 'EventCreatedDate',
CONVERT(VARCHAR(12), evt.fkApplicationStatus) As 'KEY',
evt.EventDescription As 'DESCRIPTION',
evt.EventCode As 'CODE' ,
CONVERT(VARCHAR(20), DATEDIFF(d, ev.createdate, GETDATE()) ) As 'DAYS',
apps.StatusDescription As 'STATUS' ,
apps.StatusCode As 'STATUSCODE'
FROM dbo.CAOT_ApplicationEvent (NOLOCK) ev
INNER JOIN dbo.CAOT_EventType (NOLOCK) evt ON ev.fkEventType = evt.pkEventType
INNER JOIN dbo.CAOT_ApplicationStatus (NOLOCK) apps ON evt.fkApplicationStatus = apps.pkApplicationStatus

ORDER BY ev.CreateDate DESC, ev.pkApplicationEvent DESC

INSERT INTO @return
Select * from @AppStatus AllStatus
Where AllStatus.EVENTCREATEDDATE = ( Select Max(LatestAppStatus.EVENTCREATEDDATE) from @AppStatus LatestAppStatus where LatestAppStatus.PKAPPLICATION =AllStatus.PKAPPLICATION ) --Z On X.PKAPPLICATION = Z.PKAPPLICATION

RETURN
END

GO

您可以用視圖替換 TVF（或保留 TVF，但將視圖用於性能關鍵的儲存過程）：

CREATE VIEW CAOT_AllApplicationStatuses AS
 SELECT
   fkApplication,
   ev.pkApplicationEvent AS pkApplicationEvent,
   ev.CreateDate AS EventCreatedDate,
   CONVERT(VARCHAR(12), evt.fkApplicationStatus) As 'KEY',
   evt.EventDescription AS 'DESCRIPTION',
   evt.EventCode AS 'CODE',
   CONVERT(VARCHAR(20), DATEDIFF(d, ev.createdate, GETDATE())) AS 'DAYS',
   apps.StatusDescription AS 'STATUS',
   apps.StatusCode AS 'STATUSCODE'
 FROM
   dbo.CAOT_ApplicationEvent (NOLOCK) ev
   INNER JOIN dbo.CAOT_EventType (NOLOCK) evt ON ev.fkEventType = evt.pkEventType
   INNER JOIN dbo.CAOT_ApplicationStatus (NOLOCK) apps ON evt.fkApplicationStatus = apps.pkApplicationStatus
 WHERE
   NOT EXISTS
     (
     SELECT * FROM dbo.CAOT_ApplicationEvent AS LaterEvent WHERE EV.pkApplication = LaterEvent.pkApplication AND LaterEvent.pkApplication.CreateDate > EV.CreateDate
     )
 ORDER BY
   ev.CreateDate DESC, ev.pkApplicationEvent DESC

這只是 TVF 主要SELECT查詢的內容，WHERE第二個中的子句SELECT合併為NOT EXISTS. 我相信所有記錄CAOT_ApplicationEvent都有記錄CAOT_EventType和CAOT_ApplicationStatus; 如果不是這種情況，您需要在NOT EXISTS查詢中添加這些連接。

僅使用視圖而不是 TVF 可能會有所幫助，因為解析器會將視圖合併到最終查詢中，並丟棄未使用的部分；例如，這些對 的呼叫CONVERT()可能相對昂貴，但它們似乎未被使用。但是，頂級儲存過程中的複雜謂詞可能需要進行表掃描。讓我們試一試，看看它是否需要更多的工作！

引用自：https://dba.stackexchange.com/questions/255401