Формирование табеля

[Вячеслав Ионов]

Доброе утро, коллеги.

Возникла следующая ситуация. При стандартном формировании табеля за период ("Операции" -> "Сформировать табель на основании режима работы" ) БОСС зависает и в итоге падает по времени ожидания.
Было выявлено, что зависание происходит на запросе (выполняется в процедуре tb_prc_GenTabel):

INSERT INTO tb_tabel (pid, dt, t2, tab_id, hours, wage, tarif_id, Acc, Sub, THEME, CUST_ID, struct_code, Code_Regim, code_syspay, id_danger, cond, poz)

SELECT e.pid ,z.d ,(CASE WHEN z.t = 1 THEN 'Я' ELSE 'В' END) ,1 ,Z.h ,
wage = (case when 0 = 1 then (case when .0 <> 0 then .0 else (CASE WHEN Typ_SysPay.Pay_period = 0 THEN e.wage * e.Number_w ELSE e.wage END) end) else NULL end) ,
tarif_id = (case when 0 = 1 then (case when 0 > 0 then 0 else e.tarif_id end) else NULL end) ,Bal_Account = (case when 0 = 1 then (case when '' <> '' then '' else e.Bal_Account end) else NULL end) ,
Sub_Bal_Acc = (case when 0 = 1 then (case when '' <> '' then '' else e.Sub_Bal_Acc end) else NULL end) ,
theme = (case when 0 = 1 then (case when 0 > 0 then 0 else e.theme end) else NULL end) ,
cust_id = (case when 0 = 1 then (case when 0 > 0 then 0 else e.cust_id end) else NULL end) ,
Code_struct_name = (case when 0 = 1 then (case when 0 > 0 then 0 else e.Code_struct_name end) else NULL end) ,
Code_Regim = (case when 0 = 1 then (case when 0 > 0 then 0 else e.Code_Regim end) else NULL end) ,
code_syspay = (case when 0 = 1 then (case when 0 > 0 then 0 else e.code_syspay end) else NULL end) ,
id_danger = (case when 0 = 1 and 0 > 0 then 0 else e.id_danger end) ,
cond = (case when 0 = 1 and 0 > 0 then 0 else e.cond_work end) ,
poz = (case when 0 = 1 and 0 > 0 then '' else e.poz end)
FROM z_calend z WITH(NOLOCK)
INNER JOIN tb_emps WITH(NOLOCK) ON tb_emps.st = 384
INNER JOIN people WITH(NOLOCK) ON people.pid = tb_emps.pid AND people.out_date >= '2019-01-01' AND people.out_date >= z.d
INNER JOIN pr_current e WITH(NOLOCK) ON e.prid = tb_emps.prid AND e.date_trans <= '2019-01-31' AND e.date_depart >= '2019-01-01' AND z.d between e.date_trans AND e.date_depart
LEFT JOIN Typ_SysPay WITH(NOLOCK) ON Typ_SysPay.Code_SysPay = e.Code_SysPay
INNER JOIN "#tb_pid_lst" pl WITH(NOLOCK) ON e.pid = pl.pid
WHERE z.d between '2019-01-01' AND '2019-01-31' AND z.d <= tb_emps.out_date
AND not exists ( SELECT top 1 1 FROM tb_tabel T WITH(NOLOCK) WHERE T.pid = e.pid AND T.dt = z.d AND T.tab_id = 1 )
AND z.R = e.Code_Regim

UNION ALL

SELECT e.pid ,z.d ,'Н' ,1 ,Z.h_no ,wage = (case when 0 = 1 then (case when .0 <> 0 then .0 else (CASE WHEN Typ_SysPay.Pay_period = 0 THEN e.wage * e.Number_w ELSE e.wage END) end) else NULL end) ,
tarif_id = (case when 0 = 1 then (case when 0 > 0 then 0 else e.tarif_id end) else NULL end) ,
Bal_Account = (case when 0 = 1 then (case when '' <> '' then '' else e.Bal_Account end) else NULL end) ,
Sub_Bal_Acc = (case when 0 = 1 then (case when '' <> '' then '' else e.Sub_Bal_Acc end) else NULL end) ,
theme = (case when 0 = 1 then (case when 0 > 0 then 0 else e.theme end) else NULL end) ,
cust_id = (case when 0 = 1 then (case when 0 > 0 then 0 else e.cust_id end) else NULL end) ,
Code_struct_name = (case when 0 = 1 then (case when 0 > 0 then 0 else e.Code_struct_name end) else NULL end) ,
Code_Regim = (case when 0 = 1 then (case when 0 > 0 then 0 else e.Code_Regim end) else NULL end) ,
code_syspay = (case when 0 = 1 then (case when 0 > 0 then 0 else e.code_syspay end) else NULL end) ,
id_danger = (case when 0 = 1 and 0 > 0 then 0 else e.id_danger end) ,cond = (case when 0 = 1 and 0 > 0 then 0 else e.cond_work end) ,
poz = (case when 0 = 1 and 0 > 0 then '' else e.poz end)
FROM z_calend z WITH(NOLOCK)
INNER JOIN tb_emps WITH(NOLOCK) ON tb_emps.st = 384
INNER JOIN people WITH(NOLOCK) ON people.pid = tb_emps.pid AND people.out_date >= '2019-01-01' AND people.out_date >= z.d
INNER JOIN pr_current e WITH(NOLOCK) ON e.prid = tb_emps.prid AND e.date_trans <= '2019-01-31' AND e.date_depart >= '2019-01-01' AND z.d between e.date_trans AND e.date_depart
lEFT JOIN Typ_SysPay WITH(NOLOCK) ON Typ_SysPay.Code_SysPay = e.Code_SysPay
INNER JOIN "#tb_pid_lst" pl WITH(NOLOCK) ON e.pid = pl.pid
WHERE z.d between '2019-01-01' AND '2019-01-31' AND not exists ( SELECT top 1 1 FROM tb_tabel T WITH(NOLOCK) WHERE T.pid = e.pid AND T.dt = z.d AND T.tab_id = 1 )
AND z.R = e.Code_Regim AND z.h_no > 0

UNION ALL

SELECT e.pid ,z.d ,'Я' ,1 ,Z.h_ve ,wage = (case when 0 = 1 then (case when .0 <> 0 then .0 else (CASE WHEN Typ_SysPay.Pay_period = 0 THEN e.wage * e.Number_w ELSE e.wage END) end) else NULL end) ,
tarif_id = (case when 0 = 1 then (case when 0 > 0 then 0 else e.tarif_id end) else NULL end) ,
Bal_Account = (case when 0 = 1 then (case when '' <> '' then '' else e.Bal_Account end) else NULL end) ,
Sub_Bal_Acc = (case when 0 = 1 then (case when '' <> '' then '' else e.Sub_Bal_Acc end) else NULL end) ,
theme = (case when 0 = 1 then (case when 0 > 0 then 0 else e.theme end) else NULL end) ,
cust_id = (case when 0 = 1 then (case when 0 > 0 then 0 else e.cust_id end) else NULL end) ,
Code_struct_name = (case when 0 = 1 then (case when 0 > 0 then 0 else e.Code_struct_name end) else NULL end) ,
Code_Regim = (case when 0 = 1 then (case when 0 > 0 then 0 else e.Code_Regim end) else NULL end) ,
code_syspay = (case when 0 = 1 then (case when 0 > 0 then 0 else e.code_syspay end) else NULL end) ,
id_danger = (case when 0 = 1 and 0 > 0 then 0 else e.id_danger end) ,cond = (case when 0 = 1 and 0 > 0 then 0 else e.cond_work end) ,
poz = (case when 0 = 1 and 0 > 0 then '' else e.poz end)
FROM z_calend z WITH(NOLOCK)
INNER JOIN tb_emps WITH(NOLOCK) ON tb_emps.st = 384
iNNER JOIN people WITH(NOLOCK) ON people.pid = tb_emps.pid AND people.out_date >= '2019-01-01' AND people.out_date >= z.d
INNER JOIN pr_current e WITH(NOLOCK) ON e.prid = tb_emps.prid AND e.date_trans <= '2019-01-31' AND e.date_depart >= '2019-01-01' AND z.d between e.date_trans AND e.date_depart
LEFT JOIN Typ_SysPay WITH(NOLOCK) ON Typ_SysPay.Code_SysPay = e.Code_SysPay
INNER JOIN "#tb_pid_lst" pl WITH(NOLOCK) ON e.pid = pl.pid
WHERE z.d between '2019-01-01' AND '2019-01-31' AND not exists ( SELECT top 1 1 FROM tb_tabel T WITH(NOLOCK) WHERE T.pid = e.pid AND T.dt = z.d AND T.tab_id = 1 ) AND z.R = e.Code_Regim AND z.h_ve > 0

UNION ALL

SELECT e.pid ,z.d ,'РВ' ,1 ,Z.h_pr ,wage = (case when 0 = 1 then (case when .0 <> 0 then .0 else (CASE WHEN Typ_SysPay.Pay_period = 0 THEN e.wage * e.Number_w ELSE e.wage END) end) else NULL end) ,
tarif_id = (case when 0 = 1 then (case when 0 > 0 then 0 else e.tarif_id end) else NULL end) ,
Bal_Account = (case when 0 = 1 then (case when '' <> '' then '' else e.Bal_Account end) else NULL end) ,
Sub_Bal_Acc = (case when 0 = 1 then (case when '' <> '' then '' else e.Sub_Bal_Acc end) else NULL end) ,
theme = (case when 0 = 1 then (case when 0 > 0 then 0 else e.theme end) else NULL end) ,
cust_id = (case when 0 = 1 then (case when 0 > 0 then 0 else e.cust_id end) else NULL end) ,
Code_struct_name = (case when 0 = 1 then (case when 0 > 0 then 0 else e.Code_struct_name end) else NULL end) ,
Code_Regim = (case when 0 = 1 then (case when 0 > 0 then 0 else e.Code_Regim end) else NULL end) ,
code_syspay = (case when 0 = 1 then (case when 0 > 0 then 0 else e.code_syspay end) else NULL end) ,
id_danger = (case when 0 = 1 and 0 > 0 then 0 else e.id_danger end) ,
cond = (case when 0 = 1 and 0 > 0 then 0 else e.cond_work end) ,poz = (case when 0 = 1 and 0 > 0 then '' else e.poz end)
FROM z_calend z WITH(NOLOCK)
INNER JOIN tb_emps WITH(NOLOCK) ON tb_emps.st = 384
INNER JOIN people WITH(NOLOCK) ON people.pid = tb_emps.pid AND people.out_date >= '2019-01-01' AND people.out_date >= z.d
INNER JOIN pr_current e WITH(NOLOCK) ON e.prid = tb_emps.prid AND e.date_trans <= '2019-01-31' AND e.date_depart >= '2019-01-01' AND z.d between e.date_trans AND e.date_depart
LEFT JOIN Typ_SysPay WITH(NOLOCK) ON Typ_SysPay.Code_SysPay = e.Code_SysPay
iNNER JOIN "#tb_pid_lst" pl WITH(NOLOCK) ON e.pid = pl.pid WHERE z.d between '2019-01-01' AND '2019-01-31' AND not exists ( SELECT top 1 1 FROM tb_tabel T WITH(NOLOCK)
WHERE T.pid = e.pid AND T.dt = z.d AND T.tab_id = 1 ) AND z.R = e.Code_Regim AND z.h_pr > 0

Селекты по отдельности выполняются без проблем, но стоит выполнить хотя бы два из них через UNION, сразу происходит зависание. Это вряд ли из-за блокировок.

Может кто сталкивался с подобным?

Не сразу увидел, что создал тему не в том разделе

[RVV]

Сколько записей в tb_tabel?

[RVV]

Как вариант, нужно работать через временную таблицу

INSERT INTO "#tmp_tb_tabel" (pid, dt, t2, tab_id, hours, wage, tarif_id, Acc, Sub, THEME, CUST_ID, struct_code, Code_Regim, code_syspay, id_danger, cond, poz)

и т.д.

а потом переносить данные

-- Переносим данные
INSERT INTO tb_tabel (
pid, dt, t2, tab_id, hours, wage, tarif_id, Acc, Sub, THEME, CUST_ID, struct_code, Code_Regim, code_syspay, id_danger, cond, poz, idMain, N_dirM
)
SELECT
tb.pid, tb.dt, tb.t2, _tab_id_['], tb.hours, tb.wage, tb.tarif_id, tb.Acc, tb.Sub, tb.THEME, tb.CUST_ID, tb.struct_code, tb.Code_Regim, tb.code_syspay, tb.id_danger, tb.cond, tb.poz, tb.idMain, tb.N_dirM
FROM "#tmp_tb_tabel" tb _hintbrowser
INNER JOIN "#tb_pid_lst" pl _hintbrowser ON tb.pid = pl.pid
;

[RVV]

А еще, как вариант, разбить код на несколько отдельных INSERT,
убрав UNION

[Вячеслав Ионов]

RVV
Вот как раз разбили на несколько инсертов, пока что.

В таблице tb_tabel около 15 млн записей, что не так много, в принципе. Смущает то, что в декабре таких проблем не было. Хотя, ничего с базой не делали.

Возможно дело в индексах, но их мы пробовали перестраивать.

Спасибо за идею с темповой таблицей, попробуем