Список форумов BOSSForum.RU - Форум. БОСС-Кадровик
Независимый форум, посвященный системе БОСС-Кадровик
и всему, что с ней связано
 FAQFAQ   ПоискПоиск   ПользователиПользователи   ГруппыГруппы   РегистрацияРегистрация 
 ПрофильПрофиль   Войти и проверить личные сообщенияВойти и проверить личные сообщения   ВходВход 

view отпусков с отзывами

 
Начать новую тему   Ответить на тему    Список форумов BOSSForum.RU - Форум. БОСС-Кадровик -> Серверные процедуры MS SQL Server
Предыдущая тема :: Следующая тема  
Автор Сообщение
rebel25
Большой шоколадный орден
Большой шоколадный орден


Зарегистрирован: 06.10.2008
Сообщения: 578
Откуда: Москва

СообщениеДобавлено: Пт Фев 18, 2022 11:46    Заголовок сообщения: view отпусков с отзывами Ответить с цитатой

Коллеги привет, нужна помощь в написании view

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

Вью должна показывать отпуска с учётом отзывов, которые могут быть в любых комбинациях – несколько отзывов закрывают один отпуск полностью, несколько отзывов отзывают не полностью, один отзыв отзывает полностью, один отзыв отзывает часть.
Для обработки отзывов я использую тиражную функцию … cross apply dbo.prfn_leave которая правильно обрабатывает любые отзывы и создает основную нагрузку т к применяется к каждому отпуску.

Сейчас работает вью, которая не использует функцию и исключает полностью отозванные отпуска. Частично отозванные выводятся без учета отзыва, что не верно и пока редко, создает инциденты.
Вью, работающая правильно с использованием функции работает слишком медленно.

Индексированную вью создать невозможно т к внутри вью есть функция.

Я пробовал разделить отпуска без отзывов и отозванные и соединить с помощью UNION. Отпуска без отзывов не используют функцию, а с отзывами используют. Она работает быстрее, но всё равно слишком медленно.

Ниже вью и один из запросов, который должен выполниться быстро.

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

Табличную функцию использовать нельзя вместо вью. Может быть можно использовать SP процедуру, если к ней можно обратиться как к вью – нужно спросить у разработчиков портала.

Возможны разные варианты, в т ч необычные. Например создать таблицу, изначально заполнить её отпусками, изменив их даты так, как их меняют отзывы и в дальнейшем поддерживать её актуальность с помощью триггеров – удалять, добавлять, изменять даты в случае введения отзыва и некоторые другие операции.

Там будут только основные отпуска отпуска с правильными датами начала и окончания и не будет отзывов.
К такой таблице можно быстро обращаться, если решить вопрос с блокировками. Сами отпуска постоянно переносятся, а отзывы вносятся редко.
А что, не плохая идея. Это я только что придумал.

Может можно что то попроще придумать? Во вью нужны отпуска с 2020 по 2023 года

Про API босс-кадровика и его личный кабинет напоминать не нужно, я сейчас не могу их использовать. Нужно пока решить вопрос с помощью объектов MSSQL

IF OBJECT_ID ('[EXT_SP].[pr_leave_vw]', 'view') IS NOT NULL
DROP VIEW [EXT_SP].[pr_leave_vw] ;
GO
CREATE VIEW [EXT_SP].[pr_leave_vw]
-- WITH SCHEMABINDING
AS
select
l.Auto_Leave,
p.Auto_Card,
l.Code_Leave,
t.FromD,
t.ToD as tod3 ,
(select count(iif(t=1,1,0)) from z_calend where r = tl.Code_Regim and t = 1 and d between t.FromD and t.ToD) as workdays, --так странно т к может быть частичный отзыв и рабочие дни нужно вычислять, в отзыве только календарные
--[dbo].[user_z_calend_d](tl.Code_Regim,t.FromD,t.ToD,1) as workdays,
datediff(dd,t.FromD,t.ToD)+1 as [days],
0 as compensation,
0 as Refer_Num,
l.cMonth,
0 as l_back,
0 as flag_plan,
'1900-01-01' as mdate,
'' as uname,
'' as text_comment
from
pr_leave l (nolock) cross apply dbo.prfn_leave(l.fromd,l.tod3 , 0, l.pid) t –функция выполняется для каждой строки, что очень долго т к отпусков много.
join people p (nolock) on (p.pid = l.pid)
left join dbo.pr_leave_add a (nolock) on (a.auto_leave = t.Auto_Leave)
join dbo.typ_Leave tl on (tl.Code_Leave = t.Code_Leave)
where 1=1
and t.Code_Leave in (select code_leave from typ_Leave where code_operat not in (1, 6, 8)) --без компенсации и отзывов
--and o.auto_leave is null --без отзывов
and a.Auto_Leave is null --без дополнительных
and convert(date,getdate()) between p.in_date and p.out_date --текущая дата между датой приема и увольнения, чтобы не обрабатывать уволенных?
--and not exists (select top 1 1 from pr_leave lback where lback.l_back = t.Auto_Leave GROUP BY lback.l_back,fromd,ToD3 having sum(lback.days_tot) = datediff(dd,t.fromd,t.ToD3)+1) --без полностью отозванных – это условие из текущей вью, которая работает без функции и не верно обрабатывает частично отозванные отпуска. Она в рабочей базе.
GO


declare @__startDate_1 datetime2(7) = '2020-01-01',@__endDate_2 datetime2(7) = '2022-12-31'
SELECT [x].[Days], [x].[ToD3] AS [EndDate], [x].[Code_Leave] AS [CodeLeave], [x].[text_comment] AS [Comment], [x].[auto_card] AS [EmployeeId], [x].[Auto_Leave] + 715827882 AS [Id], [x].[mdate] AS [ModifiedDate], [x].[cMonth] AS [Paid], [x].[FromD] AS [StartDate], [x].[Workdays], [x].[compensation] AS [Year]
FROM [EXT_SP].[pr_leave_vw] AS [x]
WHERE ([x].[auto_card] IN (29493, 79215, 80810, 80834, 83151, 91034, 104095, 112338, 154575, 159677, 163175, 174970, 206647, 213999, 235378, 243257, 249319, 251310, 288592, 304657, 383752, 409402, 409523, 415566, 434284, 437216, 441771, 461713, 472502, 475659, 477958, 480137, 482416, 490249, 497588, 520176, 522669, 538395, 551063, 551548, 554505, 556673, 557700, 557826, 560587, 560595, 560937, 562971, 563289, 565904, 567520, 567521, 572517, 572520, 572533, 572534, 572537, 578440, 578683, 579298, 579344, 579812, 579820, 580162, 580193, 580533, 580946, 581164, 581166, 581170, 581302, 581309, 581323, 581325, 581329, 581332, 581333, 581335, 581339, 581341, 581525, 581528, 581538, 581547, 581559, 581563, 581785, 581788, 581801, 581806, 581808, 581809, 581828, 581833, 581975, 581980, 581985, 582013, 582021, 582022, 582027, 582029, 582190, 582201, 582203, 582241, 582708, 582760, 582762, 583169, 583176, 583212, 583220, 583226, 583229, 583362, 583398, 583407, 583411, 583420, 583475, 584208, 584230, 584245, 584259, 584270, 584275, 584640, 584658, 584663, 584672, 584754, 584766, 584779, 584783, 584789, 584799, 584805, 585426, 585441, 585442, 585448, 585450, 585453, 585859, 585869, 585878, 585882, 585888, 585893, 585899, 585913, 586072, 586106, 586125, 586133, 586430, 586444, 586663, 586692, 587082, 587117, 587134, 587530, 587535, 587566, 587573, 587585, 587591, 587598, 588298, 588304, 588321, 588588, 588610, 588611, 588613, 588615, 588621, 588625, 588627, 588841, 588858, 589167, 589173, 589179, 589192, 589426, 589428, 589966, 589984, 590003, 590449, 590459, 590484, 590494, 590498, 590531, 590535, 590539, 590545, 590549, 590714, 590725, 590734, 590737, 590747, 590751, 590898, 590918, 590925, 590926, 590938, 594725, 594731, 595018, 595048, 595556, 595702, 595713, 595736, 596368, 596562, 596567, 597829, 597845, 598176, 598550, 600122, 600283, 600815, 600819, 600837, 601303, 601405, 601854, 601952, 602239, 602542, 602777, 602792, 602908, 603100, 603510, 604101, 604121, 604321, 604541, 604996, 605506, 605593, 605950) AND ([x].[ToD3] >= @__startDate_1)) AND ([x].[FromD] <= @__endDate_2)
Вернуться к началу
Посмотреть профиль Отправить личное сообщение Отправить e-mail
NewAge



Зарегистрирован: 11.06.2010
Сообщения: 108

СообщениеДобавлено: Пт Фев 18, 2022 19:12    Заголовок сообщения: Ответить с цитатой

Доброго дня

А можете кратко написать, что требуется получить на выходе? Я в этом большом количестве букв потерял, что нужно сделать.
Вернуться к началу
Посмотреть профиль Отправить личное сообщение
Spartak



Зарегистрирован: 18.03.2010
Сообщения: 180

СообщениеДобавлено: Пн Фев 21, 2022 16:40    Заголовок сообщения: Ответить с цитатой

Может попробовать полностью код функции prfn_leave вынести в запрос view? И дальше уже пытаться тюнить перфоманс...
Вернуться к началу
Посмотреть профиль Отправить личное сообщение
rebel25
Большой шоколадный орден
Большой шоколадный орден


Зарегистрирован: 06.10.2008
Сообщения: 578
Откуда: Москва

СообщениеДобавлено: Ср Мар 02, 2022 11:40    Заголовок сообщения: Ответить с цитатой

NewAge писал(а):
Доброго дня

А можете кратко написать, что требуется получить на выходе? Я в этом большом количестве букв потерял, что нужно сделать.


На выходе нужно получить периоды отпусков с учетом отзывов.
Вернуться к началу
Посмотреть профиль Отправить личное сообщение Отправить e-mail
NewAge



Зарегистрирован: 11.06.2010
Сообщения: 108

СообщениеДобавлено: Чт Мар 10, 2022 14:12    Заголовок сообщения: Ответить с цитатой

Вот у меня есть такой кусок кода для основных отпусков:
Код:

Select t.Id, t.EmpId, t.Date1, t.Date2, t.ModeId, t.Recalc, IIf(t.Recalc = 1, Count(Distinct IIf(c.t = 1, c.d, Null)), Sum(t.WDays)), IIf(t.Recalc = 1, Count(Distinct c.d), Sum(t.CDays))
From (
   Select
      l.auto_leave, l.pid, IsNull(DateAdd(Day, 1, lb.tod), l.fromd), IsNull(DateAdd(Day, -1, Lead(lb.fromd) Over(Partition By l.auto_leave Order By lb.fromd)), l.tod3),
      IIf(IsNull(t.code_regim, 0) = 0, dbo.prfn_region_regim('_рр_отпуск', l.pid, l.fromd), t.code_regim), IIf(IsNull(lb.auto_leave, 0) > 0, 1, 0),
      l.workdays, l.days
   From
      pr_leave l With (NoLock) Inner Join typ_leave t With (NoLock) On t.code_leave = l.code_leave And t.code_operat Not In (1, 6)
      Left Outer Join (pr_leave lb With (NoLock) Inner Join typ_leave tb With (NoLock) On tb.code_leave = lb.code_leave And tb.code_operat = 1) On
         lb.l_back = l.auto_leave And lb.fromd <= l.tod3 And lb.tod >= l.fromd
   Where IsNull(l.flag_plan, 0) = 0
   Union
   Select
      l.auto_leave, l.pid, IsNull(DateAdd(Day, 1, Lag(lb.tod) Over(Partition By l.auto_leave Order By lb.fromd)), l.fromd), IsNull(DateAdd(Day, -1, lb.fromd), l.tod3),
      IIf(IsNull(t.code_regim, 0) = 0, dbo.prfn_region_regim('_рр_отпуск', l.pid, l.fromd), t.code_regim), IIf(IsNull(lb.auto_leave, 0) > 0, 1, 0),
      l.workdays, l.days
   From
      pr_leave l With (NoLock) Inner Join typ_leave t With (NoLock) On t.code_leave = l.code_leave And t.code_operat Not In (1, 6)
      Left Outer Join (pr_leave lb With (NoLock) Inner Join typ_leave tb With (NoLock) On tb.code_leave = lb.code_leave And tb.code_operat = 1) On
         lb.l_back = l.auto_leave And lb.fromd <= l.tod3 And lb.tod >= l.fromd
   Where IsNull(l.flag_plan, 0) = 0
) t(Id, EmpId, Date1, Date2, ModeId, Recalc, WDays, CDays)
Left Outer Join z_calend c With (NoLock) On c.r = t.ModeId And c.d Between t.Date1 And t.Date2 And t.Recalc = 1
Where t.Date1 <= t.Date2
Group By t.Id, t.EmpId, t.Date1, t.Date2, t.ModeId, t.Recalc
Order By t.Date1 Desc

Как я понял вам нужен только основной отпуск без допов. Если чуть доработать будет и допы выдавать.
У меня работает не больше минуты.
Вернуться к началу
Посмотреть профиль Отправить личное сообщение
superjek



Зарегистрирован: 04.04.2022
Сообщения: 25

СообщениеДобавлено: Пн Апр 04, 2022 18:32    Заголовок сообщения: Ответить с цитатой

Можно использовать второй вариант вызова функции dbo.prfn_leave:

Код:
   Select l.*
   From dbo.prfn_leave('2020-01-01', '2023-12-31', 2,0) l
      join people p on l.pid=p.pid
   where p.out_date> convert(date,getdate())


В таком варианте она сразу возвращает таблицу всех отпусков
Вернуться к началу
Посмотреть профиль Отправить личное сообщение
NewAge



Зарегистрирован: 11.06.2010
Сообщения: 108

СообщениеДобавлено: Вт Апр 12, 2022 12:53    Заголовок сообщения: Ответить с цитатой

superjek писал(а):
Можно использовать второй вариант вызова функции dbo.prfn_leave:

Код:
   Select l.*
   From dbo.prfn_leave('2020-01-01', '2023-12-31', 2,0) l
      join people p on l.pid=p.pid
   where p.out_date> convert(date,getdate())


В таком варианте она сразу возвращает таблицу всех отпусков


Мне она не особо нравится. Медленно работает.))))
Вернуться к началу
Посмотреть профиль Отправить личное сообщение
NewAge



Зарегистрирован: 11.06.2010
Сообщения: 108

СообщениеДобавлено: Вт Апр 12, 2022 13:39    Заголовок сообщения: Ответить с цитатой

И плюс она не пересчитывает дни по отпуску с учетом отзыва.
Вернуться к началу
Посмотреть профиль Отправить личное сообщение
Показать сообщения:   
Начать новую тему   Ответить на тему    Список форумов BOSSForum.RU - Форум. БОСС-Кадровик -> Серверные процедуры MS SQL Server Часовой пояс: GMT + 4
Страница 1 из 1

 
Перейти:  
Вы не можете начинать темы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете голосовать в опросах


Pоwerеd by рhpВB © 2001, 2005 рhpВB Grouр
Русская поддержка phрВB
Rambler's Top100 Рейтинг@Mail.ru Список форумов BOSSForum.RU