我有一个 SQL Server 2017 企业版实例,其中存储过程大约需要。五分钟执行。查看存储过程代码后,我可以看到在 SELECT 列表和存储过程主体的谓词 WHERE 子句中多次引用了一个内联标量 UDF。
我建议拥有代码的应用程序团队应该重构他们的存储过程,不要使用他们采用的内联 UDF,并用 TVF 替换。在他们这样做的同时,我注意到应用程序数据库的数据库兼容性级别仍然为 100,因此在通过数据迁移助手运行数据库以检查已弃用的功能和重大更改后,我将其提升到最新的 140 级别。
在将 UDF 替换为 TVF 并将数据库兼容性级别从 100 提高到 140 之后,性能有了很大的提高,现在存储的 proc 可以在不到一分钟的时间内执行,但性能仍然不是我想要的。我希望有人能够就我遗漏的任何明显的事情提出建议,或者指出我可以做的任何其他事情的正确方向,以进一步优化代码或让它表现得更好?执行计划在这里: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,但将视图用于性能关键的存储过程):
这只是 TVF 主要
SELECT
查询的内容,WHERE
第二个中的子句SELECT
合并为NOT EXISTS
. 我相信所有记录CAOT_ApplicationEvent
都有记录CAOT_EventType
和CAOT_ApplicationStatus
; 如果不是这种情况,您需要在NOT EXISTS
查询中添加这些连接。仅使用视图而不是 TVF 可能会有所帮助,因为解析器会将视图合并到最终查询中,并丢弃未使用的部分;例如,这些对 的调用
CONVERT()
可能相对昂贵,但它们似乎未被使用。但是,顶级存储过程中的复杂谓词可能需要进行表扫描。让我们试一试,看看它是否需要更多的工作!主要关注领域
(@JustMyQueue = 0 OR InitialUserID = @userID)
. 尝试将它们转换为 UNION ALL 或将它们放入 #temp 表并加入。例子,
现在只需加入#Status
编辑 1:
在您的 UDF 中传递 Parameter 后,您必须注意到性能改进。可以进一步改进。如果可能,将 RETURNS VARCHAR(100) 减少到 VARCHAR(50) 左右。
不要忘记#Status 加入技巧。您应该更新您的脚本和查询计划。
阅读http://sommarskog.se/dyn-search.html后
您可以将查询拆分为多个部分,例如:
这可能会在 CAOT_Application 表中搜索更少的行。