Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Оптимизация скрипта / 9 сообщений из 9, страница 1 из 1
14.12.2017, 10:00:42
    #39569445
Николай.Л
Николай.Л
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация скрипта
Недавно работаю с SQL и после написания скрипта возникла проблема, слишком долгое выполнение, страница ASP не успевает выполнить хранимую процедуру. Собственно прошу помощи с оптимизацией.


 
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
159.
160.
161.
162.
163.
164.
165.
166.
167.
168.
169.
170.
171.
172.
173.
174.
175.
176.
177.
178.
179.
180.
181.
182.
183.
184.
185.
186.
187.
188.
189.
190.
191.
192.
193.
194.
195.
196.
197.
198.
199.
200.
201.
202.
203.
204.
205.
206.
207.
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

...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.12.2017, 10:33:45
    #39569477
iiyama
iiyama
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация скрипта
Николай.Л,

ИМХО, у Вас слишком много императивного кода => либо переписать без курсора, либо вытащить расчет на сервер приложений
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.12.2017, 10:35:04
    #39569478
alexeyvg
alexeyvg
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация скрипта
Николай.ЛНедавно работаю с SQL и после написания скрипта возникла проблема, слишком долгое выполнение, страница ASP не успевает выполнить хранимую процедуру. Собственно прошу помощи с оптимизацией.Ну, во первых, надо переписать скрипт в виде одного запроса, выкинуть эти циклы.
Потом смотреть план этого запроса, и создать необходимые индексы.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.12.2017, 10:37:23
    #39569479
alexeyvg
alexeyvg
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация скрипта
iiyamaНиколай.Л,

ИМХО, у Вас слишком много императивного кода => либо переписать без курсора, либо вытащить расчет на сервер приложенийДа вроде простая логика, не вижу каких то проблем с переписыванием без курсора.
Единственно, там по GHH делается агрегирование, так что может быть и не очень быстро (впрочем, без понимания данных и бизнес логики прогнозировать трудно).
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.12.2017, 10:48:48
    #39569485
TaPaK
TaPaK
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация скрипта
Николай.Л,

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

Код: sql
1.
(id>=214) and (id<=223) and (id<>233) and (id<>234)



враг не пройдёт (id<>233) and (id<>234)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.12.2017, 10:54:13
    #39569489
alexeyvg
alexeyvg
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация скрипта
TaPaKНиколай.Л,

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

Код: sql
1.
(id>=214) and (id<=223) and (id<>233) and (id<>234)




враг не пройдёт (id<>233) and (id<>234)Ну это на всякий случай, вдруг из за очередного бага мелкософта сервер неточно проверит первые условия?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.12.2017, 10:54:47
    #39569491
alexeyvg
alexeyvg
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация скрипта
alexeyvgTaPaKНиколай.Л,

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

Код: sql
1.
(id>=214) and (id<=223) and (id<>233) and (id<>234)





враг не пройдёт (id<>233) and (id<>234)Ну это на всякий случай, вдруг из за очередного бага мелкософта сервер неточно проверит первые условия?Там в коде много таких мест, надёжно сделано, с запасом.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.12.2017, 11:06:49
    #39569501
Николай.Л
Николай.Л
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация скрипта
alexeyvg,

там в плоть до 244 id просто это я сделал поменьше пока.
не подскажешь примерно как будет выглядеть без курсора?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.12.2017, 11:29:53
    #39569518
alexeyvg
alexeyvg
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация скрипта
Николай.Лalexeyvg,

там в плоть до 244 id просто это я сделал поменьше пока.
не подскажешь примерно как будет выглядеть без курсора?Ну, просто раскрывайте постепенно вычисления.
У вас главная таблица Devices, и вы выбираете 3 поля из Devices, соответсвенно, начинайте запрос из этой таблицы (сразу проставляйте алиасы, что бы не запутаться):
Код: sql
1.
2.
3.
4.
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:
Код: sql
1.
2.
3.
4.
5.
6.
7.
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
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Оптимизация скрипта / 9 сообщений из 9, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 512ms
Закрыть
start [/forum/topic.php?fid=46&tablet=1&tid=1690677]:
 0ms
get settings:
 6ms
get forum list:
 14ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 218ms
get topic data:
 7ms
get forum data:
 2ms
get page messages:
 38ms
get tp. blocked users:
 1ms
others: 220ms
total:  512ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]