sql - 选择查询中的 CAST to TIME 需要很长时间才能执行

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

我有这个以前写的查询,直到昨天都运行良好。突然,这个查询需要很长时间才能执行(现在 10 分钟 - 之前 10 秒)。我之前在列上添加了建议的索引。问题是由于上面代码中突出显示的行(WHERE 子句)。如果我保留该行代码,则查询需要 10 分钟才能执行,如果删除它会在 10 秒内执行,并且都返回 300K 记录。我已经阅读了几篇关于 CAST 如何影响选择语句的文章——但与场景无关。我还尝试将除突出显示的行之外的所有代码移至外部查询,并在 where 子句中给出突出显示的查询,但看不到任何性能差异。

有人可以告诉我这行(WHERE 子句)有什么问题,并提出解决这个问题的建议。

回答1

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

SQL Server 很难优化一个标量的用户定义函数,它通常必须为每一行调用。如果函数本身本质上是低效的,那么您的问题就会成倍增加,并且随着数据变大而不断增加。 Microsoft 已尝试在现代版本中解决此问题,https://docs.microsoft.com/en-us/sql/relational-databases/user-defined-functions/scalar-udf-inlining#inlineable-scalar-udfs-requirements,他们必须在https://support.microsoft.com/en-us/topic/kb4538581-fix-scalar-udf-inlining-issues-in-sql-server-2019-f52d3759-a8b7-a107-1ab9-7fbee264dd5d

与其依靠引擎最终弥补这种低效率,更好的策略是从一开始就编写内联(而不是多语句!)table 值函数。这导致将查询逻辑折叠到外部查询中,并为 SQL 服务器提供更好的统计信息和更多需要考虑的优化路径。

假设您的标量函数如下所示:

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

你这样称呼它:

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

将其更改为:

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

并这样称呼它:

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

我可以探讨计划和执行统计数据之间的差异,但您应该使用自己的函数自行执行此操作,以获得更真实的比较。您可以检查 sys.dm_exec_query_statssys.dm_exec_function_stats 之类的内容。

相似文章

最新文章