I have a SQL Server 2017 Enterprise Edition instance where a stored procedure was taking approx. five minutes to execute. After reviewing the stored procedure code, I could see that there was an inline scalar UDF referenced several times in the SELECT list and also in the predicate WHERE clause of the stored procedure body.
I advised the application team owning the code that they should refactor their stored proc not to use an inline UDF which they took onboard and replaced with a TVF. Whilst they were doing this, I noticed that the application database still had database compatibility level 100 so I raised this to the latest level of 140 after running the database through the Data Migration Assistant to check for deprecated features and breaking changes.
Following the replacement of the UDF for a TVF and raising the database compatibility level from 100 to 140, the performance has increased greatly and the stored proc now executes in under a minute but performance is still not where I'd like to it be. I'm hoping someone might be able to advise of anything obvious I'm missing or point me in the right direction of anything else I can do to further optimise the code or get this to perform better? The execution plan is here: https://www.brentozar.com/pastetheplan/?id=ByrsEdRpr
The code for the stored proc and function are as below and the stored procedure is called by the application as such: "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