Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Оптимизация запроса / 8 сообщений из 8, страница 1 из 1
23.10.2018, 10:40
    #39721292
ondorsal
ondorsal
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
Коллеги, помогите, пожалуйста, оптимизировать запрос.
Используется для расчетов начальных и конечных остатков на дату.
Верхняя часть считает остатки по датам, в которых были движения по товару, складу, типу движения, документу и группе документов.
Нижняя часть считает, если на эту дату не было движения по товара.
Код: sql
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.
USE [DB]
GO
drop table DoNotTouch4
GO
CREATE TABLE [DoNotTouch4](
[YMD] [date] NOT NULL,
[ProductID] [int] NOT NULL,
[SkladId] [int] NOT NULL,
[MovingTypeId] [int]  NOT NULL,
[DocTypeId] [int] NOT NULL,
[DocGroupId] [int] NOT NULL,
[BegRest] numeric(10, 2),
[InOut] numeric(10, 2),
[EndRest] numeric(10, 2)
)
GO
declare @ss as datetime
declare @ss2 as datetime
set @ss = '2016-01-01 00:00:00.000'    --YYYY.MM.DD
set @ss2 = '2017-01-01 00:00:00.000'   --YYY.MM.DD

while @ss<=@ss2
begin

insert into DoNotTouch4
select L.YMD,L.ProductId,L.SkladId,L.MovingTypeId,L.DocTypeId,L.DocGroupId, (L.EndRest-isnull(R.InOut,0)) as BegRest, isnull(R.InOut,0) as InOut,L.EndRest
from(
SELECT @ss as YMD
       ,T.[ProductID]
      ,T.[SkladId]
      ,T.[MovingTypeId]
      ,T.[DocTypeId]
      ,T.[DocGroupId]
, Sum(T.InOut)as EndRest
	    FROM [DB].[dbo].[TempFact2] as T
  where T.[YMD]<=@ss 
  group by T.[ProductID]
      ,T.[SkladId]
      ,T.[MovingTypeId]
      ,T.[DocTypeId]
	  ,T.[DocGroupId]) as L left outer join [DB].[dbo].[TempFact2] as R
	   on L.YMD=R.[YMD]
     and L.ProductId=R.ProductId
     and L.SkladId=R.SkladId 
	 and L.MovingTypeId=R.MovingTypeId
	 and L.DocTypeId=R.DocTypeId
	 and L.DocGroupId=R.DocGroupId
	 where abs(isnull(R.InOut,0))>0 -- там, где есть движения на дату

insert into DoNotTouch4
select L.YMD,L.ProductId,L.SkladId,1 as MovingTypeId, 1 as DocTypeId, 10001 as DocGroupId,
sum((L.EndRest-isnull(R.InOut,0))) as BegRest, Sum(isnull(R.InOut,0)) as InOut, sum(L.EndRest) as EndRest
from(
SELECT @ss as YMD
       ,T.[ProductID]
      ,T.[SkladId]
      ,T.[MovingTypeId]
      ,T.[DocTypeId]
      ,T.[DocGroupId]
, Sum(T.InOut)as EndRest
	    FROM [DB].[dbo].[TempFact2] as T
  where T.[YMD]<=@ss 
  group by T.[ProductID]
      ,T.[SkladId]
      ,T.[MovingTypeId]
      ,T.[DocTypeId]
	  ,T.[DocGroupId]) as L left outer join [DB].[dbo].[TempFact2] as R
	   on L.YMD=R.[YMD]
     and L.ProductId=R.ProductId
     and L.SkladId=R.SkladId 
	 and L.MovingTypeId=R.MovingTypeId
	 and L.DocTypeId=R.DocTypeId
	 and L.DocGroupId=R.DocGroupId
	 where isnull(R.InOut,0)=0  and  (abs(isnull(R.InOut,0)) + abs(L.EndRest))>0--там, где нет движений на дату
	 group by L.YMD,L.ProductId,L.SkladId
having abs(sum(isnull(R.InOut,0)))+abs(sum(L.EndRest))>0
option (maxdop 1)
  set @ss=@ss+1
  end
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.10.2018, 10:43
    #39721293
ondorsal
ondorsal
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
План запроса.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.10.2018, 10:50
    #39721299
uaggster
uaggster
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
А чего тут оптимизировать то?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.10.2018, 11:14
    #39721317
ondorsal
ondorsal
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
uaggster,

Не знаю, что оптимизировать. Для меня пока это проблема. Таблицы и запрос можно менять как угодно.
В TempFact2: 2,8 млн. cтрок. В DoNotTouch4: 35 млн строк. DoNotTouch4 заполняется за 2 часа. Хотелось бы за 20 минут.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.10.2018, 11:16
    #39721321
ondorsal
ondorsal
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
uaggster,
Правда за 2 часа заполняется 10 лет на каждый день с 2007 года.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.10.2018, 11:33
    #39721336
court
court
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
ondorsal,
1
left join, в первом запросе, смело меняешь на inner
2
зачем вообще 2-а запроса ?
подзапрос, который L оформляешь как СТЕ, и
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
;with cte as (

)
insert ...

select ...
from cte inner join [DB].[dbo].[TempFact2] as R
where ...

union all

select ...
from cte left join [DB].[dbo].[TempFact2] as R
where ...


3
нуу и в идеале, ваще убрать цикл
сформировать 2-е времянки (вместо L) : одна сгруппированная на дату @ss, и вторая - по дням между @ss и @ss2
и получить всё одним запросом
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.10.2018, 11:34
    #39721338
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
Избавиться от цикла и считать нарастающий итог оконной sum. Плюс таблица-календарь.
 
Пример
Код: sql
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.
use tempdb;
go

create table dbo.Calendar (d date primary key);
insert into dbo.Calendar
 (d)
 select top (1000)
  dateadd(day, row_number() over (order by (select 1)), '20180101')
 from
  master.dbo.spt_values a cross join
  master.dbo.spt_values b;

create table dbo.t (id int identity primary key, store_id int, product_id int, d date, quantity int);
insert into dbo.t
 (store_id, product_id, d, quantity)
values
 (1, 1, '20181001', 1), (1, 1, '20181003', 1), (1, 1, '20181011', -1);
go

with t as
(
 select
  store_id, product_id, d,
  sum(quantity) over (partition by store_id, product_id order by d) as EndRest,
  sum(quantity) over (partition by store_id, product_id order by d) - quantity as BegRest,
  lead(d) over (partition by store_id, product_id order by d) as next_d 
 from
  dbo.t
)
select
 t.store_id, t.product_id, isnull(c.d, t.d), t.BegRest, t.EndRest 
from
 t outer apply
 (select d from dbo.Calendar where t.d < t.next_d and d >= t.d and d < t.next_d) c
order by
 t.store_id, t.product_id, isnull(c.d, t.d)
go

drop table dbo.Calendar, dbo.t;
go

...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.10.2018, 11:52
    #39721345
aleks222
aleks222
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
ondorsaluaggster,

Не знаю, что оптимизировать. Для меня пока это проблема. Таблицы и запрос можно менять как угодно.
В TempFact2: 2,8 млн. cтрок. В DoNotTouch4: 35 млн строк. DoNotTouch4 заполняется за 2 часа. Хотелось бы за 20 минут.
Сгруппируй TempFact2 по датам, страдалец.
ОДИН раз.

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
SELECT ss = cast(YMD as date )
                  ,T.[ProductID]
                  ,T.[SkladId]
                  ,T.[MovingTypeId]
                  ,T.[DocTypeId]
                  ,T.[DocGroupId]
                  , Sum(T.InOut) as EndRest

  into #TempFact2

  FROM [DB].[dbo].[TempFact2] as T
  where T.[YMD] between @ss  and  @ss2 
  group by T.[ProductID]
      ,T.[SkladId]
      ,T.[MovingTypeId]
      ,T.[DocTypeId]
      ,T.[DocGroupId]
      , cast(YMD as date )



и пользуйся.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Оптимизация запроса / 8 сообщений из 8, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


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