在where子句中多次使用用户定义函数(UDF)，只运行一次函数

Question

我有一个表来保存任务列表，这些任务需要在特定的日期和时间进行处理。棘手的是，这些任务都是递归的，运行时间必须根据5个不同的参数计算。

通过UDF计算运行时间很简单：

Function dbo.task_next_run(
    @task_type varchar(10),
    @task_schedule_day_of_week varchar(20),
    @task_schedule_time varchar(20),
    @task_period smallint,
    @last_run datetime
)
Returns datetime
...
...
...
Return @next_run

我最后的任务查询是：

SELECT id, 
       task_name, 
       last_run 
From tasks 
Where dbo.task_next_run
(
   task_type, @task_schedule_day_of_week, 
   @task_schedule_time, @task_period, @last_run
) < getdate() and 
dbo.task_next_run
(
     task_type, @task_schedule_day_of_week, 
     @task_schedule_time, @task_period, @last_run
) > last_run

我的问题是在where子句中运行相同的函数两次。我需要一个在where子句中使用计算值作为别名的解决方案。

Answer 1

为什么不这样做：

DECLARE @now DATETIME = CURRENT_TIMESTAMP;
SELECT id, task_name, last_run
FROM 
(
  SELECT id, task_name, last_run, d = dbo.task_next_run
  (task_type, @task_schedule_day_of_week, @task_schedule_time, @task_period, @last_run)
  From tasks 
) AS x
WHERE x.d < @now
AND x.d > x.last_run;

不过，我相当确信SQL Server会将其折叠为相同的东西，并且只调用该函数一次，而不是两次。不过，根据函数的性质，它仍然可能每行执行一次。您是否考虑过将UDF转换为内联表值函数？这些通常优化得更好。

另一种选择是（如评论中所述）：

DECLARE @now DATETIME = CURRENT_TIMESTAMP;
DECLARE @d TABLE(task_type INT PRIMARY KEY, post DATETIME);
INSERT @d SELECT task_type, dbo.task_next_run(task_type, @variables)
  FROM (SELECT task_type FROM dbo.tasks GROUP BY task_type);

现在你可以说：

SELECT t.id, t.task_name, t.last_run
FROM dbo.tasks AS t
INNER JOIN @d AS d
ON t.task_type = d.task_type
AND t.last_run > d.post
WHERE d.post < @now;

你甚至可以先进一步过滤：

DELETE @d WHERE post >= @now;

这允许您消除上面的WHERE。

总的来说，它可能仍然会优化相同的性能，但可能值得尝试一下稍微更好的性能（这里的任何人都无法从30000英尺的高度预测太多的变量）。

Answer 2

交叉应用正是我所需要的。这是交叉应用的最后一个查询。

SELECT id, task_name, last_run, func.next_run
FROM tasks
Cross Apply (Select dbo.task_next_run(task_type, @task_schedule_day_of_week, @task_schedule_time, @task_period, @last_run) as next_run) as func
WHERE 
func.next_run < getdate() and
func.next_run > last_run

Answer 3

SELECT id, 
       task_name, 
       last_run 
       From tasks 
WHERE dbo.task_next_run
      (
            task_type, @task_schedule_day_of_week, 
            @task_schedule_time, @task_period, @last_run
      ) BETWEEN  last_run AND getdate()