Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Цикл с выполнением процедуры  [new]
Oleg Krivopusk
Member

Откуда: Хабаровск
Сообщений: 50
Есть список сотрудников (около 2500 чел) в таблице @T
Есть процедура формирования табеля по сотруднику.
Я пробегаюсь и для каждого запускаю расчет. Занимает минут 15.

Вопрос: можно ли что то усовершенствовать тут?


CREATE PORCEDURE ....
...
While Exists (Select Tabel_nom from @T) 
Begin 

  Select @PK = MIN(Tabel_nom) from @T
  
  exec dbo.up__CreateTabel4Sotrudnik_RN @FirstDay, @PK   
    
  Delete from @T Where Tabel_nom = @PK
...

End

Спасибо.
1 сен 09, 09:31    [7601058]     Ответить | Цитировать Сообщить модератору
 Re: Цикл с выполнением процедуры  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
Переделать процедуру up__CreateTabel4Sotrudnik_RN таким образом,
чтобы она формировала табели по сотрудникам,
список Tabel_nom-ов которых она принимала бы в качестве входного параметра
1 сен 09, 09:47    [7601163]     Ответить | Цитировать Сообщить модератору
 Re: Цикл с выполнением процедуры  [new]
DENIS_CHEL
Member

Откуда:
Сообщений: 23097
+ Я бы еще рекомендовал переделать dbo.up__CreateTabel4Sotrudnik_RN, 15 минут для формирования 2.5К таблей, как то для меня не приемлимо страшно смотрится...
1 сен 09, 09:51    [7601189]     Ответить | Цитировать Сообщить модератору
 Re: Цикл с выполнением процедуры  [new]
Oleg Krivopusk
Member

Откуда: Хабаровск
Сообщений: 50
Передавать список таб. номеров в процедуру - это и есть перенос этого цикла туда :)

В том то и дело, что расчет 1 человека (с наибольшим количеством переводов за период и сменой процента районной надбавки в середине месяца, с доп. окладом и т.п.) идет мгновенно - менее 0,5 сек, а тут в среднем получается около 3-х. Я вот и думаю, что именно из-за цикла начинает тормозить...
1 сен 09, 10:07    [7601291]     Ответить | Цитировать Сообщить модератору
 Re: Цикл с выполнением процедуры  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
Oleg Krivopusk
Передавать список таб. номеров в процедуру - это и есть перенос этого цикла туда :)
не совсем
это join с таблицей, которая будет получена из входного списка
как потом сервер осуществит этот join (вложенными циклами или еще как) - ему виднее

(в случае 2008 можно вроде прямо таблицу в хранимку передавать, я, правда, с 2008 еще не работал)
1 сен 09, 10:13    [7601331]     Ответить | Цитировать Сообщить модератору
 Re: Цикл с выполнением процедуры  [new]
DENIS_CHEL
Member

Откуда:
Сообщений: 23097
Вы не поняли, Паганель предлагает отказаться от цыкла, заменив его на обработку всей @T...
1 сен 09, 10:15    [7601341]     Ответить | Цитировать Сообщить модератору
 Re: Цикл с выполнением процедуры  [new]
Oleg Krivopusk
Member

Откуда: Хабаровск
Сообщений: 50
Вот код самой процедуры... джоином тут не обойтись думаю....
В месяце может произойти изменение Районной надбавки (с 20 до 30%, например)
вот и надо табель разбить на период с 20% и 30% и т.д...


CREATE PROCEDURE dbo.up__CreateTabel4Sotrudnik_RN (@FirstDay datetime, @TN int) 
AS

-- RAISERROR('ТАБЕЛЬ ВРЕМЕННО НАХОДИТСЯ НА КОРРЕКТИРОВКЕ!',16,1)

Set NOCOUNT ON

DECLARE @Err_x int, @Err_y int, @LastDay DateTime, @MaxIDTabel int, @RaschPeriodStart datetime,
@RaschPeriodEnd datetime, @MyName varchar(20), @M int, @Y int, @I int, @D1 datetime,
@D2 datetime, @RN decimal(6,2), @P1 datetime, @P2 datetime,
@D1Chto datetime, @D2Chto datetime, @D1Chem datetime, @D2Chem datetime

IF @TN IS Null RAISERROR('Не задан табельный номер!',16,1)
if Exists (
Select * 
from dbo.CAT_ZP_PROVODKA 
where Month = MONTH(@FirstDay) and Year = YEAR(@FirstDay)) RAISERROR('Период закрыт!!!',16,1)

	SET @LastDay =  CAST(Convert(varchar(10), dateadd(mm,1,@FirstDay), 103) AS datetime)-1
	Set @M = Month(@FirstDay)
	Set @Y = Year(@FirstDay)
	Select @MyName = dbo.MyName()


-- создаем и заполняем таблицу с периодами действия и 
-- значениями Районной надбавки за тек. месяц
-- @TRN
	Declare @TRN Table (PK int identity(1,1) primary key, D1 datetime, D2 datetime, RN decimal(6,2))
	Declare @TRNTMP Table (PK int identity(1,1) primary key, D1 datetime, D2 datetime, RN decimal(6,2))  

	INSERT @TRN
	Select DateBegin, DateEnd, [Percent]
	From Cat_ZP_NU_Post 
	Where (Deleted=0) and (IdNU=96) and Tabel_Nom = @TN
  and dbo.uf_isEntry(DateBegin, DateEnd, @FirstDay, @LastDay) = 1
  Order by DateBegin
  
  UPDATE @TRN SET D1 = @FirstDay WHERE IsNull(D1,'1.1.1900')<@FirstDay
  UPDATE @TRN SET D2 = @LastDay WHERE IsNull(D2,'1.1.2099')>@LastDay
  
  if not Exists (Select * from @TRN) Insert @TRN Select @FirstDay,@LastDay,0
  
--  if (Select MIN(D1) From @TRN)
  
  
  if not Exists (select * from @TRN) -- если нет Р/Н вообще
	  Insert @TRN Values (@FirstDay,DATEADD(dd,-1,@D1),0)
  else -- если Р/Н начинается не с начала месяца (!)
  begin
	  Select @D1 = MIN(D1) From @TRN 
	  if @D1>@FirstDay Insert @TRN Values (@FirstDay,DATEADD(dd,-1,@D1),0)
  end  
  
    
-- @TRN    


-- Загоняем данные во временную таблицу
-- @T (подготовка переводов)
Declare @T Table (PK int identity(1,1) primary key, IdPerev int, Tabel_nom int, CodeDol int, 
									CodePodrazd int, Salary money, Rate decimal (18,2), TabelDate datetime, 
                  DateStart datetime, DateEnd datetime, ShifrZatr varchar(20), IsDopOklad bit,
                  RN decimal(6,2))

Declare @TTMP Table (PK int identity(1,1) primary key, IdPerev int, Tabel_nom int, CodeDol int, 
									CodePodrazd int, Salary money, Rate decimal (18,2), TabelDate datetime, 
                  DateStart datetime, DateEnd datetime, ShifrZatr varchar(20), IsDopOklad bit,
                  RN decimal(6,2))
                  
Declare @TTMPT Table (PK int primary key, IdPerev int, Tabel_nom int, CodeDol int, 
									CodePodrazd int, Salary money, Rate decimal (18,2), TabelDate datetime, 
                  DateStart datetime, DateEnd datetime, ShifrZatr varchar(20), IsDopOklad bit,
                  RN decimal(6,2))
                  

-- Загоняем данные во временную таблицу
-- @TTMP (временная, для создания @T)
	INSERT INTO @TTMP (IdPerev, Tabel_nom, CodeDol, CodePodrazd, Salary, Rate, TabelDate, DateStart, 
  								DateEnd, ShifrZatr, IsDopOklad,RN)
	SELECT IdPerev, Tabel_nom, Code_Dolgn, Code_Podrazd, IsNull(Oklad,0), Rate, @FirstDay, DateBegin, 
  			 DateEnd, ShifrZatr, IsDopOklad, 0 
	From dbo.v_ZP_ListSotrudFull_WithDopOklad
	WHERE Tabel_nom = @TN and IsNull(DateBegin, '1.1.2000')<=@LastDay 
  			and IsNull(DateEnd, '1.1.2099')>=@FirstDay
        
-- Делаем начальные и конечные записи равные "краям" заданного периода
	Update @TTMP Set DateEnd = @LastDay Where IsNull(DateEnd, '1.1.2099')>@LastDay
	Update @TTMP Set DateStart = @FirstDay Where IsNull(DateStart, '1.1.1900')<@FirstDay

	Insert @TTMPT Select * from @TTMP  

Declare @Z int  
Declare  @T2306091348 Table  (D1Chto datetime, D2Chto datetime, D1Chem datetime, D2Chem datetime, D1New datetime, D2New datetime )
  
----------------------------------
    
  While Exists (Select * from @TTMP) -- пробегаем по переводам
  Begin
  	Select @I = MIN(PK) From @TTMP
  
		Delete @TRNTMP
		Insert @TRNTMP (D1, D2, RN) Select D1, D2, RN From @TRN Order by D1, D2

    Select @D1Chto = DateStart, @D2Chto = DateEnd
    From @TTMP Where PK = @I    
    
	  While Exists (Select * from @TRNTMP) -- пробегаем по Р/Н
	  Begin
	  	Select @Z = MIN(PK) From @TRNTMP
	    Select @D1Chem = D1, @D2Chem = D2, @RN = RN From @TRNTMP Where PK = @Z
      
	    Insert @T2306091348    
	    Select D1Chto, D2Chto, D1Chem, D2Chem, D1New, D2New 
	    from dbo.uf_GetListDatesAcross2Period(@D1Chto,@D2Chto,@D1Chem,@D2Chem,1)
	    Group by D1Chto, D2Chto, D1Chem, D2Chem, D1New, D2New 
      
      Delete @TRNTMP where PK = @Z   
    End        

    Delete @TTMP where PK = @I  
	End  
    
----------------------------------  
  
	Delete @TRNTMP
	Insert @TRNTMP (D1, D2, RN) Select D1, D2, RN From @TRN Order by D1, D2
  
  Insert @T
  (IdPerev, Tabel_nom, CodeDol, CodePodrazd, Salary, Rate, TabelDate, 
  DateStart, DateEnd, ShifrZatr, IsDopOklad, RN)
  Select IdPerev, Tabel_nom, CodeDol, CodePodrazd, Salary, Rate, 
  TabelDate, D1New, D2New, ShifrZatr, IsDopOklad, c.RN
  From @TTMPT a
  Left Join @T2306091348 b
  on a.DateStart=b.D1Chto and a.DateEnd=b.D2Chto
--  on dbo.uf_isEntry(a.DateStart,a.DateEnd,b.D1Chto,b.D2Chto)=1
  Left Join @TRNTMP c
  on c.D1=b.D1Chem and c.D2=b.D2Chem
--  on dbo.uf_isEntry(c.D1,c.D2,b.D1Chem,b.D2Chem)=1
  Group by IdPerev, Tabel_nom, CodeDol, CodePodrazd, Salary, Rate, 
  TabelDate, D1New, D2New, ShifrZatr, IsDopOklad, c.RN


--Удаляем табеля за этот период, если таковые уже имеются
	Update dbo.zrp_Tabel Set Deleted=1,DateModify=GetDate(),UserName = @MyName 
    WHERE Tabel_nom = @TN and Month(TabelDate) = @M and  Year(TabelDate) = @Y
	Set @Err_y = @@Error

	SELECT @MaxIDTabel = IsNull(MAX(a.IDTabel),0) FROM dbo.Zrp_Tabel a

-- Пихаем все в постоянную табличку
	INSERT INTO dbo.Zrp_Tabel (IdPerev, Tabel_nom, CodeDol, CodePodrazd, Salary, Rate, TabelDate, DateStart, DateEnd, ShifrZatr, IsDopOklad, RN)
	SELECT IdPerev, Tabel_nom, CodeDol, CodePodrazd, Salary, Rate, TabelDate, DateStart, DateEnd, ShifrZatr, IsDopOklad, RN
  FROM @T
	Set @Err_y = @Err_y +@@Error

    
-- Формируем для каждой записи табеля  - строки с расшифровкой каждого дня месяца (рабочий или выходной)
	INSERT INTO dbo.Zrp_TabelFull 
    (IDTabel, DayDate, IdDayType, DayNumber, MainHour) 
	SELECT a.IDTabel, IsNull(b.DayDate,0), b.IdDayType, IsNull(b.DayNumber,0), IsNull(b.MainHour,0)
	FROM dbo.vZrp_Tabel a
	LEFT JOIN dbo.Zrp_GraphDay b 
	ON b.DayDate BETWEEN a.DateStart AND a.DateEnd
	WHERE a.IDTabel > @MaxIDTabel and Tabel_Nom = @TN -- только свежедобавленные записи

--Подсчитываем плановое количество часов для каждого табеля 
	IF object_id('tempdb..#AA') IS NOT NULL DROP Table #AA
    
  SELECT IDTabel, SUM(MainHour) AS MainHour_pl, SUM(EveningHour) AS EveningHour_pl, SUM(NightHour) AS  NightHour_pl 
	INTO #AA
	FROM dbo.vZrp_TabelFull
	WHERE IDTabel > @MaxIDTabel
	GROUP BY IDTabel
	Set @Err_y = @Err_y +@@Error

--// Расстановка плановых дней и часов (у всех одинаковая)
    UPDATE dbo.Zrp_Tabel
    Set MainHour_pl = (Select SUM(MainHour) as Result from vZrp_GraphDay where month(DayDate)=@M and year(DayDate)=@Y),
	Day_pl =  (Select Count(MainHour) as Result from vZrp_GraphDay where month(DayDate)=@M and year(DayDate)=@Y and MainHour>0)
	Where (IDTabel > @MaxIDTabel) and Tabel_Nom = @TN and (TabelDate = @FirstDay) or (TabelDate = @LastDay) 

	DECLARE @T1 Table (IdTabel int primary key, TN int, IsDopOklad bit)
	
   Insert @T1 (IdTabel, TN, IsDopOklad)
   Select IdTabel, Tabel_Nom, IsDopOklad
   From dbo.Zrp_Tabel
   Where Tabel_Nom = @TN and TabelDate = @FirstDay and Deleted=0


--// Считаем количество больничных дней.

Declare @Kol int, @Kol2 int

		
	Update dbo.Zrp_TabelFull
	Set IdDayType=4, MainHour=0
	Where IdTabel in (Select IdTabel From @T1 Where TN = @TN)
		and DayDate in (Select DateBoln from dbo.uf_zp_BolnPoDnyamZaPeriod(@TN,@FirstDay,@LastDay))

	Select @Kol = Count(*) from Zrp_TabelFull 
	Where IdTabel in (Select IdTabel From @T1 Where TN = @TN and IsDopOklad=0)
			and DayDate in (Select DateBoln from dbo.uf_zp_BolnPoDnyamZaPeriod(@TN,@FirstDay,@LastDay)) and (dbo.uf_ZP_IsWorkDay(DayDate)=1)

-- 22.06.09		Update dbo.Zrp_Tabel Set DayIll = @Kol Where (Tabel_Nom = @TN) and (TabelDate = @FirstDay)

	Update dbo.Zrp_Tabel 
  Set DayIll = b.Kol 
  From dbo.Zrp_Tabel a,
  (Select IdTabel, count(*) kol 
  from dbo.Zrp_TabelFull 
  Where IDTabel > @MaxIDTabel and Deleted = 0 and (dbo.uf_ZP_IsWorkDay(DayDate)=1) and IdDayType=4
  Group by IdTabel) b
  Where a.IdTabel = B.IdTabel and a.Deleted = 0 and a.Tabel_Nom = @TN and a.TabelDate = @FirstDay and a.IdTabel = b.IdTabel


--// Считаем количество дней по среднему.

	Update Zrp_TabelFull
	Set IdDayType=9, MainHour=0
	Where IdTabel in (Select IdTabel From @T1 Where TN = @TN)
		and DayDate in (Select DateAvg from dbo.uf_zp_AvgPoDnyamZaPeriod(@TN,@FirstDay,@LastDay))

	Select @Kol = Count(*) from Zrp_TabelFull 
	Where IdTabel in (Select IdTabel From @T1 Where TN = @TN and IsDopOklad=0)
		and DayDate in (Select DateAvg from dbo.uf_zp_AvgPoDnyamZaPeriod(@TN,@FirstDay,@LastDay)) and (dbo.uf_ZP_IsWorkDay(DayDate)=1)

-- 22.06.09	Update Zrp_Tabel Set DayAverage = @Kol Where (Tabel_Nom = @TN) and (TabelDate = @FirstDay) 

	Update dbo.Zrp_Tabel 
  Set DayAverage = b.Kol 
  From dbo.Zrp_Tabel a,
  (Select IdTabel, count(*) kol 
  from dbo.Zrp_TabelFull 
  Where IDTabel > @MaxIDTabel and Deleted = 0 and (dbo.uf_ZP_IsWorkDay(DayDate)=1) and IdDayType=9
  Group by IdTabel) b
  Where a.IdTabel = B.IdTabel and a.Deleted = 0 and a.Tabel_Nom = @TN and a.TabelDate = @FirstDay and a.IdTabel = b.IdTabel

--// Считаем количество дней по отпускам

	Update dbo.Zrp_TabelFull
	Set IdDayType=5, MainHour=0
	Where IdTabel in (Select IdTabel From @T1 Where TN = @TN)
	and DayDate in (Select DateOtp from dbo.uf_zp_OtpPoDnyamZaPeriod(@TN,@FirstDay,@LastDay))
	and DayDate not in (Select D from dbo.GetOtzivDays_AtMonth_Between2Dates_4Tabel (@TN, @M,@Y))

	Select @Kol = Count(*) from Zrp_TabelFull -- отпуск
	Where IdTabel in (Select IdTabel From @T1 Where TN = @TN and IsDopOklad=0)
	and DayDate in (Select DateOtp from dbo.uf_zp_OtpPoDnyamZaPeriod(@TN,@FirstDay,@LastDay)) and (dbo.uf_ZP_IsWorkDay(DayDate)=1)

  Select @Kol2 = SUM(dbo.uf_GetKolDays_AtMonth_Between2Dates_4Tabel(D1,D2,M)) -- отзыв
  From dbo.Cat_ZP_OtzivOtpuska a
  Where a.Tabel_Nom = @TN and M = @M and Y = @Y
   
  Set @Kol = @Kol - IsNull(@Kol2,0) --* отпуск - отзыв

	Update dbo.Zrp_Tabel 
  Set DayOff = b.Kol 
  From dbo.Zrp_Tabel a,
  (Select IdTabel, count(*) kol 
  from dbo.Zrp_TabelFull 
  Where IDTabel > @MaxIDTabel and Deleted = 0 and (dbo.uf_ZP_IsWorkDay(DayDate)=1) and IdDayType=5
  Group by IdTabel) b
  Where a.IdTabel = B.IdTabel and a.Deleted = 0 and a.Tabel_Nom = @TN and a.TabelDate = @FirstDay and a.IdTabel = b.IdTabel
			

--------------------------------------------------------------
--Подсчитываем фактическое количество часов для каждого табеля 
--------------------------------------------------------------


UPDATE dbo.Zrp_Tabel 
SET Zrp_Tabel.MainHour_f = IsNull(a.MainHour_f,0), 
		Zrp_Tabel.EveningHour_f = IsNull(a.EveningHour_f,0), 
		Zrp_Tabel.NightHour_f = IsNull(a.NightHour_f,0)
FROM 
(
SELECT IDTabel, SUM(MainHour) AS MainHour_f, SUM(EveningHour) AS EveningHour_f, SUM(NightHour) AS  NightHour_f 
FROM dbo.vZrp_TabelFull
WHERE IDTabel > @MaxIDTabel 
GROUP BY IDTabel
) a
WHERE Zrp_Tabel.IDTabel = a.IDTabel and  Zrp_Tabel.Tabel_Nom = @TN 
Set @Err_y = @Err_y +@@Error

--Подсчитываем Фактическое количество дней для каждого табеля. 
--просто считаем количество рабочих дней (1).

UPDATE dbo.Zrp_Tabel 
SET Day_f =   
IsNull((SELECT Count(*)
 FROM dbo.vZrp_TabelFull a
 WHERE a.IDTabel = Zrp_Tabel.IDTabel AND MainHour <>0
 GROUP BY IDTabel),0)
WHERE IDTabel > @MaxIDTabel and Tabel_Nom = @TN 
Set @Err_y = @Err_y +@@Error


--// Для совместителей по Ф-2 

UPDATE Zrp_Tabel 
SET BonusHour = MainHour_f, MainHour_f = 0, DayBonus = Day_f, Day_f = 0
Where Tabel_Nom = @TN and (TabelDate = @FirstDay) and 
(IdPerev in (Select IdPerev from dbo.Cat_ZP_ListSotrud_Perev where Tabel_Nom = @TN and Deleted=0 and IdTipWork=2)) and (IDTabel > @MaxIDTabel) -- только свежедобавленные записи


1 сен 09, 10:22    [7601400]     Ответить | Цитировать Сообщить модератору
 Re: Цикл с выполнением процедуры  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
Паганель
(в случае 2008 можно вроде прямо таблицу в хранимку передавать, я, правда, с 2008 еще не работал)
Можно передавать параметр заранее созданного пользовательского табличного типа.
Но только в режиме READ ONLY
1 сен 09, 10:24    [7601420]     Ответить | Цитировать Сообщить модератору
 Re: Цикл с выполнением процедуры  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
Oleg Krivopusk
Вот код самой процедуры...
Я вижу два выхода

а) продолжать использовать то, что есть,
борясь с тормозами путем определения профилером маленьких кусочков Вашего кода,
где эти тормоза имеются, и оптимизации этих кусочков (плюс, возможно, создание каких-то индексов...)

б) упорядочивание всего Вашего код (а там есть что упорядочивать)
и упрощение Вашего кода (а там есть что упрощать)
а уж потом - оптимизация

Выбирать Вам

ЗЫ добиться того, чтобы процедура работала не с одним сотрудником, а со списком,
можно, как видно, только при варианте б)
1 сен 09, 10:51    [7601649]     Ответить | Цитировать Сообщить модератору
 Re: Цикл с выполнением процедуры  [new]
aleks2
Guest
Oleg Krivopusk
Есть список сотрудников (около 2500 чел) в таблице @T
Есть процедура формирования табеля по сотруднику.
Я пробегаюсь и для каждого запускаю расчет. Занимает минут 15.

Вопрос: можно ли что то усовершенствовать тут?


CREATE PORCEDURE ....
...
While Exists (Select Tabel_nom from @T) 
Begin 

  Select @PK = MIN(Tabel_nom) from @T
  
  exec dbo.up__CreateTabel4Sotrudnik_RN @FirstDay, @PK   
    
  Delete from @T Where Tabel_nom = @PK
...

End

Спасибо.


Нужно отделить мух от котлет.
1. Скока работает ЭТО

While Exists (Select Tabel_nom from @T) 
Begin 

  Select @PK = MIN(Tabel_nom) from @T
  
-- уберем  exec dbo.up__CreateTabel4Sotrudnik_RN @FirstDay, @PK   
    
  Delete from @T Where Tabel_nom = @PK
...
По результатам можно делать ОСМЫСЛЕННЫЙ выбор направления оптимизации.

2. Но в общем случае эффективнее ПЕРЕПИСАТЬ процедуру, так чтобы она в каждом своем действии обрабатывала сразу весь список....
1 сен 09, 11:00    [7601733]     Ответить | Цитировать Сообщить модератору
 Re: Цикл с выполнением процедуры  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
8 операций с zrp_Tabel
и 5 операций с zrp_TabelFull..

для спящего время бодрствования равносильно сну
1 сен 09, 11:07    [7601804]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить