Здравствуйте!
Ребят прошу вашей помощи в оптимизации SQL запроса.
Задача:
Есть очень большая таблица (7 миллионов и это только за год), данные могут быть разбросаны по всей таблице и нужные данные могут быть в переделе одной недели т.е. в пределах примерно в 300 тысяч строк.
Нужно найти повторения строк в разрезе одного документа, и определить что данные повторения выполняются последовательно одним сотрудником. И в конце посчитать сколько раз повторения строк были выполнены сотрудником не последовательно, либо вообще другим сотрудником.
Кусок таблицы:
Сейчас получился вот такой запрос:
Код:
DECLARE @StartDate as datetime
DECLARE @FinishDate as datetime
DECLARE @ExternalCode as varchar(30)
set @StartDate = '2013-12-09 08:00:15.100'
set @FinishDate = GETDATE()
--set @Document
DECLARE @doc table (
TID int,
Documents varchar(20) null,
Article varchar(20) null,
NameRU varchar(255) null,
SourseLocations varchar(20) null,
SourseOS varchar(20) null,
ProductionResources varchar(50) null,
TargetOS varchar(20) null,
Quantity int null,
Units varchar(20) null,
FinishDate datetime null,
Namber int IDENTITY (1,1) not null,
Quantity_SKU int null,
Razriv int null
)
/*Заполняем временную таблицу данными с сортировкой (Документ, Сотрудник, Дата/Время выполнения)*/
insert into @doc (TID, Documents, Article, NameRU, SourseLocations, SourseOS, ProductionResources, TargetOS, Quantity, Units, FinishDate)
select
mp.tid,
DR.ExternalCode,
m.NameEN,
m.NameRU,
MP.SourceLocationName,
MP.SourceStorageObjectName,
MP.TargetLocationName,
MP.TargetStorageObjectName,
MP.BaseQuantity,
MP.AlternateMaterialUnitName,
mp.FinishDate
from
hdr_DeliveryRequest as DR with (nolock)
join Transactions as T1 with (nolock) on DR.Transaction_id = T1.tid
join Transactions as T2 with (nolock) on T2.ParentTransaction_id = T1.tid
join Transactions as T3 with (nolock) on T3.ParentTransaction_id = T2.tid
join hdr_MaterialPicking as MP with (nolock) on MP.Transaction_id = T3.tid
join Materials as M with (nolock) on MP.Material_id = M.tid
where mp.FinishDate > ISNULL(@StartDate, mp.FinishDate)
and mp.FinishDate < ISNULL(@FinishDate, mp.FinishDate)
and DR.ExternalCode like ISNULL(@ExternalCode, DR.ExternalCode)
and mp.TransactionStatus = 1
group by
mp.tid,
DR.ExternalCode,
m.NameEN,
m.NameRU,
MP.SourceLocationName,
MP.SourceStorageObjectName,
MP.TargetLocationName,
MP.TargetStorageObjectName,
MP.BaseQuantity,
MP.AlternateMaterialUnitName,
mp.FinishDate
order by
DR.ExternalCode,
MP.TargetLocationName,
mp.FinishDate
/*Считаем количество повторении позиции внутри документа, в штучно/коробочном разрезе*/
DECLARE @Document as varchar(20)
DECLARE @TID as INT
DECLARE @ARTICLE as varchar(20)
DECLARE @Quantity_SKU as int
DECLARE @Unit as varchar(10)
while (select COUNT(*) from @doc where Quantity_SKU is null) > 0
BEGIN
select
@TID = t.TID,
@ARTICLE = t.Article,
@Document = t.Documents,
@Unit = t.Units
from
(select TOP 1 TID, Article, Documents, Units from @doc where Quantity_SKU is null order by TID DESC) as t
if
@Unit = 'Штука'
select @Quantity_SKU = COUNT(Article) from @doc where Documents = @Document and Article = @ARTICLE and Units like 'Штука'
else
select @Quantity_SKU = COUNT(Article) from @doc where Documents = @Document and Article = @ARTICLE and Units not like 'Штука'
UPDATE @doc SET Quantity_SKU = @Quantity_SKU where TID = @TID
end
/*Считаем разрывы, на основе разницы местоположения строк одинокового товара внутри одного документа*/
DECLARE @Document2 as varchar(20)
DECLARE @TID2 as INT
DECLARE @ARTICLE2 as varchar(20)
DECLARE @Quantity_SKU2 as int
DECLARE @Unit2 as varchar(10)
while (select COUNT(*) from @doc where Razriv is null) > 0
BEGIN
select
@TID2 = t2.TID,
@ARTICLE2 = t2.Article,
@Document2 = t2.Documents,
@Quantity_SKU2 = t2.Quantity_SKU,
@Unit2 = t2.Units
from
(select TOP 1 TID, Article, Documents, Quantity_SKU, Units from @doc where Razriv is null order by Documents, ProductionResources, FinishDate) as t2
if
@Unit2 = 'Штука'
update @doc set Razriv = (select MAX(Namber) + 1 from @doc where Documents = @Document2 and Article = @ARTICLE2 and Units like 'Штука') - (select MIN(Namber) from @doc where Documents = @Document2 and Article = @ARTICLE2 and Units like 'Штука')
where TID = @TID2 and Razriv is null and Units like 'Штука'
else
update @doc set Razriv = (select MAX(Namber) + 1 from @doc where Documents = @Document2 and Article = @ARTICLE2 and Units not like 'Штука') - (select MIN(Namber) from @doc where Documents = @Document2 and Article = @ARTICLE2 and Units not like 'Штука')
where TID = @TID2 and Razriv is null and Units not like 'Штука'
end
/*Выводим разрывы, на основе отличия местоположения позиции одинакового товара внутри документа, и количества повторении строки с одинаковым товаром внутри документа*/
select COUNT(*) as 'Разрыв штук' from @doc where Quantity_SKU <> Razriv and Units like 'Штука'
select COUNT(*) as 'Разрыв коробок' from @doc where Quantity_SKU <> Razriv and Units not like 'Штука'
Отрабатывает данный запрос за 14 минут всего 20 тысяч строк, это очень медленно, как оптимизировать, у меня уже идей нет.
