Оптимизация запроса

[oleg_e]

День добрый! Не подскажут ли знатоки, есть ли альтернатива по тяжелым запросам использующие 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]

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

[oleg_e]

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

[belugin]

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

[ansoft]

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

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]

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

[Jabberwocky]

Цитата:

Сообщение от 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]

Цитата:

Сообщение от 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]

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

[dn]

Цитата:

Сообщение от 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]

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

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]

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

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]

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

[oleg_e]

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

[Wamr]

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

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

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

[Alexius]

Цитата:

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

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

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

[NNB]

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

Успехов