Cross apply function running forever

Question

I am using a cross apply in a stored procedure to expand range of dates to individual dates.

select *
from #tbl_tempOutPut as st
cross apply dbo.explodeDates(st.startdate,st.enddate) as dt

Table #tbl_tempOutPut looks like this:

ID  Des startdate   enddate     kindex      
A   att 2003-12-04  2004-01-03  1
A   att 2004-01-04  2004-02-03  1

The function dbo.explodeDates is a previous accepted answer.

My problem is that the query runs forever and does not return.

Many thanks,

Are you aware that the function `dbo.explodeDates` will be called for each row in the table? You should consider using SQL/CLR instead since it would probably perform way better in a scenario like this. — Torben Koch Pløen, May 22 '12 at 18:57
What happens if you just call `SELECT dbo.explodeDates('2004-01-04', '2004-02-03)`? What happens if you change it to `SELECT dbo.explodeDates('2004-04-01', '2004-03-02)`? — Lieven Keersmaekers, May 22 '12 at 18:58
@Lieven individual calls of the function return date list as expected — Johnyy, May 23 '12 at 01:33

score 2 · Answer 1 · answered May 22 '12 at 19:36

2

Rewrite explodeDates as an inline table-valued function. This will almost certainly make an enormous difference.

CREATE FUNCTION dbo.explodeDates (
  @StartDate    char(10),
  @EndDate      char(10)
) RETURNS TABLE AS RETURN SELECT
  CONVERT(datetime,@StartDate)+n-1 AS [Date]
  FROM Numbers
  WHERE Number <= DATEDIFF(day,@StartDate,CONVERT(datetime,@EndDate)+1)

answered May 22 '12 at 19:36

Steve Kass

7,144
20
26

As a matter of fact, the answer the OP is referring to suggests using an inline TVF too. – Andriy M May 22 '12 at 21:36
The function explodeDates is always tabled valued function. Also using a numbers table, although the table looks a bit different. – Johnyy May 23 '12 at 01:34
@Johnyy: Not sure what you mean here, but maybe you don't realize that there are two kinds of table-valued functions in SQL Server: multi-statement and inline. They are handled completely differently, even though they might not seem so different. When inline functions are used, like I suggest here, there are many more possibilities for the optimizer. Have you tried the inline version? I think you will find it much better. – Steve Kass May 23 '12 at 05:00
I think I finally saw the cause of the confusion. The OP was referring to the accepted answer and the function called `dbo.explodeDates`, which *is* an inline TVF. But their link opens at a different answer, suggesting a different function (`dbo.ListDates`), and that function is a multi-statement TVF. Was it because of *that* function that you decided to post your answer? – Andriy M May 31 '12 at 05:58
Andriy: Yes. I assumed that the OP linked to the function he was using, despite the difference in function name. – Steve Kass May 31 '12 at 18:54

Cross apply function running forever

1 Answers1