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

Откуда:
Сообщений: 2
Недавно работаю с SQL и после написания скрипта возникла проблема, слишком долгое выполнение, страница ASP не успевает выполнить хранимую процедуру. Собственно прошу помощи с оптимизацией.


+
DECLARE
 @Naimenovanie varchar(50),@j int, @H int, @Sm1Begin smalldatetime, 
 @Sm1End smalldatetime, @Sm2End smalldatetime,@DateMesBegin smalldatetime,
 @dateArh smalldatetime,@ID_Meln int,@id_name varchar(50),
 @ResGraf real,@Res1sm  real,@Res2sm  real,@Res_sut real,@ResMes  real,
 @ResAVG  real,@ResGraf_Itogo real,@NomerSek int, @Date Smalldatetime


--drop table #MelnOF_HourSPT
CREATE TABLE #MelnOF_HourSPT
(nomer int,id_Meln int,Naimenovanie varchar(50),chas21  real,chas22  real,chas23  real,chas0  real,chas1  real,chas2  real,chas3  real,chas4  real,chas5  real,chas6  real,chas7  real,chas8  real,chas9  real,
chas10  real,chas11  real,chas12  real,chas13  real,chas14  real,chas15  real,chas16  real,chas17  real,chas18  real,chas19  real,chas20  real,Smena1 real,Smena2 real)
			
set @Date=getdate()

 -- Объявляем курсор 
DECLARE Curs_MelnSPT1 CURSOR FOR
SELECT id,Name
FROM GAS.DBO.Devices
WHERE  (Grup=23) and (id>=214) and (id<=223) and (id<>233) and (id<>234)
ORDER BY id --сортируем поле id по возрастанию 

-- Открываем курсор
OPEN Curs_MelnSPT1
-- Получаем первое значение в курсор из поля id в переменную @id_Meln @id_name
FETCH next FROM Curs_MelnSPT1 INTO @id_Meln,@id_name

-- Цикл по номерам из таблицы GAS.DBO.Devices
WHILE @@FETCH_STATUS = 0
 BEGIN

  SELECT @NomerSek=KodType
  FROM GAS.DBO.Devices
  WHERE (Grup=23) and  (ID=@id_Meln)
 

   --определение дат по сменам с 20ч до 20ч
   SET @Date=DATEADD(Hour,-DATEPART(Hour,@Date),@Date)
   SET @Sm1Begin=DATEADD(Minute,-DATEPART(Minute,@Date),@Date)+'21:00:00'-1
   SET @Sm1End=DATEADD(Minute,-DATEPART(Minute,@Date),@Date)+'8:00:00'
   SET @Sm2End=DATEADD(Minute,-DATEPART(Minute,@Date),@Date)+'20:00:00'
   --последний день предыдущего месяца +20:30:00
   set @DateMesBegin=dateadd(mm,-1,@Date)
   set @DateMesBegin=dateadd(day,-1, convert(char(6), dateadd(month,1,@DateMesBegin),112)+'01')+'21:00:00'

   --select @id_Meln as '@id_Meln',@id_name as '@id_name',@Sm1Begin as '@Sm1Begin',@Sm1End as '@Sm1End',@Sm2End as '@Sm2End'

    --среднее
    --SELECT @ResAVG=isnull(AVG(Vm3),0)
    --FROM GHH 
    --WHERE ID=@id_Meln AND Data>=@Sm1Begin AND Data<=@Sm2End

    --по сменам с 20 до 20   
  
    SELECT @Res1sm=isnull(Sum(Vm3),0)
    FROM GHH 
    WHERE ID=@id_Meln AND Data>=@Sm1Begin AND Data<=@Sm1End

    SELECT @Res2sm=isnull(Sum(Vm3),0)
       FROM GHH 
    WHERE ID=@id_Meln  AND Data>@Sm1End AND Data<=@Sm2End

    --SELECT @ResMes=isnull(Sum(Vm3),0)
    --    FROM GHH 
    --WHERE ID=@id_Meln  AND Data>=@DateMesBegin AND Data<=@Sm2End

    --set @Res1sm=CAST(@Res1sm AS NUMERIC(10,2))
   -- set @Res2sm=CAST(@Res2sm AS NUMERIC(10,2))
   -- set @Res_sut=@Res1sm+@Res2sm
      --set @ResAVG=CAST(@ResAVG AS NUMERIC(10,0))

--SELECT @ResAVG '@ResAVG',@Res1sm '@Res1sm',@Res2sm '@Res2sm',@ResMes '@ResMes'

    INSERT into  #MelnOF_HourSPT (id_Meln,Naimenovanie,Smena1,Smena2)
    SELECT  @id_Meln,@id_name,@Res1sm ,@Res2sm


   --подсчет строчки Итого
    if @id_Meln=217 or @id_Meln=219 or @id_Meln=215 or @id_Meln=221 or @id_Meln=223 or @id_Meln=225 or @id_Meln=227 or @id_Meln=229 or @id_Meln=232 or @id_Meln=236 or @id_Meln=238
	 or @id_Meln=240 or @id_Meln=242 or @id_Meln=244
     begin
          --среднее
          --SELECT @ResAVG=isnull(AVG(Vm3),0)
          -- FROM GHH 
          -- WHERE ID=@id_Meln and Data>=@Sm1Begin AND Data<=@Sm2End or ID=@id_Meln-1 and Data>=@Sm1Begin AND Data<=@Sm2End
		   --select @ResAVG as'ss'
            --по сменам с 20 до 20   
  
           SELECT @Res1sm=isnull(Sum(Vm3),0)
           FROM GHH 
           WHERE ID=@id_Meln AND Data>=@Sm1Begin AND Data<=@Sm1End or ID=@id_Meln-1 AND Data>=@Sm1Begin AND Data<=@Sm1End
		  --select  @Res1sm as 'ss'
            SELECT @Res2sm=isnull(Sum(Vm3),0)
             FROM GHH 
             WHERE  ID=@id_Meln  AND Data>@Sm1End AND Data<=@Sm2End or ID=@id_Meln-1  AND Data>@Sm1End AND Data<=@Sm2End

            --SELECT @ResMes=isnull(Sum(Vm3),0)
            --FROM GHH 
            --WHERE ID=@id_Meln  AND Data>=@DateMesBegin AND Data<=@Sm2End or ID=@id_Meln-1 AND Data>=@DateMesBegin AND Data<=@Sm2End

   
             --set @ResAVG=CAST(@ResAVG AS NUMERIC(10,0))

             set @Naimenovanie= 'Итого секция '+convert(varchar(50),@NomerSek)
		

            INSERT into  #MelnOF_HourSPT (id_meln,Naimenovanie,Smena1,Smena2)
            SELECT @NomerSek,@Naimenovanie,@Res1sm ,@Res2sm

     end 
 
 
--график по часам  
  while @Sm1Begin<=@Sm2End
   begin
   --select @Sm2end as 's'
      set @ResGraf=null
--select @ResGraf as 's'
     SELECT @ResGraf=Vm3
     FROM GHH 
     WHERE ID=@id_Meln AND Data=@Sm1Begin

   --подсчет строчки Итого по часам
      if @id_Meln=217 or @id_Meln=219 or @id_Meln=215 or @id_Meln=221 or @id_Meln=223 or @id_Meln=225 or @id_Meln=227 or @id_Meln=229 or @id_Meln=232 or @id_Meln=236 or @id_Meln=238
	 or @id_Meln=240 or @id_Meln=242 or @id_Meln=244
     begin
	  
         SELECT @ResGraf_Itogo=Sum(Vm3)
         FROM GHH 
         WHERE ID<=@id_Meln AND  ID>=@id_Meln-1 AND Data=@Sm1Begin
	
     end

     --set @ResGraf=CAST(@ResGraf AS NUMERIC(10,2))
     
     set @H =DATEPART(Hour,@Sm1Begin)
     --SELECT @ResGraf '@ResGraf',@H as '@H'

	 if @H=0  update #MelnOF_HourSPT  set chas0=@ResGraf   WHERE id_Meln=@id_Meln
     if @H=1  update #MelnOF_HourSPT  set chas1=@ResGraf   WHERE id_Meln=@id_Meln
     if @H=2  update #MelnOF_HourSPT  set chas2=@ResGraf   WHERE id_Meln=@id_Meln
     if @H=3  update #MelnOF_HourSPT  set chas3=@ResGraf   WHERE id_Meln=@id_Meln
     if @H=4  update #MelnOF_HourSPT  set chas4=@ResGraf   WHERE id_Meln=@id_Meln
     if @H=5  update #MelnOF_HourSPT  set chas5=@ResGraf   WHERE id_Meln=@id_Meln
     if @H=6  update #MelnOF_HourSPT  set chas6=@ResGraf   WHERE id_Meln=@id_Meln
     if @H=7  update #MelnOF_HourSPT  set chas7=@ResGraf   WHERE id_Meln=@id_Meln
     if @H=8  update #MelnOF_HourSPT  set chas8=@ResGraf   WHERE id_Meln=@id_Meln
     if @H=9  update #MelnOF_HourSPT  set chas9=@ResGraf   WHERE id_Meln=@id_Meln
     if @H=10 update #MelnOF_HourSPT  set chas10=@ResGraf  WHERE id_Meln=@id_Meln
     if @H=11 update #MelnOF_HourSPT  set chas11=@ResGraf  WHERE id_Meln=@id_Meln
     if @H=12 update #MelnOF_HourSPT  set chas12=@ResGraf  WHERE id_Meln=@id_Meln
     if @H=13 update #MelnOF_HourSPT  set chas13=@ResGraf  WHERE id_Meln=@id_Meln
     if @H=14 update #MelnOF_HourSPT  set chas14=@ResGraf  WHERE id_Meln=@id_Meln
     if @H=15 update #MelnOF_HourSPT  set chas15=@ResGraf  WHERE id_Meln=@id_Meln
     if @H=16 update #MelnOF_HourSPT  set chas16=@ResGraf  WHERE id_Meln=@id_Meln
     if @H=17 update #MelnOF_HourSPT  set chas17=@ResGraf  WHERE id_Meln=@id_Meln
     if @H=18 update #MelnOF_HourSPT  set chas18=@ResGraf  WHERE id_Meln=@id_Meln
     if @H=19 update #MelnOF_HourSPT  set chas19=@ResGraf  WHERE id_Meln=@id_Meln
     if @H=20 update #MelnOF_HourSPT  set chas20=@ResGraf  WHERE id_Meln=@id_Meln
     if @H=21 update #MelnOF_HourSPT  set chas21=@ResGraf  WHERE id_Meln=@id_Meln
     if @H=22 update #MelnOF_HourSPT  set chas22=@ResGraf  WHERE id_Meln=@id_Meln
     if @H=23 update #MelnOF_HourSPT  set chas23=@ResGraf  WHERE id_Meln=@id_Meln



     if @H=0  update #MelnOF_HourSPT  set chas0=@ResGraf_Itogo   WHERE id_Meln=@NomerSek
     if @H=1  update #MelnOF_HourSPT  set chas1=@ResGraf_Itogo   WHERE id_Meln=@NomerSek
     if @H=2  update #MelnOF_HourSPT  set chas2=@ResGraf_Itogo   WHERE id_Meln=@NomerSek
     if @H=3  update #MelnOF_HourSPT  set chas3=@ResGraf_Itogo   WHERE id_Meln=@NomerSek
     if @H=4  update #MelnOF_HourSPT  set chas4=@ResGraf_Itogo   WHERE id_Meln=@NomerSek
     if @H=5  update #MelnOF_HourSPT  set chas5=@ResGraf_Itogo   WHERE id_Meln=@NomerSek
     if @H=6  update #MelnOF_HourSPT  set chas6=@ResGraf_Itogo   WHERE id_Meln=@NomerSek
     if @H=7  update #MelnOF_HourSPT  set chas7=@ResGraf_Itogo   WHERE id_Meln=@NomerSek
     if @H=8  update #MelnOF_HourSPT  set chas8=@ResGraf_Itogo   WHERE id_Meln=@NomerSek
     if @H=9  update #MelnOF_HourSPT  set chas9=@ResGraf_Itogo   WHERE id_Meln=@NomerSek
     if @H=10 update #MelnOF_HourSPT  set chas10=@ResGraf_Itogo  WHERE id_Meln=@NomerSek
     if @H=11 update #MelnOF_HourSPT  set chas11=@ResGraf_Itogo  WHERE id_Meln=@NomerSek
     if @H=12 update #MelnOF_HourSPT  set chas12=@ResGraf_Itogo  WHERE id_Meln=@NomerSek
     if @H=13 update #MelnOF_HourSPT  set chas13=@ResGraf_Itogo  WHERE id_Meln=@NomerSek
     if @H=14 update #MelnOF_HourSPT  set chas14=@ResGraf_Itogo  WHERE id_Meln=@NomerSek
     if @H=15 update #MelnOF_HourSPT  set chas15=@ResGraf_Itogo  WHERE id_Meln=@NomerSek
     if @H=16 update #MelnOF_HourSPT  set chas16=@ResGraf_Itogo  WHERE id_Meln=@NomerSek
     if @H=17 update #MelnOF_HourSPT  set chas17=@ResGraf_Itogo  WHERE id_Meln=@NomerSek
     if @H=18 update #MelnOF_HourSPT  set chas18=@ResGraf_Itogo  WHERE id_Meln=@NomerSek
     if @H=19 update #MelnOF_HourSPT  set chas19=@ResGraf_Itogo  WHERE id_Meln=@NomerSek
     if @H=20 update #MelnOF_HourSPT  set chas20=@ResGraf_Itogo  WHERE id_Meln=@NomerSek
     if @H=21 update #MelnOF_HourSPT  set chas21=@ResGraf_Itogo  WHERE id_Meln=@NomerSek
     if @H=22 update #MelnOF_HourSPT  set chas22=@ResGraf_Itogo  WHERE id_Meln=@NomerSek
     if @H=23 update #MelnOF_HourSPT  set chas23=@ResGraf_Itogo  WHERE id_Meln=@NomerSek

     set @Sm1Begin=@Sm1Begin+'01:00:00'
		      
  end-- while @Sm1Begin<=@Sm2End

--переход курсора на следующую запись
 FETCH NEXT FROM Curs_MelnSPT1 INTO @id_Meln,@id_name
END --WHILE

-- Закрываем курсор
CLOSE Curs_MelnSPT1
DEALLOCATE Curs_MelnSPT1

SELECT id_Meln,Naimenovanie,chas21,chas22,chas23,chas0,chas1,chas2,chas3,chas4,chas5,chas6,chas7,chas8,chas9,chas10,chas11,chas12,chas13,chas14,chas15,chas16,chas17,chas18,chas19,chas20,Smena1,Smena2
from #MelnOF_HourSPT
--order by id_Meln

drop table #MelnOF_HourSPT


Сообщение было отредактировано: 14 дек 17, 12:22
14 дек 17, 10:00    [21032738]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация скрипта  [new]
iiyama
Member

Откуда:
Сообщений: 642
Николай.Л,

ИМХО, у Вас слишком много императивного кода => либо переписать без курсора, либо вытащить расчет на сервер приложений
14 дек 17, 10:33    [21032829]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация скрипта  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31354
Николай.Л
Недавно работаю с SQL и после написания скрипта возникла проблема, слишком долгое выполнение, страница ASP не успевает выполнить хранимую процедуру. Собственно прошу помощи с оптимизацией.
Ну, во первых, надо переписать скрипт в виде одного запроса, выкинуть эти циклы.
Потом смотреть план этого запроса, и создать необходимые индексы.
14 дек 17, 10:35    [21032834]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация скрипта  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31354
iiyama
Николай.Л,

ИМХО, у Вас слишком много императивного кода => либо переписать без курсора, либо вытащить расчет на сервер приложений
Да вроде простая логика, не вижу каких то проблем с переписыванием без курсора.
Единственно, там по GHH делается агрегирование, так что может быть и не очень быстро (впрочем, без понимания данных и бизнес логики прогнозировать трудно).
14 дек 17, 10:37    [21032847]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация скрипта  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Николай.Л,

хардкодненько :)

(id>=214) and (id<=223) and (id<>233) and (id<>234)


враг не пройдёт (id<>233) and (id<>234)
14 дек 17, 10:48    [21032875]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация скрипта  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31354
TaPaK
Николай.Л,

хардкодненько :)

(id>=214) and (id<=223) and (id<>233) and (id<>234)



враг не пройдёт (id<>233) and (id<>234)
Ну это на всякий случай, вдруг из за очередного бага мелкософта сервер неточно проверит первые условия?
14 дек 17, 10:54    [21032897]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация скрипта  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31354
alexeyvg
TaPaK
Николай.Л,

хардкодненько :)

(id>=214) and (id<=223) and (id<>233) and (id<>234)




враг не пройдёт (id<>233) and (id<>234)
Ну это на всякий случай, вдруг из за очередного бага мелкософта сервер неточно проверит первые условия?
Там в коде много таких мест, надёжно сделано, с запасом.
14 дек 17, 10:54    [21032901]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация скрипта  [new]
Николай.Л
Member

Откуда:
Сообщений: 2
alexeyvg,

там в плоть до 244 id просто это я сделал поменьше пока.
не подскажешь примерно как будет выглядеть без курсора?
14 дек 17, 11:06    [21032946]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация скрипта  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31354
Николай.Л
alexeyvg,

там в плоть до 244 id просто это я сделал поменьше пока.
не подскажешь примерно как будет выглядеть без курсора?
Ну, просто раскрывайте постепенно вычисления.
У вас главная таблица Devices, и вы выбираете 3 поля из Devices, соответсвенно, начинайте запрос из этой таблицы (сразу проставляйте алиасы, что бы не запутаться):
SELECT d.id, d.Name, d.KodType
FROM GAS.DBO.Devices as d
WHERE  (d.Grup=23) and (d.id>=214) and (d.id<=223) and (d.id<>233) and (d.id<>234)
ORDER BY d.id --сортируем поле id по возрастанию 

Далее, эту таблицу вы выводите как есть, с подсчётом Smena1,Smena2
Соответственно, считаете Smena1,Smena2:
SELECT d.id, d.Name, d.KodType, 
        isnull(Sum(case when g.Data>=@Sm1Begin AND g.Data<=@Sm1End then g.Vm3 else 0 end),0) as Smena1, 
        isnull(Sum(case when g.Data>=@Sm1End AND g.Data<=@Sm2End then g.Vm3 else 0 end),0) as Smena2
FROM GAS.DBO.Devices as d
    JOIN GHH as g on g.ID=d.id
WHERE  (d.Grup=23) and (d.id>=214) and (d.id<=223) and (d.id<>233) and (d.id<>234)
ORDER BY d.id --сортируем поле id по возрастанию 

Потом прибавляете UNION ALL вторую часть, которая "подсчет строчки Итого"
Правда, у вас строчек "Итого" по одной на каждый Devices.id, но тут уж я вашу логику разбирать не буду, сами смотрите. Причём это у вас нарастающий итог, но вычисленный неправильно, исходит из предположения, что ИД записей отличаются на 1
14 дек 17, 11:29    [21033013]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить