CODEWITHSUNDEEP
sqlsql-serversql-server-2008
David Duman
David Duman

Reputation: 6656

Using user defined function (UDF) in where clause more than once with running the function only once

I've a table to keep list of tasks and these tasks need to be processed on specific date and time. Tricky part is these tasks are recursive running time must be calculated based on 5 different parameters.

Calculating running time through UDF was simple part:

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

My final task query was this:

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

My problem is running same function 2 times in where clause. I need a solution to use calculated value as alias in where clause.

Upvotes: 1

Views: 9078

Answers (3)

Aaron Bertrand
Aaron Bertrand

Reputation: 280431

Why don't you do:

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;

I am fairly certain though that SQL Server will collapse it to the same thing, and only call the function once and not twice. Depending on the nature of the function, it might still execute once per row, though. Have you considered converting the UDF to an inline table-valued function? These generally optimize much better.

Another option would be (as described in a comment):

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);

Now you can say:

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;

You could even filter further first:

DELETE @d WHERE post >= @now;

This allows you to eliminate the WHERE above.

All told it might still optimize the same, but could be worth a shot at slightly better performance (way too many variables for anyone here to predict from 30,000 feet).

Upvotes: 4

abhIShek BandI
abhIShek BandI

Reputation: 1

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()

Upvotes: -1

David Duman
David Duman

Reputation: 6656

Cross Apply is what I needed. Here is the final query with Cross Apply.

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

Upvotes: 3

Related Questions