Why is this sql script so slow and how to make it lightning fast?

Question

Here is my sql script

CREATE TABLE dbo.calendario (
    datacal DATETIME NOT NULL PRIMARY KEY,
    horautil BIT NOT NULL DEFAULT 1
);

-- DELETE FROM dbo.calendario;

DECLARE @dtmin DATETIME, @dtmax DATETIME, @dtnext DATETIME;

SELECT
    @dtmin = '2014-03-11 00:00:00'
    , @dtmax = '2030-12-31 23:50:00'
    , @dtnext = @dtmin;

WHILE (@dtnext <= @dtmax) BEGIN
    INSERT INTO dbo.calendario(datacal) VALUES (@dtnext);
    SET @dtnext = DATEADD(MINUTE, 10, @dtnext);
END;

Basically, I want to create a table with time intervals of 10 minutes each. The loop inserts a lot of records, but I thought I would do it quickly. It takes a few minutes ...

I am using sql server 2008 r2.

Any help is appreciated.

Answer 1

You should avoid loops, etc. and try to approach this set. (google for "RBAR SQL")

Anyway, this works after 1 second on my laptop:

DROP TABLE dbo.calendario 
GO

CREATE TABLE dbo.calendario (
    datacal DATETIME NOT NULL PRIMARY KEY,
    horautil BIT NOT NULL DEFAULT 1
);

-- DELETE FROM dbo.calendario;

DECLARE @dtmin DATETIME, @dtmax DATETIME, @intervals int

SELECT @dtmin = '2014-03-11 00:00:00'
     , @dtmax = '2030-12-31 23:50:00'


SELECT @intervals = DateDiff(minute, @dtmin, @dtmax) / 10

;WITH 
  L0   AS(SELECT 1 AS c UNION ALL SELECT 1),
  L1   AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
  L2   AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
  L3   AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
  L4   AS(SELECT 1 AS c FROM L3 AS A, L3 AS B),
  L5   AS(SELECT 1 AS c FROM L4 AS A, L4 AS B),
  L6   AS(SELECT 1 AS c FROM L5 AS A, L5 AS B),
  Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM L6)

INSERT INTO dbo.calendario(datacal)
SELECT DateAdd(minute, 10 * (n - 1), @dtmin)
  FROM Nums
 WHERE n BETWEEN 1 AND @intervals + 1

-- SELECT * FROM dbo.calendario ORDER BY datacal

Answer 2

This code takes 23 seconds on my machine (and most of it when sorting)

DECLARE @DateMin AS datetime = '2014-03-11 00:00:00';
DECLARE @DateMax AS datetime = '2030-12-31 23:50:00';

DECLARE @Test AS Table (
   datacal DATETIME NOT NULL PRIMARY KEY
);

WITH Counter AS (
    SELECT ROW_NUMBER() OVER (ORDER BY a.object_id) -1 AS Count
    FROM sys.all_objects AS a
         CROSS JOIN sys.all_objects AS b
)
INSERT INTO @Test (datacal)
SELECT DATEADD(minute, 10 * Count, @DateMin)
FROM Counter
WHERE DATEADD(minute, 10 * Count, @DateMin) <= @DateMax

Answer 3

. , .

DECLARE @StartDate DATETIME = '2014-03-11 00:00:00';
DECLARE @EndDate DATETIME = '2030-12-31 00:00:00';

--CTE of days in the Start/End date range.
WITH DaysTable AS
(
    SELECT @StartDate AS CalendarDate
    UNION ALL
    SELECT DATEADD(dd, 1, CalendarDate)
    FROM DaysTable
    WHERE CalendarDate < @EndDate
) 
--CTE of 10 min intervals for one day.
,MinsTable AS
(
    SELECT CAST(0 AS DATETIME) MinutesDate
    UNION ALL
    SELECT DATEADD(MINUTE, 10, mt.MinutesDate)
    FROM MinsTable mt
    WHERE MinutesDate < DATEADD(MINUTE, -10, CAST(0 AS DATETIME) + 1)
) 
SELECT DATEADD(MINUTE, DATEDIFF(MINUTE, CAST(0 AS DATETIME), mt.MinutesDate), dt.CalendarDate) Every10Min
INTO #TestTable
FROM MinsTable mt
--Cross join for all 10 min intervals of all days in date range.
CROSS JOIN DaysTable dt
ORDER BY DATEADD(MINUTE, DATEDIFF(MINUTE, CAST(0 AS DATETIME), mt.MinutesDate), dt.CalendarDate)
OPTION (MAXRECURSION 32767)

SELECT *
FROM #TestTable