CAST to TIME in select query is taking long time to execute

Question

declare 
@fromLocalTime TIME = '06:00:00',
@toLocalTime TIME = '06:59:59',
@runDate AS DATETIME2
SET @runDate = GETUTCDATE()
DECLARE @notificationCreatedFromDate AS DATETIME2 = (SELECT DATEADD(day, -1, @runDate))
SELECT 
Col1,
Col2,
Col3,
...,
Global.UDF_ConvertDateUtcToTimeZone(@runDate, tz.TimeZone) AS 'UserLocalTime',
tz.TimeZone
FROM Notification tn
INNER JOIN Dilect d ON d.DilectID = tn.DilectID AND d.IsActive = 1
INNER JOIN NotificationType nt ON nt.NotificationTypeID = tn.NotificationTypeID AND nt.IsActive = 1
INNER JOIN Task t ON t.TaskID = tn.TaskID AND t.IsActive = 1 AND t.ApplicationID = @ApplicationID
INNER JOIN UserTimeZone userTz ON userTz.UserID = tn.UserID AND userTz.ClientId = t.ClientId
INNER JOIN Global.TimeZone tz ON tz.TimeZoneId = userTz.TimeZoneId AND tz.IsActive = 1
WHERE CAST (Global.UDF_ConvertDateUtcToTimeZone(@runDate, tz.TimeZone) AS TIME) BETWEEN @fromLocalTime AND @toLocalTime
AND tn.CompletedDate IS NULL
AND tn.IsActive = 1
AND d.CompletedDate IS NOT NULL
AND tn.Created_DT >= @notificationCreatedFromDate and tn.Created_DT <= @runDate

WHERE CAST (Global.UDF_ConvertDateUtcToTimeZone(@runDate, tz.TimeZone) AS TIME) BETWEEN @fromLocalTime AND @toLocalTime

I have this previously written query which was working fine till yesterday. Suddenly this query taking long time to execute(10 mins now - 10 Seconds previously). I have added suggested index previously on the columns. The issue is due to the highlighted line in the above code(WHERE clause). If I keep that line code then query takes 10 mins to execute and if remove it executes in 10 seconds and both returns 300K records. I have gone through few articles regarding how CAST impacts select statements- but can't relate to the scenarios. I have also tried moving all the code except the highlighted line to outer query and given the highlighted query in where clause but cant see any difference in performance.

Can someone tell me what can be the issue with this line(WHERE clause) and suggest something to fix this.

Answer 1

Global.UDF_ConvertDateUtcToTimeZone(@runDate, tz.TimeZone) AS 'UserLocalTime',

It is very difficult for SQL Server to optimize a scalar user-defined function, which generally has to be called for every row. If the function itself is inherently inefficient, your problem just multiplied and will keep multiplying as your data gets larger. Microsoft has tried to address this in modern versions, but there are a lot of restrictions and limitations and they have to fix something in almost every cumulative update.

Instead of relying on the engine to eventually compensate for this inefficiency, a much better strategy is to write inline (not multi-statement!) table-valued functions from the start. This leads to folding the query logic into the outer query and gives SQL Server both better statistics and many more optimization paths to consider.

Let's say your scalar function looks like this:

CREATE FUNCTION dbo.DoAThingToOneRow(@i int)
RETURNS int
AS
BEGIN
  RETURN (SELECT [output] = @i + 1);
END

And you call it like this:

SELECT [object_id], 
       [output] = dbo.DoAThingToOneRow([object_id])
  FROM sys.all_objects;

Change it to this:

CREATE FUNCTION dbo.DoAThingToAllRows(@i int)
RETURNS TABLE WITH SCHEMABINDING
AS
  RETURN (SELECT [output] = @i + 1);

And call it like this:

SELECT o.[object_id], 
       f.[output]
FROM sys.all_objects AS o
CROSS APPLY dbo.DoAThingToAllRows(o.[object_id]) AS f;

• Example db<>fiddle

I could go into the differences between the plans and execution statistics but you should do this on your own with your own function to get a much more realistic comparison. You can check things like sys.dm_exec_query_stats and sys.dm_exec_function_stats.

• Read more:
  • Benefits of SCHEMABINDING
  • Refactor scalar UDF to inline TVF to improve performance