Faster approach to enter rows in a time table in ms sql

Question

I need to fill a time table to use it for joining the data in reporting services. Generally I do this with this code:

TRUNCATE TABLE tqTimeTable

DECLARE @CNT int
DECLARE @DATE datetime
DECLARE @END int

SET @CNT  = 1
SET @DATE = 25567 -- 01.01.1970
SET @END  = 20000 -- + 20k days => years 2024

WHILE(@CNT < @END)
BEGIN
  INSERT INTO tqTimeTable (Tag, Monat, Jahr)
  VALUES (DATEADD(day,@CNT,@DATE), MONTH(DATEADD(day,@CNT,@DATE)), YEAR(DATEADD(day,@CNT,@DATE)))
  SET @CNT = @CNT + 1
END;

But this takes a while (on my test system around 2 minutes) so I hope someone had the same issue and solved it better then me. As I fire this statement from a .NET connection I need a faster solution or if there isn't one to raise the timeout of my connection.

Weird. On my system this takes around 5 seconds. And on SQLFiddle about 13s: http://www.sqlfiddle.com/#!3/5965b/1 — Cristian Lupascu, Jun 13 '13 at 09:34

Martin Smith · Accepted Answer · 2013-06-13T09:52:14.730

Simply adding

BEGIN TRAN
WHILE(@CNT < @END)
BEGIN
  INSERT INTO tqTimeTable (Tag, Monat, Jahr)
  VALUES (DATEADD(day,@CNT,@DATE), MONTH(DATEADD(day,@CNT,@DATE)), YEAR(DATEADD(day,@CNT,@DATE)))
  SET @CNT = @CNT + 1
END;
COMMIT

will speed it up as you are doing many individual commits (that all require the log to be written to disc). I would do a set based insert in a single statement though.

   WITH E1(N) AS 
    (
        SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
        SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
        SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
    )                                       -- 1*10^1 or 10 rows
    , E2(N) AS (SELECT 1 FROM E1 a, E1 b)   -- 1*10^2 or 100 rows
    , E4(N) AS (SELECT 1 FROM E2 a, E2 b)   -- 1*10^4 or 10,000 rows
    , E8(N) AS (SELECT 1 FROM E4 a, E4 b)   -- 1*10^8 or 100,000,000 rows
    , NUMS(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM  E8)
INSERT INTO tqTimeTable
            (Tag,
             Monat,
             Jahr)
SELECT DATEADD(day, N, @DATE),
       MONTH(DATEADD(day, N, @DATE)),
       YEAR(DATEADD(day, N, @DATE))
FROM   NUMS
WHERE  N <= 20000

The transaction was it, works in 1 second now...Your set based statement popup an error here (wrong syntax close to WITH), but the time is more then acceptable fast now, so no more need to solve this... — YvesR, Jun 13 '13 at 12:13
@YvesR - If it is preceded by any other statements they need to be terminated with a semicolon.# — Martin Smith, Jun 13 '13 at 12:24
Yes, it works now with correct ; speration. But honestly, this query is to much rocket science for me :) Do not understand at all how it works. — YvesR, Jun 13 '13 at 15:38

Faster approach to enter rows in a time table in ms sql

1 Answers1