ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Оптимизация запроса
8 сообщений из 8, страница 1 из 1
Оптимизация запроса
    #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:40
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #39721293
ondorsal
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ondorsal
Гость
План запроса.
...
Рейтинг: 0 / 0
23.10.2018, 10:43
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #39721299
uaggster
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
uaggster
Гость
А чего тут оптимизировать то?
...
Рейтинг: 0 / 0
23.10.2018, 10:50
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #39721317
ondorsal
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ondorsal
Гость
uaggster,

Не знаю, что оптимизировать. Для меня пока это проблема. Таблицы и запрос можно менять как угодно.
В TempFact2: 2,8 млн. cтрок. В DoNotTouch4: 35 млн строк. DoNotTouch4 заполняется за 2 часа. Хотелось бы за 20 минут.
...
Рейтинг: 0 / 0
23.10.2018, 11:14
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #39721321
ondorsal
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ondorsal
Гость
uaggster,
Правда за 2 часа заполняется 10 лет на каждый день с 2007 года.
...
Рейтинг: 0 / 0
23.10.2018, 11:16
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #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:33
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #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:34
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #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
23.10.2018, 11:52
| Ответить | Цитировать | Написать  
8 сообщений из 8, страница 1 из 1
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Оптимизация запроса
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=46&tid=1688927]:
 0ms
get settings:
 9ms
get forum list:
 9ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 164ms
get topic data:
 8ms
get forum data:
 4ms
get page messages:
 31ms
get tp. blocked users:
 1ms
others: 241ms
total:  473ms
созд. / загр.: 473ms
x
x
Закрыть


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