Проблемы с Exists Join

[Logger]

Коллеги поделитесь опытом.
Столкнулся с такой проблемой.
Есть метод
\Data Dictionary\Tables\FactureJour_RU\Methods\invoiceJourSortedList_CustVend

в нем исполняется запрос

    while select custVendInvoiceJour
    exists join  factureTrans
        where custVendInvoiceJour.InvoiceAccount == this.CustVendInvoiceAccount               &&
              factureTrans.Module                == this.Module                               &&
              factureTrans.FactureId             == this.FactureId                            &&
              (factureTrans.FactureLineType       == FactureLineType_RU::InvoiceLine      ||
               factureTrans.FactureLineType       == FactureLineType_RU::InvoiceEndDisc   ||
               factureTrans.FactureLineType       == FactureLineType_RU::InvoiceRoundOff)     &&
              factureTrans.InvoiceDate           == custVendInvoiceJour.InvoiceDate           &&
              factureTrans.InvoiceId             == custVendInvoiceJour.InvoiceId             &&
              factureTrans.SalesPurchId          == custVendInvoiceJour.Num                   &&
              factureTrans.NumberSequenceGroup   == custVendInvoiceJour.NumberSequenceGroupId &&
              (this.Module == FactureModule_RU::Cust ||
               (this.Module == FactureModule_RU::Vend &&
                factureTrans.InternalInvoiceId  == custVendInvoiceJour.PurchInternalInvoiceId))
    {
        if (! ret.find(custVendInvoiceJour))
        {
            ret.ins(custVendInvoiceJour);
        }
    }

из-за Exists Join оракл сперва пытается фильтровать и сортировать custVendInvoiceJour а затем для каждой записи выполняет подзапрос. Из-за это БД жутко нагружена.

Подобные же проблемы есть в методах
\Data Dictionary\Tables\FactureJour_RU\Methods\invoiceJourSortedList_TaxCorrection
\Data Dictionary\Maps\CustVendInvoiceJour\Methods\factureJourSortedList_RU

Как вы решали эти проблемы ?
Есть ли возможность заставить оракл (не изменяя запрос в Аксапте) сначала отфильтровать подзапрос, который сидит в Exists Join , а потом уже обрабатывать custVendInvoiceJour ?
В MS SQL такие проблемы встрачались ?

Я пока придумал только такой способ :
изменить
Exist Join factureTrans
на
Inner join TableId from factureTrans

в таком случае обе таблицы становятся равноправными в запросе. БД сперва обрабатывает FactureTrans - сужает выборку FactureTrans до числа строчек из одной фактуры и для такой маленькой выборки уже получает и сортирует шапки custVendInvoiceJour.

Производительность резко выросла. Нагрузка на БД упала многократно.
Незначительно выросла нагрузка на АОС из-за того что в результате выборки получается не одна запись, а столько сколько было строчек в фактуре и они все перебираются в цикле. Но это мелочь.

P.S.
Ax 3.0 SP3

[Yprit]

Цитата:

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

Я пока придумал только такой способ :
изменить
Exist Join factureTrans
на
Inner join TableId from factureTrans

Я всегда и везде использую только join tableId. В противном случае у меня на сервер уходил подзапрос.

[Logger]

Цитата:

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

Я всегда и везде использую только join tableId. В противном случае у меня на сервер уходил подзапрос.

Жаль что разработчики аксапты так не сделали

Или можно было при наличии Exists Join- ов добиться нормального выполнения запросов, не меняя код Аксапты ?

[Yprit]

Цитата:

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

Жаль что разработчики аксапты так не сделали

Или можно было при наличии Exists Join- ов добиться нормального выполнения запросов, не меняя код Аксапты ?

Честно говоря, не знаю. По-моему, на форуме это как-то обсуждалось уже. Думаю, Vadik сможет квалифицированно ответить

[Vadik]

Цитата:

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

Или можно было при наличии Exists Join- ов добиться нормального выполнения запросов, не меняя код Аксапты ?

Случайно наткнулся на ветку. Понимаю, что два года прошло, но все же.. Проблема похоже в том, что неаккуратно как-то условия на главную таблицу накладывать в подзапросе к подчиненной (не знаю как сейчас, раньше локализаторы этим грешили)

exists join  factureTrans where custVendInvoiceJour.InvoiceAccount == this.CustVendInvoiceAccount

а должно было бы быть

while select custVendInvoiceJour where custVendInvoiceJour.InvoiceAccount == this.CustVendInvoiceAccount
exists join  factureTrans

[Владимир Максимов]

Нет. Вопрос вовсе не в синтаксисе. На MS SQL при выполнении Exist Join наблюдается та же проблема (по крайней мере в AX 2.5 + MS SQL 2005).

Правда там отследить проблематично, что физически происходит, поскольку запрос "обернут" в хранимые процедуры по созданию курсоров

exec sp_cursoropen
exec sp_cursorfetch
exec sp_cursorclose

Но, судя по времени исполнения, именно что формируется курсор без Exists, а потом, внутри себя, проверяется это условие. А вот с Inner Join такого не наблюдается.

Т.е. вопрос тут не столько к разработчикам Axapta, сколько к разработчикам этих хранимых процедур от MS SQL. Поскольку если просто скопировать сам запрос из трассировки и посмотреть план его исполнения (или просто исполнить), то все "летает". А вот внутри sp_cursorfetch - тормоза страшные.

[Wamr]

Согласен с Vadik, не согласен с Владимиром Максимовым

не надо "кошмарить" системные процедуры - включите профайлинг в режиме TSQL_SPs

[Владимир Максимов]

Цитата:

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

не надо "кошмарить" системные процедуры - включите профайлинг в режиме TSQL_SPs

Это в смысле "не читал, но осуждаю"?

Ну, вот последний по времени пример, когда опять напоролся на ту же проблему

   while select ItemId, SalesQty, LineAmount, salesId
        from salesLine
        where   salesLine.SalesQty  != 0
        exists join wmsBillOfLadingOrder
//        join TableId from wmsBillOfLadingOrder
        where   wmsBillOfLadingOrder.inventTransRefId   == salesLine.SalesId
            &&  wmsBillOfLadingOrder.billOfLadingId     == "101"
    {
        info(salesLine.ItemId);
    }

Профайлер показывает время выполнения Duration = 2243. Комментирую exists join и ставлю join TableId. Профайлер показывает Duration = 21

Как говорится, "почувствуйте разницу"! На два порядка! 2243 и 21.

Если же посмотреть где именно тратятся ресурсы, то в случае с inner join небольшое время тратится на команде "exec sp_cursoropen". А вот в случае с exist открытие курсора происходит мгновенно, но бешенная задержка не первой команде "exec sp_cursorfetch" после открытия курсора

Если же выдернуть оба запроса, которые прошли через профайлер, в Managment Studio, то время их исполнения практически одинаковое. План запросов делит их в процентном отношении ровно по 50%

Хотя, согласен, что TSQL_SPs показал, что запрос никак не делится. Как был Exists, так и остался. Но, от этого ничуть не легче...

Очевидно, что внутри "exec sp_cursoropen" его обработка выполняется как-то иначе, чем в Managment Studio. И опять же, от Axapta тут ничего не зависит!

Да, для порядка, профайлер показал, что на сервер ушли такие запросы

SELECT A.ITEMID,A.SALESQTY,A.LINEAMOUNT,A.SALESID,A.RECID 
FROM SALESLINE A 
WHERE ((A.DATAAREAID='цтр') 
	AND (A.SALESQTY<>0)) 
	AND EXISTS (SELECT 'x' FROM WMSBILLOFLADINGORDER B 
		WHERE ((B.DATAAREAID='цтр') 
			AND ((B.INVENTTRANSREFID=A.SALESID) 
			AND (B.BILLOFLADINGID='101'))))

SELECT A.ITEMID,A.SALESQTY,A.LINEAMOUNT,A.SALESID,A.RECID 
FROM SALESLINE A,WMSBILLOFLADINGORDER B 
WHERE ((A.DATAAREAID='цтр') 
	AND (A.SALESQTY<>0)) 
	AND ((B.DATAAREAID='цтр') 
	AND ((B.INVENTTRANSREFID=A.SALESID) 
	AND (B.BILLOFLADINGID='101')))

План запроса в Managment Studio показывает, что в обоих случаях основное время тратится на сканирование по индексу SalesLineIdx (91%)

[Wamr]

Владимир.
На счет согласен-не согласен, я говорил в контексте первого сообщения топика и определении первопричин проблем. Чем кривей запрос в Аксапте, тем сложнее SQL-серверу подобрать правильный план. И кривизна того запроса очевидна.

В профайлере есть еще группа событий Perfomance и там есть Execution Plan. Если Вы его посмотрите для своих запросов из Аксапты, то сильно удивитесь

Еще бы я рекомендовал смотреть не Duration, а Reads - очень показательная цифра.

Цитата:

сканирование по индексу SalesLineIdx (91%)

Даже Аксапта знает, что это плохо. Мне кажется, у Вас какие-то проблемы со статистиками.

Можно Вас попросить проверить 1 "чудесный" способ на Вашем ExistsJoin.
Поменяйте, пожалуйста, в последнем where поля местами. Сначала billOfLadingId, а потом inventTransRefId.

[Владимир Максимов]

Что-то я группу событий Perfomance в профайлере MS SQL 2005 не нашел...

Reads - показывает вполне ожидаемые значения коррелирующиеся с уже озвученным выводом. Не так идет чтение, как ожидается. Даже самим MS SQL.

Exists: Duration = 2260 Reads = 480454
Inner: Duration = 21 Reads = 105

"Чудесный" способ не повлиял никак. Т.е. вообще. Разница в пределах погрешности. Duration на 1 миллисекунду изменился. Что, собственно, и следовало ожидать. Ведь речь идет не о парсинге на стороне Axapta, а о работе собственно сервера MS SQL. Ему-то какая разница в каком порядке расположены условия?

Насчет "плохо" выполнение по индексу - первый раз слышу. Я же привел цифру не времени выполнения, а стоимости этапа выполнения запроса. Поскольку основа запроса - это выборка по SalesLine - вполне естесственно, что стоимость именно этой операции будет самой высокой. Да, наверное стоит заметить, что у нас этот индекс сделан кластерным.

Насчет статистики могу сказать, что пока общее количество записей в таблице wmsBillOfLadingOrder несопоставимо меньше количества записей в SalesLine. Разница в несколько порядков.

Однако обсуждение плана выполнения запроса в среде Managment Studio - бессмысленно. Как я уже говорил, время выполнения обоих вариантов там сопоставимо и весьма быстро. Проблемы возникают именно при выполнении через хранимые процедуры. Т.е. надо смотреть какой там, внутри процедур, план выполнения.

[Wamr]

Цитата:

Сообщение от Владимир Максимов

Что-то я группу событий Perfomance в профайлере MS SQL 2005 не нашел...

надо было галочку Show all events поставить

Цитата:

Насчет "плохо" выполнение по индексу - первый раз слышу. Я же привел цифру не времени выполнения, а стоимости этапа выполнения запроса. Поскольку основа запроса - это выборка по SalesLine - вполне естесственно, что стоимость именно этой операции будет самой высокой. Да, наверное стоит заметить, что у нас этот индекс сделан кластерным.

Насчет статистики могу сказать, что пока общее количество записей в таблице wmsBillOfLadingOrder несопоставимо меньше количества записей в SalesLine. Разница в несколько порядков.

плохо Index Scan вместо Index Seek, возможно это из-за кластерности, и может быть не страшно - не знаю
Будь я оптимизатором SQL-сервера, я бы предложил
1. поиск в wmsBillOfLadingOrder по индексу BOM (есть у вас такой?)
2. Собрать все уникальные значения inventTransRefId
3. поиск в индексе в SalesLineIdx всех записей, относящихся к полученному списку
4. получение данных для определенного списка записей SalesLine
5. Дальнейшая фильтрация

Видимо, из-за кластерности п3 и 4 у вас объединены

У вас запросы на сервер так и уходят со значениями или они параметризированы?

[AndyD]

Владимир, в менеджмент студио можно точно так же использовать серверные курсоры, как это делает Аксапта.

Просто сохраняем идентификатор курсора в промежуточной переменной и подставляем его в соответствующие вызовы

[Владимир Максимов]

В плане запроса стоит Index Seek. Это я неудачно выразился

Кстати, спасибо за Perfomance. Посмотрел, какие же планы запроса формируются для курсоров. Получилось то, что я и предполагал с самого начала.

Если используется Inner Join, то сначала идет выборка по wmsBillOfLadingOrder, а затем, найденные значения используются как фильтр при поиске по SalesLine (scalar operator)

А вот в случае с Exists Join происходит обратное. Сначала выполняется поиск по SalesLine, а потом, найденные значения проверяются по wmsBillOfLadingOrder. Естесственно, получаем дикие тормоза.

При этом, если те же самые запросы напрямую выполнять в Managment Studio, то в обоих случаях сначала выполняется запрос по wmsBillOfLadingOrder, а запрос по SalesLine уже использует его результаты.

Чтобы уж совсем быть уверенным, напрямую в Managment Studio написал создание курсоров и их Fetch

DECLARE SalesLine_cursor CURSOR FOR
SELECT A.ITEMID,A.SALESQTY,A.LINEAMOUNT,A.SALESID,A.RECID 
FROM SALESLINE A,WMSBILLOFLADINGORDER B 
WHERE ((A.DATAAREAID='цтр') AND (A.SALESQTY<>0)) AND ((B.DATAAREAID='цтр') 
	AND ((B.BILLOFLADINGID='101') AND (B.INVENTTRANSREFID=A.SALESID)))

OPEN ...
FETCH ...

DECLARE SalesLine_cursor CURSOR FOR
SELECT A.ITEMID,A.SALESQTY,A.LINEAMOUNT,A.SALESID,A.RECID 
	FROM SALESLINE A WHERE ((A.DATAAREAID='цтр') AND (A.SALESQTY<>0)) 
	AND EXISTS (SELECT 'x' FROM WMSBILLOFLADINGORDER B 
			WHERE ((B.DATAAREAID='цтр') AND ((B.BILLOFLADINGID='101') 
			AND (B.INVENTTRANSREFID=A.SALESID))))

OPEN ...
FETCH ...

Результат получился такой же, как и при работе sp_cursoropen, sp_cursorfetch. Т.е. принципиально другой план выполнения запросов чем в случае прямого Select

Так что, очевидно, что просто как-то по разному работает планировщик запросов в курсорах и в прямых запросах. И от Axapta здесь вообще ничего и никак не зависит!

Конечно, возможно это происходит потому, что количество записей в SalesLine по одной компании порядка 500 тысяч, а по wmsBillOfLadingOrder - около 2 тысяч. Просто пока мало статистики по второй таблице...

[Wamr]

Цитата:

Сообщение от Владимир Максимов

Так что, очевидно, что просто как-то по разному работает планировщик запросов в курсорах и в прямых запросах.

В такой ситуации, часто применяют местную анестезию (указание индекса или перестроение запроса).
Хотя некоторым помогает "шаманский танец" с перестроением индексов и статистик на участвующих таблицах.

На постановку полноценного диагноза жалко либо время, либо денег.

[kashperuk]

Очень интересная тема, обожаю перформанс анализ.
Wamr, Владимир Максимов, AndyD - вот если бы кто-то из вас статейку еще написал по пройденному здесь диалогу? Со скриншотами, как настроить профайлер для оптимального анализа запросов, и обсуждение того, чем отличается exist join и join TableId.
Очень бы хотелось ее перевести и на блог себе запостить (с ссылочкой на автора, ессно)

Что скажете?

[Владимир Максимов]

Цитата:

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

вот если бы кто-то из вас статейку еще написал по пройденному здесь диалогу?

Рано пока. Проблема есть, но в чем она заключается не очень понятно. Точнее, не пришли к единому мнению по этому вопросу.

С моей точки зрения проблема заключается в том, что MS SQL строит разные планы запросов для кусоров, созданных через DECLARE ... CURSOR FOR (которые используются при работе Axapta) и для прямых запросов Select ... FROM (которые используются при отладке запросов в Managment Studio).

Другими словами, если запрос начинает тормозить, то просто скопировать его в Managment Studio с целью отладки может оказаться недостаточно. Т.е. в Managment Studio запрос уже "летает", а при вызове из Axapta по прежнему тормозит. В этом случае, необходимо еще "обернуть" этот запрос в курсор, чтобы привести его к реальным условиям работы.

[Владимир Максимов]

Кстати, оригинальный план запросов строится при связке SalesLine - SalesTable по аналогичной схеме. Т.е. поиск всех строк одного заказа.

В этом случае та же "фишка". В случае Exist сначала сканируется SalesLine, а потом сверяется по SalesTable. НО! При этом в запрос по SalesLine сразу передается значение SalesId взятое "из воздуха". Вот на основании чего MS SQL решает, что скаляр для SalesTable.SalesId подходит как скаляр для SalesLine.SalesId?

[Wamr]

Цитата:

Сообщение от Владимир Максимов

НО! При этом в запрос по SalesLine сразу передается значение SalesId взятое "из воздуха". Вот на основании чего MS SQL решает, что скаляр для SalesTable.SalesId подходит как скаляр для SalesLine.SalesId?

Интересно, я один не понял о чем речь?

[Владимир Максимов]

Цитата:

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

Интересно, я один не понял о чем речь?

Выполните в Managment Studio два следующих запроса. Точнее, даже выполнять не обязательно. Просто посмотрите план выполнения

SELECT A.ITEMID,A.SALESQTY,A.LINEAMOUNT,A.SALESID,A.RECID 
	FROM SALESLINE A WHERE ((A.DATAAREAID='цтр') AND (A.SALESQTY<>0)) 
	AND EXISTS (SELECT 'x' FROM SalesTable B 
			WHERE ((B.DATAAREAID='цтр') AND ((B.SalesId=right(space(20)+'827137',20)) 
			AND (B.SalesId=A.SALESID))))

SELECT A.ITEMID,A.SALESQTY,A.LINEAMOUNT,A.SALESID,A.RECID 
FROM SALESLINE A,SalesTable B 
WHERE ((A.DATAAREAID='цтр') AND (A.SALESQTY<>0)) AND ((B.DATAAREAID='цтр') 
	AND ((B.SalesId=right(space(20)+'827137',20)) AND (B.SalesId=A.SALESID)))

А потом ответьте, откуда в первом запросе этого плана по SalesLine взялось скалярное значение для SalesId?

[vanokh]

Цитата:

Сообщение от Владимир Максимов

Выполните в Managment Studio два следующих запроса. Точнее, даже выполнять не обязательно. Просто посмотрите план выполнения...
А потом ответьте, откуда в первом запросе этого плана по SalesLine взялось скалярное значение для SalesId?

Ну наверняка он сращивает вместе два условия

(B.SalesId=[COLOR=blue]right[/COLOR](space(20)+[COLOR=red]'827137'[/COLOR],20)) AND (B.SalesId=A.SALESID)

и выводит по логике, что A.SALESID = Скаляр

Проверил у себя.
Для обоих запросов выполняется Clustered Index Seek, в первом - сначала по SalesLine, потом по SalesTable и Join; во втором наоборот. Если добавить OPTION (FORCE ORDER), то планы запросов станут практически идентичными - как по использованию таблиц, так и по времени выполнения.



С курсорами планы такие же, но с одним существенным отличием - поверх добавляется вставка в Tempdb, которая занимает столько же времени, сколько cам запрос, в результате время выполнения увеличивается чуть больше чем в два (!) раза. Хотя общее время выполнения все равно осталось 0,02 с

OPTION (FORCE ORDER) для курсоров не сработал



Поскольку WMSBILLOFLADINGORDER в нашей базе пустая, то провел эксперимент для связки SalesTable (около 100 тыс в одной компании) и VendTable (3500 записей). Можете привести свои планы по запросу с WMSBILLOFLADINGORDER для сравнения?

Первый запрос -

SELECT A.CUSTACCOUNT,A.INVOICEACCOUNT,A.SALESID,A.RECID 
    FROM SALESTABLE A WHERE ((A.DATAAREAID='dvc') AND (A.SALESTYPE=3)) 
    AND EXISTS (SELECT 'x' FROM VENDTABLE B 
            WHERE ((B.DATAAREAID='com') AND ((B.ACCOUNTNUM='СПЦ') 
            AND (B.ACCOUNTNUM = A.CONSIGNORACCOUNT_RU))))

SELECT A.CUSTACCOUNT,A.INVOICEACCOUNT,A.SALESID,A.RECID 
FROM SALESTABLE A, VENDTABLE B
WHERE ((A.DATAAREAID='dvc') AND (A.SALESTYPE=3)) 
     AND ((B.DATAAREAID='com') AND ((B.ACCOUNTNUM='СПЦ') 
    AND (B.ACCOUNTNUM = A.CONSIGNORACCOUNT_RU)))

План запроса:

Estimated Subtree Cost: 19 и 16 соответственно

Он же + FORCE ORDER:

Estimated Subtree Cost: оба 19

Теперь с курсорами:

Результаты одинаковы, что без. что с FORCE ORDER: 22,6 и 18,4

Выводы:

1. для простых запросов SQL строит планы хорошо и вмешательство FORCE ORDER только ухудшает дело
2. в курсорах нельзя поменять порядок FORCE ORDER (у меня во всяком случае неполучилось, может кто из грамотных спецов по SQL подскажет почему?)
3. в курсорах запрос ко внутренней таблице стал выполняться дольше (0% - 19%, хотя данные вроде те же самые) - порядок поменять нельзя см п.2. видимо дело в логике работы Left Semi Join
4. В курсорах добавляется вставка во временную таблицу, что тоже дает небольшое замедление (FETCH возвращает одну строку)