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 date intervals of 10 minutes each. The loop inserts a lot of records, but I thought it would be fast to execute that. It takes several minutes...

I'm using sql server 2008 r2.

Any help is appreciated.

Answer 1

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

Anyway, this runs in 1 sec 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 in 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