Оптимизация запроса - DAX: Программирование

oleg_e · 10.01.2008, 12:10

День добрый! Не подскажут ли знатоки, есть ли альтернатива по тяжелым запросам использующие exists join? Есть следующий запрос (см.ниже), использующий тяжелые таблицы (inventtrans>4 млн строк, inventdim>300 тыс, табл. А и B >500 тыс). Сейчас время отчета с использованием этого запроса более 40 мин (при выбранном периоде 1 мес)
Можно ли модифицироать запрос? Будем считать, что индексы расставлены.
Использование forcenestedloop forceselectorder ненамного сократило время.
Прошу прощения, на неформатированность запроса

while select inventTrans
where ((inventTrans.TransType == InventTransType::Purch) || (inventTrans.TransType == InventTransType::Sales))
&& (includeEstimated || ( ! includeEstimated &&
((inventTrans.StatusReceipt == StatusReceipt::None && inventTrans.StatusIssue == StatusIssue::Sold) ||
(inventTrans.StatusReceipt == StatusReceipt::Purchased && inventTrans.StatusIssue == StatusIssue::None))))
&& (inventTrans.DateFinancial
&& inventTrans.DateFinancial >= dateFrom && inventTrans.DateFinancial <= dateTo) ||
(!inventTrans.DateFinancial
&& inventTrans.DateExpected >= dateFrom && inventTrans.DateExpected <= dateTo))
join [fields] from inventTable
where inventTable.ItemId == inventTrans.ItemId &&
inventTable.ItemType != .....
join [fields] from inventDim
where inventDim.InventDimId == inventTrans.InventDimId
&& (.........)
exists join [fields] from A
where A.[X] == inventDim.[Y]
join B
where B.InventDimId == A.InventDimId
&& ((B.DateFinancial && B.DateFinancial >= dateFrom
&& B.DateFinancial <= dateTo) ||
( !B.DateFinancial && B.DateExpected >= dateFrom && B.DateExpected <= dateTo))

Zuzaz · 10.01.2008, 12:39

Как только в условии запроса появляется "или", про индекс можно забыть. Постарайтесь перестроить условие запроса, чтобы "или" не было вообще, либо они были как можно более ниже в условии.

oleg_e · 10.01.2008, 12:54

Без "ИЛИ" никак нельзя...

belugin · 10.01.2008, 13:22

1. Что говорит план запроса?
2. Какое наиболее селективное условие запроса?

ansoft · 10.01.2008, 13:47

Что если перестроить запрос так:

while select [fields] from inventTable
where inventTable.ItemType != .....

join [fields] from inventTrans
where inventTrans.ItemId == inventTable.ItemId &&
((inventTrans.TransType == InventTransType::Purch) || (inventTrans.TransType == InventTransType::Sales))
&& (includeEstimated || ( ! includeEstimated &&
((inventTrans.StatusReceipt == StatusReceipt::None && inventTrans.StatusIssue == StatusIssue::Sold) ||
(inventTrans.StatusReceipt == StatusReceipt::Purchased && inventTrans.StatusIssue == StatusIssue::None))))
&& (inventTrans.DateFinancial
&& inventTrans.DateFinancial >= dateFrom && inventTrans.DateFinancial <= dateTo) ||
(!inventTrans.DateFinancial
&& inventTrans.DateExpected >= dateFrom && inventTrans.DateExpected <= dateTo))

join [fields] from inventDim
where inventDim.InventDimId == inventTrans.InventDimId
&& (.........)

exists join [fields] from A
where A.[X] == inventDim.[Y]

join B
where B.InventDimId == A.InventDimId
&& ((B.DateFinancial && B.DateFinancial >= dateFrom
&& B.DateFinancial <= dateTo) ||
( !B.DateFinancial && B.DateExpected >= dateFrom && B.DateExpected <= dateTo))

Есть мысль, что это сократит "терзания" InventTrans (+InventDim) и будет быстрее...

oleg_e · 10.01.2008, 13:59

спасибо, попробую и скажу

Jabberwocky · 10.01.2008, 14:25

Цитата:

Сообщение от ansoft

Что если перестроить запрос так:

while select [fields] from inventTable
where inventTable.ItemType != .....

В данном случае необходимо также использовать forcenestedloop forceselectorder, чтобы цикл join'a начинался именно с InventTable. И еще попробуйте соптимизировать критерии выборки, например условие:

inventTrans.StatusReceipt == StatusReceipt::None && inventTrans.StatusIssue == StatusIssue::Sold

эквивалентно:

inventTrans.StatusIssue == StatusIssue::Sold ,

т.к. StatusReceipt и StatusIssue одновременно никогда не бывают не-None.

Аналогично,
inventTrans.DateFinancial
&& inventTrans.DateFinancial >= dateFrom && inventTrans.DateFinancial <= dateTo

проверка на непустую дату inventTrans.DateFinancial явно избыточна, если dateFrom и dateTo заведомо не пустые и т.д.

Alexius · 10.01.2008, 14:58

Цитата:

Сообщение от oleg_e

Будем считать, что индексы расставлены.

А можно полюбопытствовать, какие ?

1. Рекомендую избавится от использования в условии запроса переменной includeEstimated, использовав несколько специализированных запросов и при необходимости подпроцедуру для тела цикла
2. Вместо конструкций вида

Цитата:

Сообщение от oleg_e

((inventTrans.StatusReceipt == StatusReceipt::None && inventTrans.StatusIssue == StatusIssue::Sold) ||
(inventTrans.StatusReceipt == StatusReceipt::Purchased && inventTrans.StatusIssue == StatusIssue::None))

в Аксапте лучше использовать

(inventTrans.StatusReceipt == StatusReceipt::None || inventTrans.StatusReceipt == StatusReceipt::Purchased) &&
(inventTrans.StatusIssue == StatusIssue::None || inventTrans.StatusIssue == StatusIssue::Sold)

3. Даты тоже можно немного оптимизировать, меняем

Цитата:

Сообщение от oleg_e

(inventTrans.DateFinancial
&& inventTrans.DateFinancial >= dateFrom && inventTrans.DateFinancial <= dateTo) ||
(!inventTrans.DateFinancial
&& inventTrans.DateExpected >= dateFrom && inventTrans.DateExpected <= dateTo))

на

inventTrans.DateFinancial >= dateFrom
&& (inventTrans.DateFinancial && inventTrans.DateFinancial <= dateTo ||
(!inventTrans.DateFinancial
&& inventTrans.DateExpected >= dateFrom && inventTrans.DateExpected <= dateTo))

PS. А лучше всего поймать этот запрос в MS SQL Profiler и посмотреть его план выполнения

Alexius · 10.01.2008, 15:23

4. Если из inventTrans нужны не все поля, то лучше их перечислить в запросе для уменьшения результирующего объема выборки

dn · 10.01.2008, 15:28

Цитата:

Сообщение от oleg_e

(inventTrans.DateFinancial
&& inventTrans.DateFinancial >= dateFrom && inventTrans.DateFinancial <= dateTo) ||
(!inventTrans.DateFinancial
&& inventTrans.DateExpected >= dateFrom && inventTrans.Date<= dateTo)

Скорее всего в данном случае вполне можно вместо условия по полям DateFinancial и DateExpected написать условие по одной дате DateStatus. Алгоритм заполнения DateStatus в методе inventTrans.setStatusDate().

oleg_e · 10.01.2008, 16:53

Спасибо всем. Немного конкретезирую запрос (было до этого абстрактно):

while select inventTrans
where ((inventTrans.TransType == InventTransType::Purch) || (inventTrans.TransType == InventTransType::Sales))
&& (includeEstimated || ( ! includeEstimated &&
((inventTrans.StatusReceipt == StatusReceipt::None && inventTrans.StatusIssue == StatusIssue::Sold) ||
(inventTrans.StatusReceipt == StatusReceipt::Purchased && inventTrans.StatusIssue == StatusIssue::None))))
&& (transByPeriod
&& ((inventTrans.DateFinancial
&& inventTrans.DateFinancial >= dateFrom
&& inventTrans.DateFinancial <= dateTo) ||
( ! inventTrans.DateFinancial
&& inventTrans.DateExpected >= dateFrom
&& inventTrans.DateExpected <= dateTo)) || (!transByPeriod))
join ItemCategoryId from inventTable
where inventTable.ItemId == inventTrans.ItemId &&
inventTable.ItemType != ItemType::.....
join DlvInventTripId from inventDim
where inventDim.InventDimId == inventTrans.InventDimId
&& (( ! tripId && inventDim.DlvInventTripId != "") || (tripId && inventDim.DlvInventTripId == tripId))
exists join DlvInventTripId from inventDimTripByPeriod
where inventDimTripByPeriod.[field] == inventDim.[field]
join inventTransByPeriod
where inventTransByPeriod.InventDimId == inventDimTripByPeriod.InventDimId
&& ((inventTransByPeriod.DateFinancial
&& inventTransByPeriod.DateFinancial >= dateFrom
&& inventTransByPeriod.DateFinancial <= dateTo) ||
( ! inventTransByPeriod.DateFinancial
&& inventTransByPeriod.DateExpected >= dateFrom
&& inventTransByPeriod.DateExpected <= dateTo))

Пояснение к запросу
inventTransByPeriod это inventtrnas
inventDimTripByPeriod это inventDim

признаки (NOYES) для запроса:
includeEstimated,transByPeriod

Alexius · 10.01.2008, 17:34

Вторая серия, для начала:

1. Индекс на таблицу inventTrans с 3-мя полями DateFinancial, DateExpected, InventDimId

2. Слегка модифицированный запрос

while select inventTrans
where ((inventTrans.TransType == InventTransType::Purch) || (inventTrans.TransType == InventTransType::Sales))
&& (includeEstimated || ( ! includeEstimated &&
((inventTrans.StatusReceipt == StatusReceipt::None && inventTrans.StatusIssue == StatusIssue::Sold) ||
(inventTrans.StatusReceipt == StatusReceipt::Purchased && inventTrans.StatusIssue == StatusIssue::None))))
&& (transByPeriod
&& ((inventTrans.DateFinancial
&& inventTrans.DateFinancial >= dateFrom
&& inventTrans.DateFinancial <= dateTo) ||
( ! inventTrans.DateFinancial
&& inventTrans.DateExpected >= dateFrom
&& inventTrans.DateExpected <= dateTo)) || (!transByPeriod))
join ItemCategoryId from inventTable
where inventTable.ItemId == inventTrans.ItemId &&
inventTable.ItemType != ItemType::.....
join DlvInventTripId from inventDim
where inventDim.InventDimId == inventTrans.InventDimId
&& (( ! tripId && inventDim.DlvInventTripId != "") || (tripId && inventDim.DlvInventTripId == tripId))
exists join DlvInventTripId from inventDimTripByPeriod
where inventDimTripByPeriod.[field] == inventDim.[field]
join inventTransByPeriod
where inventTransByPeriod.InventDimId == inventDimTripByPeriod.InventDimId
&& inventTransByPeriod.DateFinancial <= dateTo
&& ((inventTransByPeriod.DateFinancial
&& inventTransByPeriod.DateFinancial >= dateFrom) ||
( ! inventTransByPeriod.DateFinancial
&& inventTransByPeriod.DateExpected >= dateFrom
&& inventTransByPeriod.DateExpected <= dateTo))

NNB · 10.01.2008, 17:37

Что будет если запустить запрос на сервере с помощью скажем E...M...?
Обязательно ли использовать While select?

oleg_e · 10.01.2008, 18:52

Alexius,
а нужно ли в запросе использовать волшебные слова forcenestedloop forceselectorder?

Wamr · 10.01.2008, 19:13

Не надо делать таких запросов.
- структура ограничений, а, следовательно, и план исполнения сильно зависят от входных параметров (includeEstimated,transByPeriod, tripId). Такое построение часто приводит к неожиданным зависаниям, даже при малом объеме выбираемых данных.
- разобраться через неделю, что делается в запросе сложно. А чтобы что-то подправить через месяц, придется полдня только вспоминать что же тут понаписано.
Предлагаю использовать queryRun или последовательность select cursor - while cursor - next cursor. И перестраивать запрос в заисимости от входных параметров, а не взрывать мозг SQL-серверу.

Попробуйте сами продумать план запроса. Какие ограничения должны примениться первыми, какие вторыми. Только после этого можно думать о применении "волшебных слов forcenestedloop forceselectorder"

Опишите простыми словами, что же вы тут пытаитесь выбрать.

Alexius · 11.01.2008, 09:37

Цитата:

Сообщение от oleg_e

Alexius,
а нужно ли в запросе использовать волшебные слова forcenestedloop forceselectorder?

Это лучше определить экспериментальным путем. Я воздержусь давать универсальную рекомендацию.

NNB · 11.01.2008, 10:22

Добрый день
Конкретизирую что написал вчера
Как бы я поступил:
1. Понял бы куда уходит время: на создание курсора или движение по нему
2. Если на создание - то проверил бы наличие индексов на полях связи и ограничениях, запустил бы запрос на SQL сервере и постарался оптимизировать там.
3. Если получилось - то постарался бы перенести это в Axaptу. Если не получилось перенести, то постарался бы использовать хранимую процедуру на сервере. Если на сервере запрос работает долго - значит не повезло. Остается только постараться получать промежуточные данные в пакетном режиме и затем использовать их
4. Если задержка вызвана движением по курсору - то постарался бы обойтись без него

Успехов

Похожие темы
Тема	Автор	Раздел	Ответов	Посл. сообщение
Оптимизация запроса - ranges	kashperuk	DAX: Программирование	13	31.01.2011 20:19
Изменить план выполнения запроса	Sequel	DAX: Администрирование	2	29.05.2008 15:46
Быстродействие запроса	Antonuch	DAX: Программирование	1	25.01.2008 15:58
Опять оптимизация запроса	KpecT	DAX: Программирование	3	02.11.2007 14:41
Оптимизация запроса	Янка	DAX: Программирование	1	27.04.2006 08:37