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

Откуда:
Сообщений: 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	
23 окт 18, 10:40    [21712002]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
ondorsal
Member

Откуда:
Сообщений: 79
План запроса.

К сообщению приложен файл. Размер - 83Kb
23 окт 18, 10:43    [21712004]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
uaggster
Member

Откуда:
Сообщений: 826
А чего тут оптимизировать то?
23 окт 18, 10:50    [21712015]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
ondorsal
Member

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

Не знаю, что оптимизировать. Для меня пока это проблема. Таблицы и запрос можно менять как угодно.
В TempFact2: 2,8 млн. cтрок. В DoNotTouch4: 35 млн строк. DoNotTouch4 заполняется за 2 часа. Хотелось бы за 20 минут.
23 окт 18, 11:14    [21712075]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
ondorsal
Member

Откуда:
Сообщений: 79
uaggster,
Правда за 2 часа заполняется 10 лет на каждый день с 2007 года.
23 окт 18, 11:16    [21712083]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
court
Member

Откуда:
Сообщений: 2013
ondorsal,
1
left join, в первом запросе, смело меняешь на inner
2
зачем вообще 2-а запроса ?
подзапрос, который L оформляешь как СТЕ, и
;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
и получить всё одним запросом
23 окт 18, 11:33    [21712108]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
invm
Member

Откуда: Москва
Сообщений: 9343
Избавиться от цикла и считать нарастающий итог оконной sum. Плюс таблица-календарь.
+ Пример
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
23 окт 18, 11:34    [21712111]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
aleks222
Member

Откуда:
Сообщений: 949
ondorsal
uaggster,

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

Сгруппируй TempFact2 по датам, страдалец.
ОДИН раз.

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 )


и пользуйся.
23 окт 18, 11:52    [21712127]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить