Блог

Использование SQL Server 2017.

СКРИПКА SQL: ССЫЛКА

 CREATE TABLE [TABLE_1] 
(
    PLAN_NR decimal(28,6) NULL,
    START_DATE datetime  NULL,
);

CREATE TABLE [TABLE_2] 
(
    PLAN_NR decimal(28,6) NULL,
    PERIOD_NR decimal(28,6) NULL,
);

INSERT INTO TABLE_1 (PLAN_NR, START_DATE)
VALUES (1, '2020-05-01'), (2, '2020-08-05');

INSERT INTO TABLE_2 (PLAN_NR, PERIOD_NR)
VALUES (1, 1), (1, 2), (1, 5), (1, 6), (1, 5), (1, 6), (1, 17),  
       (2, 2), (2, 3), (2, 5), (2, 2), (2, 17), (2, 28);

CREATE VIEW ALL_PERIODS
AS
    WITH rec_cte AS 
    (
        SELECT 
            PLAN_NR, START_DATE,
            1 period_nr, DATEADD(day, 7, START_DATE) next_date
        FROM 
            TABLE_1
        UNION ALL
        SELECT 
            PLAN_NR, next_date,
            period_nr   1, DATEADD(day, 7, next_date)
        FROM 
            rec_cte       
        WHERE 
            period_nr < 100       
  ),
  cte1 AS 
  (
      SELECT 
          PLAN_NR, period_nr, START_DATE
      FROM 
          rec_cte
      UNION ALL
      SELECT 
          PLAN_NR, period_nr, DATEADD(DAY, 1, EOMONTH(next_date, -1)) 
      FROM 
          rec_cte
      WHERE 
          MONTH(START_DATE) <> MONTH(next_date)
  ),
  cte2 AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY PLAN_NR ORDER BY START_DATE) rn
FROM cte1
)
SELECT PLAN_NR, rn PERIOD_NR, START_DATE 
FROM cte2
WHERE rn <= 100
 

В таблице 1 перечислены планы (PLAN_NR) и их дата начала (START_DATE).
В таблице 2 перечислены номера планов (PLAN_NR) и периоды (1 — X). Для каждого номера плана периоды могут появляться несколько раз, но также могут отсутствовать.
Период длится семь дней, если период не включает изменение месяца. Затем период делится на часть до конца месяца и часть после конца месяца.

В представлении ALL_PERIODS перечислены 100 периодов для каждого плана в соответствии с этой системой.

Моя проблема заключается в производительности следующего выбора, который я хотел бы использовать в представлении:

 SELECT 
t2.PLAN_NR
, t2.PERIOD_NR
, a_p.START_DATE 
from TABLE_2 as t2 
left outer join ALL_PERIODS a_p on t2.PERIOD_NR = a_p.PERIOD_NR and t2.PLAN_NR = a_p.PLAN_NR
 

Из примерно 4000 записей в ТАБЛИЦЕ_2 выбор становится невероятно медленным.

Само соединение еще не замедляет выполнение запроса. Только с дополнительным select a_p.START_DATE все становится невероятно медленным.

Я прочитал представление во временную таблицу и выполнил объединение над ней, и проблем с производительностью не возникло. (2 секунды для 4000 записей).

Поэтому я предполагаю, что CTE, используемый в представлении, является причиной низкой производительности. К сожалению, я не могу использовать временные таблицы в представлениях, и мне бы не хотелось записывать данные в обычную таблицу.

Есть ли в SQL Server способ улучшить задержку CTE?

Вместо рекурсивного CTE сгенерируйте ALL_PERIODS с ПЕРЕКРЕСТНЫМ соединением между таблицей плана и «таблицей чисел» либо с сохранением, либо как нерекурсивный CTE.

НАПРИМЕР

 WITH N As
(
   select top 100 row_number() over (order by (select null)) i
   from (values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10) ) v1(i),
        (values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10) ) v2(i)
),
plan_period AS 
(
    SELECT 
        PLAN_NR, START_DATE,
        N.i period_nr, DATEADD(day, 7*N.i, START_DATE) next_date
    FROM TABLE_1 CROSS JOIN N
),
 

если вы можете изменить представление, я бы рекомендовал сделать это :

1. добавьте таблицу, содержащую числа, начинающиеся с 0, во все, что, по вашему мнению, вам понадобится в базе данных, вы можете использовать команду ниже :

 create table numbers ( id int)
go
;with cte (
  select 0 num
  union all
  select num   1
  where num < 2000 -- change this 
)

insert into number
from num from cte
 

2. измените первый cte в представлении на этот :

     WITH rec_cte AS 
    (
          
  SELECT
        PLAN_NR
        , DATEADD(DAY, 7* id, START_DATE) START_DATE
        , id   1                        period_nr
        , DATEADD(DAY, 7*( id 1), START_DATE) next_date

    FROM
        TABLE_1 t
        CROSS apply intenum  i 
         WHERE i.id <100       
  ),...
 

3. Также рассмотрите возможность использования временной таблицы вместо cte, это может быть полезно