SQL Server - Efficient generation of dates in a range

Question

Using SQL Server 2016.

I have a stored procedure that produces a list of options against a range of dates. Carriage options against days for clarity but unimportant to the specifics here.

The first step in the stored procedure generates a list of dates to store additional data against, and generating this list is taking substantially longer than the balance of the code. While this process is individual short, the number of calls means that this one piece of code is putting the system under more load than anything else.

With that in mind I have been testing efficiency of several options.

Iterative common table expression:

CREATE FUNCTION [dbo].[udf_DateRange_CTE] (@StartDate DATE,@EndDate DATE)
RETURNS @Return TABLE (Date DATE NOT NULL)
AS
    BEGIN
        WITH    dates(date)
                  AS (SELECT    @StartDate [Date]
                      UNION ALL
                      SELECT    DATEADD(dd, 1, [Date])
                      FROM      dates
                      WHERE     [Date] < @EndDate
                     )
            INSERT  INTO @Return 
            SELECT  date
            FROM    dates
            OPTION  (MAXRECURSION 0)
        RETURN 
    END

A while loop:

CREATE FUNCTION [dbo].[udf_DateRange_While] (@StartDate DATE,@EndDate DATE)
RETURNS @Retun TABLE (Date DATE NOT NULL,PRIMARY KEY (Date))
AS
    BEGIN
        WHILE @StartDate <= @EndDate
            BEGIN
                INSERT  INTO @Retun
                VALUES  (@StartDate)
                SET @StartDate = DATEADD(DAY,1,@StartDate)
            END
        RETURN 
    END

A lookup from a pre-populated table of dates:

CREATE FUNCTION [dbo].[udf_DateRange_query] (@StartDate DATE,@EndDate DATE)
RETURNS @Return TABLE (Date DATE NOT NULL)
AS
    BEGIN
        INSERT  INTO @Return
        SELECT  Date
        FROM    DateLookup
        WHERE   Date >= @StartDate
                AND Date <= @EndDate
        RETURN 
    END

In terms of efficiency I have test generating a years worth of dates, 1000 times and had the following results:

CTE: 10.0 Seconds
While: 7.7 Seconds
Query: 2.6 Seconds

From this the query is definitely the faster option but does require a permanent table of dates that needs to be created and maintained. This means that the query is no loner "self-contained" and it would be possible to request a date outside of the given date range.

Does anyone know of any more efficient ways of generating dates for a range, or any optimisation I can apply to the above?

Many thanks.

it is self contained if it is all dates but that is a lot of dates — paparazzo, Apr 03 '18 at 08:51

PSK · Accepted Answer · 2018-04-03T09:16:09.807

2

You can try like following. This should be fast compared CTE or WHILE loop.

DECLARE @StartDate DATETIME = Getdate() - 1000 
DECLARE @EndTime DATETIME = Getdate() 

SELECT * 
FROM   (SELECT @StartDate + RN AS DATE 
        FROM   (SELECT ROW_NUMBER() 
                         OVER ( 
                           ORDER BY (SELECT NULL)) RN 
                FROM   master..[spt_values]) T) T1 
WHERE  T1.DATE <= @EndTime 
ORDER  BY DATE

Note: This will work for day difference <= 2537 days

If you want to support more range, you can use CROSS JOIN on master..[spt_values] to generate range between 0 - 6436369 days like following.

DECLARE @StartDate DATETIME = Getdate() - 10000
DECLARE @EndTime DATETIME = Getdate() 
SELECT @StartDate + RN AS DATE FROM
(   
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN 
    FROM   master..[spt_values] T1
    CROSS JOIN  master..[spt_values] T2
) T 
WHERE RN <= DATEDIFF(DAY,@StartDate,@EndTime)

edited Apr 03 '18 at 09:16

answered Apr 03 '18 at 08:53

PSK

17,547
5
32
43

I have checked this using the same methodology as my originals and is almost as efficient as the table lookup I was using. Coming in at 2.7 seconds. I've up voted this, and if nothing better comes in I'll mark as accepted answer. Many thanks, this is a great option. – Matthew Baker Apr 03 '18 at 09:41
I've just used this solution, and pushed it to our testers. One minor point I found though, the list doesn't include the start date. I added a SELECT @Startdate UNION ALL to your solution and it worked lovely. – Matthew Baker Apr 03 '18 at 14:37
That's true, but requires a -1 operation on every line, and a +1 operation on the WHERE. Seriously minor efficiency hit, but the union requires less resources. So little in it though. – Matthew Baker Apr 05 '18 at 10:26

SQL Server - Efficient generation of dates in a range

1 Answers1