How to optimize a cycle in stored procedure [T-SQL]

Question

I have this stored procedure in T-SQL for splitting a DATETIME into shifts.

I would divide the DATETIME START and DATETIME END into shifts (for example, one shift for hours or one shift every 15 minutes). I should this shift into a temporary table for use it in another query.

So for this I have create this cycle:

BEGIN
      SET @DATASTART = '2014-11-28 06:00:00'
      SET @DATAEND = '2014-11-28 21:00:00'
    --DICHIARO DUE VARIABILI DATA CHE UTILIZZO
    --PER MANTENERE I DUE SHIFT
    DECLARE @DataFirstShift as DATETIME
    DECLARE @DataLastShift as DATETIME
    --DICHIARO UN CONTATORE PER POPOLARE IL CAMPO ID
    DECLARE @Contatore as INT
    SET @Contatore = 0
    --SETTO LA DATA FIRSTSHIFT A DATASTART
    SET @DataFirstShift = @DATA_START

    WHILE(@DataFirstShift <= @DATA_END)
        BEGIN
            --POPOLO LA DATA LAST CON UN ORA IN PIU RISPETTO ALLA PRIMA DATA
            IF @Shift LIKE 'All'
                BEGIN 
                    SET @DataLastShift = DATEADD(HOUR,1,@DataFirstShift)
                END
            ELSE 
                BEGIN 
                    SET @DataLastShift = DATEADD(MINUTE,15,@DataFirstShift)
                END
            INSERT INTO @TemporaryTable2 (ID,DATASTART,DATAEND)
            VALUES (@Contatore,@DataFirstShift,@DataLastShift)
            SET @DataFirstShift=@DataLastShift
            --INCREMENTO IL CONTATORE
            SET @Contatore+=1
        END
END

This method works but I this cycle is slow. I want to know if exist a method faster than it.

Can someone help me? Regards

check this : http://stackoverflow.com/questions/9140308/get-all-dates-in-date-range-in-sql-server — Deep, Nov 28 '14 at 11:14

score 0 · Accepted Answer · edited May 23 '17 at 11:51

Try below :

--param of SP
DECLARE @DATASTART DATETIME = '2014-11-28 06:00:00'
DECLARE @DATAEND DATETIME = '2014-11-28 21:00:00'
DECLARE @Shift VARCHAR(50) = 'All'

--body
DECLARE @TemporaryTable2 TABLE
  (
     id        INT,
     startdate DATETIME,
     enddate   DATETIME
  )

DECLARE @id INT = 0

IF @Shift LIKE 'All'
BEGIN
    INSERT INTO @TemporaryTable2
    SELECT N.number + 1 AS id,
           startdate,
           enddate
    FROM   master..spt_values N
           CROSS apply (SELECT Dateadd(dd, N.number, @DATASTART),
                               Dateadd(dd, N.number + 1, @DATASTART)) AS D(startdate, enddate)
    WHERE  N.number BETWEEN 0 AND Datediff(dd, @DATASTART, @DATAEND)
           AND N.type = 'P'
END
ELSE
BEGIN
    INSERT INTO @TemporaryTable2
    SELECT N.number + 1 AS id,
           startdate,
           enddate
    FROM   master..spt_values N
           CROSS apply (SELECT Dateadd(MINUTE, N.number * 15, @DATASTART),
                               Dateadd(MINUTE, ( N.number + 1 ) * 15, @DATASTART)) AS D(startdate, enddate)
    WHERE  N.number BETWEEN 0 AND ( Datediff(MINUTE, @DATASTART, @DATAEND) / 15 )
           AND N.type = 'P'
END

SELECT *
FROM   @TemporaryTable2

A similar question is present here. The only limitation of this method is spt_values ranges from 0 to 2047 for type = 'p'. Hence it can return maximum 2047 rows.

How to optimize a cycle in stored procedure [T-SQL]

1 Answers1