Тормоза Аксапты в запросе по складским проводкам

[ViV]

Добрый день!

Столкнулись с непонятной ситуацией, нужна помощь в вопросе куда копать дальше:

Axapta 3.0 SP3, MS SQL 2000.

Открываем "Номенклатура" - "В наличии" - "Проводки". Аналитика на форме "В наличии" включена только склад. При открытии формы "Проводки" Аксапта довольно серьезно задумывается, молотя что-то на аос-е. Пытаемся разобраться:
В профайлере видно, что все время уходит на выполнение запроса. Сам запрос:

SELECT A.ITEMID,A.STATUSISSUE,A.DATEPHYSICAL,A.QTY,A.COSTAMOUNTPOSTED,
A.CURRENCYCODE,A.TRANSTYPE,A.TRANSREFID,A.INVOICEID,A.VOUCHER,
A.DATEEXPECTED,A.DATEFINANCIAL,A.COSTAMOUNTPHYSICAL,
A.INVENTTRANSID,A.STATUSRECEIPT,A.PACKINGSLIPRETURNED,
A.INVOICERETURNED,A.PACKINGSLIPID,A.VOUCHERPHYSICAL,
A.COSTAMOUNTADJUSTMENT,A.QTYSETTLED,A.COSTAMOUNTSETTLED,
A.VALUEOPEN,A.DIRECTION,A.DATESTATUS,A.COSTAMOUNTSTD,
A.DATECLOSED,A.DEL_CONFIGID,A.INVENTTRANSIDFATHER,A.COSTAMOUNTOPERATIONS,
A.INVENTTRANSIDRETURN,A.INVENTDIMID,A.INVENTDIMFIXED,A.DATEINVENT,
A.CUSTVENDAC,A.TRANSCHILDREFID,A.TRANSCHILDTYPE,A.REVENUEAMOUNTPHYSICAL,
A.DEL_TAXAMOUNTPHYSICAL,A.INVENTREFTRANSID,A.COSTAMOUNTSECCURPOSTED_RU,
A.COSTAMOUNTSECCURPHYSICAL_RU,A.COSTAMOUNTSECCURADJUSTMENT_RU,
A.DATECLOSEDSECCUR_RU,A.QTYSETTLEDSECCUR_RU,A.COSTAMOUNTSETTLEDSECCUR_RU,
A.VALUEOPENSECCUR_RU,A.COSTAMOUNTSTDSECCUR_RU,A.INOLAP,A.BATCHID,
A.CREATEDBY,A.RECID,B.INVENTDIMID,B.INVENTBATCHID,B.INVENTSERIALID,B.INVENTLOCATIONID,
B.INVENTGTDID_RU,B.RECID FROM INVENTTRANS A,INVENTDIM B WHERE
((A.DATAAREAID=?) AND (A.ITEMID=?)) AND ((B.DATAAREAID=?) AND 
((B.INVENTLOCATIONID=?) AND (A.INVENTDIMID=B.INVENTDIMID))) ORDER BY
A.DATAAREAID,A.ITEMID,A.DATEPHYSICAL OPTION(FAST 1)

Вроде абссолютно ничего криминального, все четко и по индексам.
При этом в Query Analyzer запрос отрабатывается за 2-3 секунды, в то время как Аксапта думает над открытием формы по пол-минуты. Да и после открытия - листая проводки в форме - Аксапта так же очень старательно задумывается.
Сервера при этом не загружены, SQL сервер тоже, статистика обновлена, с ресурсами проблем не выявлено.
В inventTrans - 4,5 млн записей, inventDim 650 тыс. Из складских аналитик включены склад, партия, серийный номер и ГТД. Еще стоит отметить, что просто форма "Номенклатура" - "Проводки" открывается быстро, видимо вся загвоздка в фильтре по inventDim.InventLocationId - но мне непонятно, почему такая существенная разница? Что еще можно глянуть для выявления причины?

[mazzy]

Цитата:

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

Вроде абссолютно ничего криминального, все четко и по индексам.
При этом в Query Analyzer запрос отрабатывается за 2-3 секунды, в то время как Аксапта думает над открытием формы по пол-минуты. Да и после открытия - листая проводки в форме - Аксапта так же очень старательно задумывается.

1. Проверяйте сеть.
2. Но скорее всего проблема в том, что задействуется tempdb для сортировки результатов выборки (в аксапте group by всегда сопровождается опцией order by, а вручную вы наверняка order by пропускаете). Поищите на форуме по поводу tempdb.

[AndyD]

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

SELECT 
A.DATAAREAID,A.ITEMID,A.INVENTDIMID,B.DATAAREAID,B.INVENTLOCATIONID,A.INVENTDIMID,B.INVENTDIMID 
FROM INVENTTRANS A,INVENTDIM B WHERE ((A.DATAAREAID=@P1) AND (A.ITEMID=@P2)) AND ((B.DATAAREAID=@P3) AND ((B.INVENTLOCATIONID=@P4) AND (A.INVENTDIMID=B.INVENTDIMID))) ORDER BY 
A.DATAAREAID,A.ITEMID OPTION(FAST 1)

т.е. ограничить выбираемые поля только теми, которые есть в поисковых индексах и убрать из сортировки поле DATEPHYSICAL

[ViV]

Цитата:

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

1. Проверяйте сеть.
2. Но скорее всего проблема в том, что задействуется tempdb для сортировки результатов выборки (в аксапте group by всегда сопровождается опцией order by, а вручную вы наверняка order by пропускаете). Поищите на форуме по поводу tempdb.

1. Точно все хорошо, иначе вылезло бы еще в куче мест.
2. Вручную order by не убирали, да и он полностью соответсвует индексу - вроде никакой сильной доп нагрузки быть не должно? Кстати сама база весит 70 гб, а размер tempdb 14 гб - это нормальное соотношение?

Цитата:

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

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

SELECT 
A.DATAAREAID,A.ITEMID,A.INVENTDIMID,B.DATAAREAID,B.INVENTLOCATIONID,A.INVENTDIMID,B.INVENTDIMID FROM INVENTTRANS A,INVENTDIM B WHERE ((A.DATAAREAID=@P1) AND (A.ITEMID=@P2)) AND ((B.DATAAREAID=@P3) AND ((B.INVENTLOCATIONID=@P4) AND (A.INVENTDIMID=B.INVENTDIMID))) ORDER BY 
A.DATAAREAID,A.ITEMID OPTION(FAST 1)

т.е. ограничить выбираемые поля только теми, которые есть в поисковых индексах и убрать из сортировки поле DATEPHYSICAL

Хм. Сделать в QueryAnalyzer? Сделали, разницы не почувствовали (план запроса тот же). Или Вы предлагаете его скормить Аксапте? Не верится, что дело только в длине передаваемой записи - ведь без фильтра по inventLocationId работает с такой же записью (набором полей), но гораздо шустрее.

[dn]

1) Сравните планы выполнения запроса через Axapta и через аналайзер. Возможно Аксапта через ядро навязывает не оптимальный индекс.

2) Попробуйте убрать сортировку по дате в аксапта

[AndyD]

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

И насчет плана запроса вы уверены?
Насколько я понимаю, планы должны отличаться как раз на пробег по страницам таблицы для выборки недостающих данных

[vc]

Цитата:

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

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

И насчет плана запроса вы уверены?
Насколько я понимаю, планы должны отличаться как раз на пробег по страницам таблицы для выборки недостающих данных

Поисковые индексы в плане запроса - ItemIdIdx таблицы InventTrans, и DimIdIdx таблицы InventDim.
Поэтому Bookmark Lookup по InventDim происходит в любом случае. Чтобы его не было, нужно из запроса убрать фильтр по складу, и убрать склад из выводимых полей.
Bookmark Lookup по InventTrans происходит в любом случае, чтобы получить InventDimId (для объединения с InventDim).

[AndyD]

Еще - приведите план исполнения для обоих запросов.

[AndyD]

Насчет InventDim, согласен.
Она у меня меня маленькая, по-этому погоды не делает. У вас влияние будет оказывать существенное.
А вот насчет InventTrans - у меня в запросе используется DimIdIdx, по-этому и букмарков нет.

Но, в любом случае, посмотрите, какая стоимость Bookmark Lookup.

[vc]

Цитата:

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

Насчет InventDim, согласен.
Она у меня меня маленькая, по-этому погоды не делает. У вас влияние будет оказывать существенное.
А вот насчет InventTrans - у меня в запросе используется DimIdIdx, по-этому и букмарков нет.

Но, в любом случае, посмотрите, какая стоимость Bookmark Lookup.

Стоимость у обоих Bookmark Lookup нулевая, по 50% стоимости уходит на Index Seek по InventTrans и InventDim

[vc]

Цитата:

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

Насчет InventDim, согласен.
Она у меня меня маленькая, по-этому погоды не делает. У вас влияние будет оказывать существенное.
А вот насчет InventTrans - у меня в запросе используется DimIdIdx, по-этому и букмарков нет.

Но, в любом случае, посмотрите, какая стоимость Bookmark Lookup.

Ну и еще, в реальной то ситуации для отображения на форме проводок, все равно понадобятся не поисковые поля (количество, код исходного документа, типы прихода, расхода из InventTrans, как минимум). Bookmark lookup представляется неизбежным

[AndyD]

Это понятно. Отсекаем лишнее, так сказать.

А план запроса такой получается в QA или при вызове из Аксапты?

[vc]

Цитата:

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

Это понятно. Отсекаем лишнее, так сказать.

А план запроса такой получается в QA или при вызове из Аксапты?

Это было в QA, сейчас попробовал в Аксапте. План получается аналогичный (стоимости нет в форме "План исполнения оператора")

[petergunn]

2 vc:
Вадим, попробуйте немного модифицировать функционал метода InventTrans.queryAddHintFromCaller() и сообщите результаты:

Client Server Static void queryAddHintFromCaller(QueryBuildDataSource qbs,Args callerArgs)
{
    switch(callerArgs.dataset())
    {
        // --> Добавлено: для InventSum использовать индекс DimIdIdx
        case(tableNum(InventSum)):
        // <-- добавлено
        case(tableNum(InventBatch)):
        case(tableNum(InventSerial)):
        case(tableNum(WMSPallet)):
        case(tableNum(WMSLocation)):

            qbs.addSortIndex(indexNum(InventTrans,DimIdIdx));
            qbs.indexIsHint(True);
            break;

        default:
            break;
    }
}

[vc]

Цитата:

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

2 vc:
Вадим, попробуйте немного модифицировать функционал метода InventTrans.queryAddHintFromCaller() и сообщите результаты:

Попробовал, результаты интересные. В плане запросов Index seek по таблице InventTrans послушно переключился на InvendDimIdx. Форма кажется стала открываться быстрее . Скроллинг по проводкам стал тормозить заметно меньше. Пропала сортировка по физической дате, вроде логично, ее нету в InventDimIdx. Пользователи сильно привыкли (очень просили) к тому, что при открытии проводок первые строки - строки резервов и предложений. Без сортировки по дате будет тяжело. Попробуем добавить физическую дату в InventDimIdx.
В общем спасибо большое, появилось направление, в котором можно поэкспериментировать.