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

Откуда:
Сообщений: 8
Здравствуйте!
Ребят прошу вашей помощи в оптимизации 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 тысяч строк, это очень медленно, как оптимизировать, у меня уже идей нет
9 дек 13, 15:53    [15265399]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
pirat89
Member

Откуда:
Сообщений: 8
В принципе хватит пинка в нужном направлении, ну или указания возможных ресурсоёмких действий.
Также, предугадывая ответы скажу, что я понимаю то что запрос отрабатывает долго из-за циклов, но без них не вижу вариантов правильной логики запроса.
9 дек 13, 16:02    [15265489]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31429
pirat89
Отрабатывает данный запрос за 14 минут всего 20 тысяч строк, это очень медленно, как оптимизировать, у меня уже идей нет
Что долго отрабатывает - первый запрос по заполнению @doc или расчёты?

Расчёты конечно нужно переделать с циклов в запросы.
Если никак, то можно хотя бы сделать @doc временной таблицей и построить индексы, а то у вас там сплошные джойны по сканам...
9 дек 13, 16:09    [15265551]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
pirat89
Member

Откуда:
Сообщений: 8
Заполнение @doc выполняется быстро всего 8 секунд на 20 тысяч строк, долго работают сами расчёты.
Без циклов вариантов не вижу.
9 дек 13, 16:13    [15265586]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
pirat89
Member

Откуда:
Сообщений: 8
автор
Если никак, то можно хотя бы сделать @doc временной таблицей и построить индексы, а то у вас там сплошные джойны по сканам..

ммм... @doc же и так временная, или предлагаете сделать её реальную и построить к ней индексы?
9 дек 13, 16:16    [15265605]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31429
pirat89
Без циклов вариантов не вижу.
Ну смотрите внимательнее...

Допустим, первый цикл легко заменяется простеньким запросом:
/*Считаем количество повторении позиции внутри документа, в штучно/коробочном разрезе*/
UPDATE d
SET Quantity_SKU = (
		SELECT COUNT(Article) 
		from @doc d1 
		where d1.Documents = d.Documents 
			and d1.Article = d.Article 
			and (
				(d.Units = 'Штука' and d1.Units = 'Штука')
				or
				(d.Units <> 'Штука' and d1.Units <> 'Штука')
			)
	)
from @doc d
9 дек 13, 16:21    [15265655]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31429
pirat89
ммм... @doc же и так временная, или предлагаете сделать её реальную и построить к ней индексы?
@doc не временная, это табличная переменная. Временные таблицы начинаются с символа #

Смотрите в хелпе статью про временные таблицы и про разницу с табличными переменными.
Постоянную табюлицу не надо ни в коем случае.
9 дек 13, 16:22    [15265662]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
pirat89
Member

Откуда:
Сообщений: 8
alexeyvg, спасибо за советы!
Буду пробовать.
9 дек 13, 16:26    [15265705]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
pirat89
Member

Откуда:
Сообщений: 8
alexeyvg
pirat89
Без циклов вариантов не вижу.
Ну смотрите внимательнее...

Допустим, первый цикл легко заменяется простеньким запросом:
/*Считаем количество повторении позиции внутри документа, в штучно/коробочном разрезе*/
UPDATE d
SET Quantity_SKU = (
		SELECT COUNT(Article) 
		from @doc d1 
		where d1.Documents = d.Documents 
			and d1.Article = d.Article 
			and (
				(d.Units = 'Штука' and d1.Units = 'Штука')
				or
				(d.Units <> 'Штука' and d1.Units <> 'Штука')
			)
	)
from @doc d


Или я что то не понимаю, или такой запрос никогда не выдаст нужные данные, из за "or" мне то нужно считать раздельно 'Штуки' и не 'Штуки'
9 дек 13, 16:42    [15265873]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
pirat89
Member

Откуда:
Сообщений: 8
Сейчас попробую с временной таблицей посмотрю что получится. Надеюсь поможет.
9 дек 13, 16:46    [15265907]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31429
pirat89
Или я что то не понимаю, или такой запрос никогда не выдаст нужные данные, из за "or" мне то нужно считать раздельно 'Штуки' и не 'Штуки'
Да, не понимаете.

Этот запрос считает отдельно для строк d.Units = 'Штука' и d.Units <> 'Штука'

Логически запрос можно представить себе так:

Делается цикл по строкам @doc d
Для каждой строки вычисляется количество записей в подзапросе.
Причём если у данной строки Units = 'Штука', то в поздапросе берутся записи, у которых d1.Units = 'Штука'
И наоборот.
И так для каждой строки.

Это логически так, построчно. Физически сервер построит наиболее эффективный план, исходя из статистики по количеству строк с разными значениями полей. Но для вас это неважно, важно для начала обеспечить логическую правильность, а планы можно смотреть уже позже, во время оптимизации.

Конечно, нужно заменить табличные переменные на временные таблицы и построить индексы.
Я вижу 2 фильтрованных индекса на поля Documents и Article с фильтрами Units = 'Штука' и Units <> 'Штука'
9 дек 13, 17:13    [15266132]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
pirat89
Member

Откуда:
Сообщений: 8
Тему можно закрывать!
Спасибо alexeyvg за дельные советы и расширенные ответы!
10 дек 13, 03:12    [15268169]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Jaffar
Member

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

разберитесь cross apply и outer apply - реально очень удобно и полезно
в них можно делать top N + сортировку и вообще все что угодно.
математически ~ выполнению запроса для каждой строки вышестоящего множества.

благодаря им почти всегда можно избавиться от уродливых и корявых(трудно_читаемых) подзапросов, говно_циклов, нет нужды писать 100500 update`ов - если вы вообще просто делаете select.




select *
from @doc d
---
outer apply(select count(*) cnt
			from @doc d1
			where
					d1.Documents = d.Documents 
			and		d1.Article = d.Article 
			and    (   (d.Units  = 'Штука' and d1.Units  = 'Штука') 
					or (d.Units <> 'Штука' and d1.Units <> 'Штука')) /**/ ) d1



Вообщем призываю вас братья - имейте в своем арсенале apply.
10 дек 13, 09:14    [15268598]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Jaffar
Member

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



кстати еще - индексы можно построить и по табл. переменным, неявно правда - но можно.
например если
declare @__TEMP table(
ID int primary key, -- будет создан индекс
IDDoc int not NULL,
IDWorker varchar(255) not NULL,
unique(IDDoc, IDWorker) -- тоже будет создан индекс.
)
10 дек 13, 09:18    [15268620]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
iap
Member

Откуда: Москва
Сообщений: 46999
Jaffar
alexeyvg,



кстати еще - индексы можно построить и по табл. переменным, неявно правда - но можно.
например если
declare @__TEMP table(
ID int primary key, -- будет создан индекс
IDDoc int not NULL,
IDWorker varchar(255) not NULL,
unique(IDDoc, IDWorker) -- тоже будет создан индекс.
)
Больше смысла в UNIQUE(IDDoc,ID) - это, по существу, необязательно уникальный индекс
по некоторому (любому) полю IDDoc благодаря уникальности ID.
10 дек 13, 09:39    [15268741]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить