Блог

Главная - Вопросы по программированию - Есть ли способ оптимизировать или заменить цикл while в sql?

Есть ли способ оптимизировать или заменить цикл while в sql?

#sql #sql-server #tsql #while-loop

#sql #sql-server #tsql #цикл while

Вопрос:

У нас есть пара миллионов строк данных, которые нам нужно «разобрать», добавив строку для каждой даты между started_at date и ended_at date . Цикл while — это то, что занимает больше всего времени в нашем запросе.

Есть идеи о том, как его оптимизировать или заменить?

 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;

Комментарии:

1. Добавьте RowNumber в оператор select и user DATEADD(dd, значение столбца Row_Number, @pointer) в предложении where . Одним оператором select можно вставить все строки.

2. Просто сделайте это с помощью одного оператора insert . В чем смысл цикла здесь?

3. Пожалуйста, ознакомьтесь с различием между декларативными и императивными языковыми структурами. В этом заключается ваш ответ. НИКОГДА не используйте циклы внутри декларативных операторов SQL.

4. Кроме того, пожалуйста, не используйте сокращение like dd . Не намного больше усилий для ввода day , но он, несомненно, более удобочитаемый (не говоря уже о надежности).

5. Вы создаете дни для диапазонов дат. На языке программирования это делается с помощью цикла. В SQL для этого обычно используется рекурсивный запрос. Сейчас у меня нет времени публиковать ответ. Надеюсь, это сделает кто-нибудь другой.

Ответ №1:

  1. Создайте таблицу с одним столбцом даты.
  2. Заполните его всеми возможными датами, которые применяются к вашим службам.
  3. Заполните вашу целевую таблицу:
  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_)

Ответ №2:

Этого можно добиться с помощью таблицы подсчета. Вот пример того, как это сделать, используя созданный на лету с помощью каскадных cte.

 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);

Ответ №3:

Вы можете попробовать приведенный ниже код с использованием CTE для генерации всех необходимых дат:

  -- 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)
Метки: