![]() |
#1 |
Участник
|
Проблемы с Exists Join
Коллеги поделитесь опытом.
Столкнулся с такой проблемой. Есть метод \Data Dictionary\Tables\FactureJour_RU\Methods\invoiceJourSortedList_CustVend в нем исполняется запрос X++: 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); } } Подобные же проблемы есть в методах \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 |
|
![]() |
#2 |
Злыдни
|
|
|
![]() |
#3 |
Участник
|
|
|
![]() |
#4 |
Злыдни
|
|
|
![]() |
#5 |
Модератор
|
Цитата:
X++: exists join factureTrans where custVendInvoiceJour.InvoiceAccount == this.CustVendInvoiceAccount X++: while select custVendInvoiceJour where custVendInvoiceJour.InvoiceAccount == this.CustVendInvoiceAccount exists join factureTrans
__________________
-ТСЯ или -ТЬСЯ ? |
|
|
За это сообщение автора поблагодарили: Logger (3). |
![]() |
#6 |
Участник
|
Нет. Вопрос вовсе не в синтаксисе. На 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 - тормоза страшные. |
|
![]() |
#7 |
----------------
|
Согласен с Vadik, не согласен с Владимиром Максимовым
не надо "кошмарить" системные процедуры - включите профайлинг в режиме TSQL_SPs Последний раз редактировалось Wamr; 16.04.2010 в 16:35. |
|
![]() |
#8 |
Участник
|
Цитата:
![]() Ну, вот последний по времени пример, когда опять напоролся на ту же проблему X++: 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); } Как говорится, "почувствуйте разницу"! На два порядка! 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'))) |
|
|
За это сообщение автора поблагодарили: gl00mie (3). |
![]() |
#9 |
----------------
|
Играем дальше
Владимир.
На счет согласен-не согласен, я говорил в контексте первого сообщения топика и определении первопричин проблем. Чем кривей запрос в Аксапте, тем сложнее SQL-серверу подобрать правильный план. И кривизна того запроса очевидна. В профайлере есть еще группа событий Perfomance и там есть Execution Plan. Если Вы его посмотрите для своих запросов из Аксапты, то сильно удивитесь ![]() Еще бы я рекомендовал смотреть не Duration, а Reads - очень показательная цифра. Цитата:
сканирование по индексу SalesLineIdx (91%)
Можно Вас попросить проверить 1 "чудесный" способ на Вашем ExistsJoin. Поменяйте, пожалуйста, в последнем where поля местами. Сначала billOfLadingId, а потом inventTransRefId. |
|
![]() |
#10 |
Участник
|
Что-то я группу событий Perfomance в профайлере MS SQL 2005 не нашел...
Reads - показывает вполне ожидаемые значения коррелирующиеся с уже озвученным выводом. Не так идет чтение, как ожидается. Даже самим MS SQL. Exists: Duration = 2260 Reads = 480454 Inner: Duration = 21 Reads = 105 "Чудесный" способ не повлиял никак. Т.е. вообще. Разница в пределах погрешности. Duration на 1 миллисекунду изменился. ![]() Насчет "плохо" выполнение по индексу - первый раз слышу. Я же привел цифру не времени выполнения, а стоимости этапа выполнения запроса. Поскольку основа запроса - это выборка по SalesLine - вполне естесственно, что стоимость именно этой операции будет самой высокой. Да, наверное стоит заметить, что у нас этот индекс сделан кластерным. Насчет статистики могу сказать, что пока общее количество записей в таблице wmsBillOfLadingOrder несопоставимо меньше количества записей в SalesLine. Разница в несколько порядков. Однако обсуждение плана выполнения запроса в среде Managment Studio - бессмысленно. Как я уже говорил, время выполнения обоих вариантов там сопоставимо и весьма быстро. Проблемы возникают именно при выполнении через хранимые процедуры. Т.е. надо смотреть какой там, внутри процедур, план выполнения. |
|
![]() |
#11 |
Участник
|
Владимир, в менеджмент студио можно точно так же использовать серверные курсоры, как это делает Аксапта.
Просто сохраняем идентификатор курсора в промежуточной переменной и подставляем его в соответствующие вызовы
__________________
Axapta v.3.0 sp5 kr2 |
|
![]() |
#12 |
----------------
|
Цитата:
Цитата:
Насчет "плохо" выполнение по индексу - первый раз слышу. Я же привел цифру не времени выполнения, а стоимости этапа выполнения запроса. Поскольку основа запроса - это выборка по SalesLine - вполне естесственно, что стоимость именно этой операции будет самой высокой. Да, наверное стоит заметить, что у нас этот индекс сделан кластерным.
Насчет статистики могу сказать, что пока общее количество записей в таблице wmsBillOfLadingOrder несопоставимо меньше количества записей в SalesLine. Разница в несколько порядков. Будь я оптимизатором SQL-сервера, я бы предложил 1. поиск в wmsBillOfLadingOrder по индексу BOM (есть у вас такой?) 2. Собрать все уникальные значения inventTransRefId 3. поиск в индексе в SalesLineIdx всех записей, относящихся к полученному списку 4. получение данных для определенного списка записей SalesLine 5. Дальнейшая фильтрация Видимо, из-за кластерности п3 и 4 у вас объединены У вас запросы на сервер так и уходят со значениями или они параметризированы? |
|
|
За это сообщение автора поблагодарили: kashperuk (5), alex55 (1). |
![]() |
#13 |
Участник
|
В плане запроса стоит 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 ... Так что, очевидно, что просто как-то по разному работает планировщик запросов в курсорах и в прямых запросах. И от Axapta здесь вообще ничего и никак не зависит! Конечно, возможно это происходит потому, что количество записей в SalesLine по одной компании порядка 500 тысяч, а по wmsBillOfLadingOrder - около 2 тысяч. Просто пока мало статистики по второй таблице... |
|
|
За это сообщение автора поблагодарили: kashperuk (5). |
![]() |
#14 |
Участник
|
Очень интересная тема, обожаю перформанс анализ.
Wamr, Владимир Максимов, AndyD - вот если бы кто-то из вас статейку еще написал по пройденному здесь диалогу? Со скриншотами, как настроить профайлер для оптимального анализа запросов, и обсуждение того, чем отличается exist join и join TableId. Очень бы хотелось ее перевести и на блог себе запостить ![]() Что скажете? |
|
![]() |
#15 |
Участник
|
Кстати, оригинальный план запросов строится при связке SalesLine - SalesTable по аналогичной схеме. Т.е. поиск всех строк одного заказа.
В этом случае та же "фишка". В случае Exist сначала сканируется SalesLine, а потом сверяется по SalesTable. НО! При этом в запрос по SalesLine сразу передается значение SalesId взятое "из воздуха". Вот на основании чего MS SQL решает, что скаляр для SalesTable.SalesId подходит как скаляр для SalesLine.SalesId? |
|
![]() |
#16 |
----------------
|
|
|
![]() |
#17 |
Участник
|
Цитата:
С моей точки зрения проблема заключается в том, что MS SQL строит разные планы запросов для кусоров, созданных через DECLARE ... CURSOR FOR (которые используются при работе Axapta) и для прямых запросов Select ... FROM (которые используются при отладке запросов в Managment Studio). Другими словами, если запрос начинает тормозить, то просто скопировать его в Managment Studio с целью отладки может оказаться недостаточно. Т.е. в Managment Studio запрос уже "летает", а при вызове из Axapta по прежнему тормозит. В этом случае, необходимо еще "обернуть" этот запрос в курсор, чтобы привести его к реальным условиям работы. |
|
|
За это сообщение автора поблагодарили: alex55 (1). |
![]() |
#18 |
Участник
|
Выполните в Managment Studio два следующих запроса. Точнее, даже выполнять не обязательно. Просто посмотрите план выполнения
X++: 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))) |
|
![]() |
#19 |
----------------
|
X++: ((B.SalesId=right(space(20)+'827137',20)) AND (B.SalesId=A.SALESID)) Данные то у меня другие, так что и планы могут у нас быть разными. |
|
![]() |
#20 |
----------------
|
Цитата:
Хотя некоторым помогает "шаманский танец" с перестроением индексов и статистик на участвующих таблицах. На постановку полноценного диагноза жалко либо время, либо денег. Последний раз редактировалось Wamr; 20.04.2010 в 23:29. |
|
Теги |
ax3.0, exists, oracle, sql server |
|
Опции темы | Поиск в этой теме |
Опции просмотра | |
|