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

Question

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.

Using GetDate() within a query is chasing a moving target, impacts performance, and may produce curious results, e.g. as the date changes. It is almost always a better idea to capture the current date/time in a variable and then use that value as needed. This is more important across multiple statements as in a stored procedure. The most common reason to use GetDate() multiple times is when capturing the start and end times for a long running operation. — HABO
– HABO, Commented Aug 30, 2012 at 18:01

Aaron Bertrand · Accepted Answer · 2012-08-30 18:21:51Z

4

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

edited Aug 30, 2012 at 18:21

answered Aug 30, 2012 at 17:23

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

David Duman Over a year ago

your idea is almost same thing and works.. about execute for each row; in my case it has to be run for each row to calculate next_run (recursive rules could be changed). So it doesnt make difference in my case..

Aaron Bertrand Over a year ago

Since 4 of the 5 inputs come from variables, it could be feasible to first generate a table holding all of the possible outcomes given a finite set of task_type values. Assuming there is a small number of them.

David Duman Over a year ago

I got your point, it might be smart decision.. I'm expecting couple hundred tasks.. thanks.

David Duman · Accepted Answer · 2012-08-30 17:21:38Z

3

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

answered Aug 30, 2012 at 17:21

David Duman

6,6567 gold badges47 silver badges55 bronze badges

Comments

Tim Henigan · Accepted Answer · 2012-08-30 17:49:21Z

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

edited Aug 30, 2012 at 17:49

Tim Henigan

62.6k11 gold badges87 silver badges79 bronze badges

answered Aug 30, 2012 at 17:25

abhIShek BandI

1

1 Comment

Aaron Bertrand Over a year ago

NO. BETWEEN is not the same as > and < ... it is the same as >= and <=. This changes the meaning of the query. Also please read this: sqlblog.org/blogs/aaron_bertrand/archive/2011/10/19/…

Collectives™ on Stack Overflow

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

3 Answers 3

3 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related