Is there a way to optimize or replace a while loop in sql?

Question

We have a couple million rows of data that we need to "explode" out by adding a row for every date between the started_at date and the ended_at date. The while loop is what is taking the longest in our query.

Any idea on how to optimize or replace it?

IF (OBJECT_ID('TempDb..#exploded_services') IS NOT NULL)
  DROP TABLE #exploded_services;

CREATE TABLE #exploded_services
  (
   target_date date,
   move_id varchar(30),
   initiation_id varchar(30),
   initiated_at date,
   booked_at date,
   transferee varchar(60),
   account_id varchar(30),
   mc_id varchar(30),
   po varchar(60),
   weight int,
   service varchar(150),
   started_at date,
   ended_at date,
   location_id nvarchar(64),
   description varchar(max),
   provider varchar(max),
   mode varchar(60),
   origin_location_id nvarchar(64),
   destination_location_id nvarchar(64),
   transferee_phone varchar(40),
   transferee_email varchar(100),
   status varchar(10),
   ordinal int
  );


WHILE (@pointer <= @end_date)
 BEGIN
   INSERT INTO #exploded_services
   SELECT
     @pointer,
     svcs.*
   FROM #Services svcs
   WHERE @pointer BETWEEN svcs.started_at AND COALESCE(svcs.ended_at,@end_date)
   SET @pointer = DATEADD(dd, 1, @pointer)
 END;

Add a RowNumber in the select statement and user DATEADD(dd, Row_Number Column Value, @pointer) in the where clause. A single select statement can be inserted all rows. — Hasan Mahmood
– Hasan Mahmood, Commented Apr 15, 2019 at 20:57
Just do this with a single insert statement. What is the point of the loop here? — Sean Lange
– Sean Lange, Commented Apr 15, 2019 at 21:00
Please read up on the difference between declarative and imperative language structures. Therin lies your answer. NEVER use loops inside SQL declarative statements. — theMayer
– theMayer, Commented Apr 15, 2019 at 21:05
Also, please don't use shorthand like dd. Not much more effort to type day, but it sure is more readable (never mind reliable). — Aaron Bertrand
– Aaron Bertrand, Commented Apr 15, 2019 at 21:07
You are creating days for date ranges. In a programming language this is done with a loop. In SQL you would typically use a recursive query for this. I don't have the time now to post an answer. Hopefully, someone else will. — Thorsten Kettner
– Thorsten Kettner, Commented Apr 15, 2019 at 21:16

Piotr Palka · Accepted Answer · 2019-04-16 05:26:41Z

1

Create a table with one date column.
Populate it will all possible dates that applies to your services.
Populate your target table with:

 INSERT INTO #exploded_services
   SELECT
     dates_table.date,
     svcs.*
   FROM #Services svcs
   INNER JOIN dates_table ON dates_table.date BETWEEN svcs.started_at AND COALESCE(svcs.ended_at,_arbitrary_end_date_)

edited Apr 16, 2019 at 5:26

answered Apr 15, 2019 at 21:05

Piotr Palka

3,2351 gold badge12 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Luis Cazares · Accepted Answer · 2019-04-15 21:01:46Z

0

This can be achieved using a Tally table. Here's an example on how to do it using one created on the fly with cascading ctes.

WITH 
E(n) AS(
    SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
),
E2(n) AS(
    SELECT a.n FROM E a, E b
),
E4(n) AS(
    SELECT a.n FROM E2 a, E2 b
),
cteTally(n) AS(
    SELECT TOP(DATEDIFF(DD, @pointer, @end_date) + 1) 
            ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1 n
    FROM E4
)
INSERT INTO #exploded_services
SELECT
    DATEADD( dd, n @pointer),
    svcs.*
FROM #Services svcs
JOIN cteTally t ON DATEADD( dd, n @pointer) BETWEEN svcs.started_at AND COALESCE(svcs.ended_at,@end_date);

answered Apr 15, 2019 at 21:01

Luis Cazares

3,60510 silver badges24 bronze badges

Comments

Michał Turczyn · Accepted Answer · 2019-04-16 05:48:12Z

0

You could try below code using CTE to generate all dates needed:

 -- cte to get all dates needed
 ;with cte as (
    select @pointer ptr
    union all
    select DATEADD(dd, 1, @pointer) from cte
    where @pointer < @end_date
 )
 -- adjusted insert query
 INSERT INTO #exploded_services
 select c.*, s.*
 from #Services s
 join cte c on c.ptr between s.started_at and coalesce(svcs.ended_at,@end_date)

answered Apr 16, 2019 at 5:48

Michał Turczyn

41.3k18 gold badges58 silver badges87 bronze badges

Collectives™ on Stack Overflow

Is there a way to optimize or replace a while loop in sql?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related