Как работает индекс и кеш запросов?

[Didukh84]

Добрый день.
Возможно это уже обсуждалось, но полной информации я, к сожалению, не нашел :-(
Выполняется аксаптовский код
select sum(CostAmountPosted), sum(CostAmountAdjustment) from inventTrans
where inventTrans.InventTransId == '03045451' &&
inventTrans.StatusIssue == StatusIssue::None &&
inventTrans.StatusReceipt == StatusReceipt::Purchased &&
inventTrans.InvoiceId == '90003347';
На профайлере SQL отлавливаю данный запрос. Получаю следующее

exec sp_cursorprepexec @p1 output,@p2 output,N'@P1 varchar(1000),@P2 varchar(1000),@P3 int,@P4 int,@P5 varchar(1000)',N'SELECT SUM(A.COSTAMOUNTPOSTED),SUM(A.COSTAMOUNTADJUSTMENT) FROM INVENTTRANS A(INDEX(I_177STATUSITEMIDX)) WHERE ((DATAAREAID=@P1) AND ((((INVENTTRANSID=@P2) AND (STATUSISSUE=@P3)) AND (STATUSRECEIPT=@P4)) AND (INVOICEID=@P5))) OPTION(FAST 2)',@p5 output,@p6 output,@p7 output,'guk',' 03045451',0,1,' 90003347'

Вопрос в следующем, откуда взялся хинт по индексу I_177STATUSITEMIDX? Насколько я понимаю индекс приходит уже с приложения (проверял через Query Analyzer без него - работает быстрее и индекс берется другой). Я так понимаю, аксапта где-то хранит у себя в кеше или еще где-то данный индекс. Где это можно посмотрет? Чем руководствуется Система, когда сюда его подцепляет (данный индекс неоптимален ( )
Заранее благодарю за помощь
Версия аксапты AX3Sp3CU1, версия сервера БД SQL2005 SP2 x64, версия ОС - Win2003SP2 x64

[lev]

На сколько мне известно (могу и ошибаться) но запросы оптимизирует сама БД, т.е. подставляет индексы и т.п. Возможно в БД храниться какой то кэш об оптимальном выполнении Вашего запроса, и он берет тот план исполнения, которые считает оптимальным.

[Didukh84]

Цитата:

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

На сколько мне известно (могу и ошибаться) но запросы оптимизирует сама БД, т.е. подставляет индексы и т.п. Возможно в БД храниться какой то кэш об оптимальном выполнении Вашего запроса, и он берет тот план исполнения, которые считает оптимальным.

да, вы правы . Но в данном случае (если смотреть через профайлер) то запрос из Аксапты уже приходит с индексом! При запуске запроса без индекса через QA оптимизатор действительно подбирает оптимальный план. И следует отдать ему должное - по другому, более оптимальному, индексу

[lev]

Цитата:

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

да, вы правы . Но в данном случае (если смотреть через профайлер) то запрос из Аксапты уже приходит с индексом! При запуске запроса без индекса через QA оптимизатор действительно подбирает оптимальный план. И следует отдать ему должное - по другому, более оптимальному, индексу

Можно попробовать сбросить использование данных в ахе... может там запрос какой сохранился (строки с запросами там начинаются на "Q:", например "Q:BatchDelete"), но не уверен что поможет.

[SRF]

Цитата:

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

Добрый день.
Возможно это уже обсуждалось, но полной информации я, к сожалению, не нашел :-(
Выполняется аксаптовский код
select sum(CostAmountPosted), sum(CostAmountAdjustment) from inventTrans
where inventTrans.InventTransId == '03045451' &&
inventTrans.StatusIssue == StatusIssue::None &&
inventTrans.StatusReceipt == StatusReceipt::Purchased &&
inventTrans.InvoiceId == '90003347';
На профайлере SQL отлавливаю данный запрос. Получаю следующее

exec sp_cursorprepexec @p1 output,@p2 output,N'@P1 varchar(1000),@P2 varchar(1000),@P3 int,@P4 int,@P5 varchar(1000)',N'SELECT SUM(A.COSTAMOUNTPOSTED),SUM(A.COSTAMOUNTADJUSTMENT) FROM INVENTTRANS A(INDEX(I_177STATUSITEMIDX)) WHERE ((DATAAREAID=@P1) AND ((((INVENTTRANSID=@P2) AND (STATUSISSUE=@P3)) AND (STATUSRECEIPT=@P4)) AND (INVOICEID=@P5))) OPTION(FAST 2)',@p5 output,@p6 output,@p7 output,'guk',' 03045451',0,1,' 90003347'

Вопрос в следующем, откуда взялся хинт по индексу I_177STATUSITEMIDX? Насколько я понимаю индекс приходит уже с приложения (проверял через Query Analyzer без него - работает быстрее и индекс берется другой). Я так понимаю, аксапта где-то хранит у себя в кеше или еще где-то данный индекс. Где это можно посмотрет? Чем руководствуется Система, когда сюда его подцепляет (данный индекс неоптимален ( )
Заранее благодарю за помощь
Версия аксапты AX3Sp3CU1, версия сервера БД SQL2005 SP2 x64, версия ОС - Win2003SP2 x64

А вы уверены, что в AX именно такой код выполняется?

select sum(CostAmountPosted), sum(CostAmountAdjustment) from inventTrans where inventTrans.InventTransId == '03045451' &&
inventTrans.StatusIssue == StatusIssue::None &&
inventTrans.StatusReceipt == StatusReceipt::Purchased &&
inventTrans.InvoiceId == '90003347';

А не такой :

select sum(CostAmountPosted), sum(CostAmountAdjustment) from inventTrans
index hint StatusItemIdx
where inventTrans.InventTransId == '03045451' &&
inventTrans.StatusIssue == StatusIssue::None &&
inventTrans.StatusReceipt == StatusReceipt::Purchased &&
inventTrans.InvoiceId == '90003347';

Я так понимаю, что когда выполняется данный запрос в QA подхватывается индекс TransIdIdx?

1. Напишите джоб с данным запросом, и посмотрите, что уйдет на SQL(потом поставьте index hint TransIdIdx)

2. Вообще использование данного индекса в запросе (StatusItemIdx) навязывается самой аксаптой при помощи index hint(если функционал дорабатывался советую поискать подобный запрос и убрать hint должно стать лучше), однако если я не ошибаюсь в 4 в настройках АОС(на вкладке DataBase Tuning есть 'Allow Index Hints in queries' и если он установлен, то будет использоваться index, который навязан AX, а не тот, который СУБД посчитает лучшим). Посмотрите есть ли у вас подобный параметр(к сожалению тройки под рукой нет) и установлен ли он?
(P.S. С даным параметром нужно быть придельно аккуратным)

[ViV]

Цитата:

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

А вы уверены, что в AX именно такой код выполняется?

select sum(CostAmountPosted), sum(CostAmountAdjustment) from inventTrans where inventTrans.InventTransId == '03045451' &&
inventTrans.StatusIssue == StatusIssue::None &&
inventTrans.StatusReceipt == StatusReceipt::Purchased &&
inventTrans.InvoiceId == '90003347';

Попроовала у себя - аксапта тоже выдала запрос "SELECT SUM(A.COSTAMOUNTPOSTED),SUM(A.COSTAMOUNTADJUSTMENT) FROM INVENTTRANS A(INDEX(I_177INVENTTRANSIDSTATUSR20002))" подставив откуда то (???) свой индекс хинт. Замечала подобное поведение и на других запросах.
В 3-шке параметра 'Allow Index Hints in queries' нет.

[Wamr]

рекомендую к прочтению
http://axapta.mazzy.ru/lib/indexhints/

[AndyD]

Это ядро Аксапты, начиная с какого-то из KR-ов, вставляет собственные хинты.

Помимо отключения из конфигурации, можно для этого запроса просто добавить сортировку по полю InventTransId - в моем случае, на сервер уходит такой запрос

SELECT SUM(A.COSTAMOUNTPOSTED),SUM(A.COSTAMOUNTADJUSTMENT) 
FROM INVENTTRANS A 
WHERE ((DATAAREAID=?) AND ((((INVENTTRANSID=?) AND (STATUSISSUE=?)) AND (STATUSRECEIPT=?)) AND (INVOICEID=?))) 
OPTION(FAST 2)

[Didukh84]

Цитата:

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

А вы уверены, что в AX именно такой код выполняется?

select sum(CostAmountPosted), sum(CostAmountAdjustment) from inventTrans where inventTrans.InventTransId == '03045451' &&
inventTrans.StatusIssue == StatusIssue::None &&
inventTrans.StatusReceipt == StatusReceipt::Purchased &&
inventTrans.InvoiceId == '90003347';

А не такой :

select sum(CostAmountPosted), sum(CostAmountAdjustment) from inventTrans
index hint StatusItemIdx
where inventTrans.InventTransId == '03045451' &&
inventTrans.StatusIssue == StatusIssue::None &&
inventTrans.StatusReceipt == StatusReceipt::Purchased &&
inventTrans.InvoiceId == '90003347';

Я так понимаю, что когда выполняется данный запрос в QA подхватывается индекс TransIdIdx?

да уверене. Проверял на джобе

Цитата:

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

1. Напишите джоб с данным запросом, и посмотрите, что уйдет на SQL(потом поставьте index hint TransIdIdx)

написал. В первом случае то, что написано выше, а во-втором с TransIdIdx.
P.S. даже и хинта не нужно: просто index TransIdIdx хватило ;-)

[Didukh84]

Цитата:

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

Попроовала у себя - аксапта тоже выдала запрос "SELECT SUM(A.COSTAMOUNTPOSTED),SUM(A.COSTAMOUNTADJUSTMENT) FROM INVENTTRANS A(INDEX(I_177INVENTTRANSIDSTATUSR20002))" подставив откуда то (???) свой индекс хинт. Замечала подобное поведение и на других запросах.
В 3-шке параметра 'Allow Index Hints in queries' нет.

да, к сожалению . Кстати, еще наблюдал в ГК tablescan по ledgerTrans хотя в коде прописано

    qBd.addSortIndex(indexnum(LedgerTrans, BondBatchIdx_RU));

а потом добавлено было

qbd.indexIsHint(true);

но tablescan все равно запускается (на SQL запрос приходит без индекса!) (

[Didukh84]

Цитата:

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

рекомендую к прочтению
http://axapta.mazzy.ru/lib/indexhints/

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

[ViV]

Цитата:

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

Это ядро Аксапты, начиная с какого-то из KR-ов, вставляет собственные хинты.

У нас ни одного из KR не стоит.

[Vadik]

Цитата:

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

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

см. Кто использует KR3 ?

[SRF]

Цитата:

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

P.S. даже и хинта не нужно: просто index TransIdIdx хватило ;-)

Директива index добавляет сортировку по полям индекса и как написал AndyD (поскольку добавилась сортировка) у вас план запроса должен стать(так ли это?) :

Цитата:

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

SELECT SUM(A.COSTAMOUNTPOSTED),SUM(A.COSTAMOUNTADJUSTMENT) 
FROM INVENTTRANS A 
WHERE ((DATAAREAID=?) AND ((((INVENTTRANSID=?) AND (STATUSISSUE=?)) AND (STATUSRECEIPT=?)) AND (INVOICEID=?))) 
OPTION(FAST 2)

В случае же c index hint TransIdIdx план запроса должен стать

SELECT SUM(A.COSTAMOUNTPOSTED),SUM(A.COSTAMOUNTADJUSTMENT) 
FROM INVENTTRANS A (INDEX(I_177TRANSIDIDX))
WHERE ((DATAAREAID=?) AND ((((INVENTTRANSID=?) AND (STATUSISSUE=?)) AND (STATUSRECEIPT=?)) AND (INVOICEID=?))) 
OPTION(FAST 2)

Поиск по АОТ(искал только по коду) для AX 3.0 SP2 данного запроса ничего не дал. Поэтому попробуйте найти данный запрос в AX и захинтовать другой индекс.

P.S. Кстати в стандарной AX запросы вида

select sum(CostAmountPosted) from InventTrans where InventTrans.InventTransId == ...

в большинстве своем идут в связке с index hint TransIdIdx.(возможно это как то и связано с тем, что ядро AX само вставляет хинты в запрос)

[AndyD]

С KR поведение ядра по добавлению хинтов отличается от "чистой" Axapat'ы (без KR)

Без KR добавление ORDER BY не имеет эффекта.

Наличие же INDEX ВСЕГДА отключает хинты ядра. Причем, даже если самого индекса и не существует в базе (отключен конф. ключ). И здесь так же есть различие в поведении с KR.
Без KR в order by запроса всегда добавляются поля из индекса (для неагрегированного запроса). C KR - только если индекс не отключен в конфигурации.

Так что, с KR можно создать для таблицы фейковый индекс и использовать его для критических запросов

А вообще, выбор того или иного индекса зависит от количества полей, входящих в предложение WHERE, а так же от того, сколько этих полей входит в различные индексы (естественно, если не указывать index hint).

Алгоритм, примерно, следующий:

1. Выбирается список индексов, первое поле которых есть в предложении WHERE. Если такой индекс один, то подставляется он в INDEX HINT, если таких индексов несколько - переходим к пункту 2
2. Для каждого следующего поля выбранных индексов проверяется, входит ли оно в WHERE. Этот пункт повторяется до тех пор, пока не останется один индекс или не закончится список полей в выбранных индексах. Если остался один индекс, то используется он, если несколько, то отправляется запрос без хинта (пусть сервер сам разбирается)

Для KR наличие order by влияет на хинты, но я не уловил закономерность.
Что заметил - соответствие полей в ORDER BY какому-либо индексу и наличие этих полей в предложении WHERE в некоторых случаях отключает хинты.

PS Я пишу KR, но проверял только на KR2 (dax 3.0 sp5 kr2). Предположение, что это правильно для всех KR - надо проверять
Без KR проверял на Axapta 3.0 sp3 cu1

[lev]

Цитата:

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

...
Алгоритм, примерно, следующий:

1. Выбирается список индексов, первое поле которых есть в предложении WHERE. Если такой индекс один, то подставляется он в INDEX HINT, если таких индексов несколько - переходим к пункту 2
2. Для каждого следующего поля выбранных индексов проверяется, входит ли оно в WHERE. Этот пункт повторяется до тех пор, пока не останется один индекс или не закончится список полей в выбранных индексах. Если остался один индекс, то используется он, если несколько, то отправляется запрос без хинта (пусть сервер сам разбирается)

Для KR наличие order by влияет на хинты, но я не уловил закономерность.
Что заметил - соответствие полей в ORDER BY какому-либо индексу и наличие этих полей в предложении WHERE в некоторых случаях отключает хинты.

PS Я пишу KR, но проверял только на KR2 (dax 3.0 sp5 kr2). Предположение, что это правильно для всех KR - надо проверять
Без KR проверял на Axapta 3.0 sp3 cu1

Такой вопросик... А от БД не зависит поведение? Я имею ввиду на Оракле аха или на SQL.

[Didukh84]

Цитата:

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

Директива index добавляет сортировку по полям индекса и как написал AndyD (поскольку добавилась сортировка) у вас план запроса должен стать(так ли это?) :



В случае же c index hint TransIdIdx план запроса должен стать

SELECT SUM(A.COSTAMOUNTPOSTED),SUM(A.COSTAMOUNTADJUSTMENT) 
FROM INVENTTRANS A (INDEX(I_177TRANSIDIDX))
WHERE ((DATAAREAID=?) AND ((((INVENTTRANSID=?) AND (STATUSISSUE=?)) AND (STATUSRECEIPT=?)) AND (INVOICEID=?))) 
OPTION(FAST 2)

да, все верно

Цитата:

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

Поиск по АОТ(искал только по коду) для AX 3.0 SP2 данного запроса ничего не дал. Поэтому попробуйте найти данный запрос в AX и захинтовать другой индекс.

а можно поподробней?

Цитата:

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

P.S. Кстати в стандарной AX запросы вида

select sum(CostAmountPosted) from InventTrans where InventTrans.InventTransId == ...

в большинстве своем идут в связке с index hint TransIdIdx.(возможно это как то и связано с тем, что ядро AX само вставляет хинты в запрос)

в данном случае если бы выбрался данный индексе этой темы и не было бы ;-)

[SRF]

Цитата:

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

а можно поподробней?

Зайдите в аксапту, откройте AOT (Ctrl + D), на корне дерева(узел AOT) нажмите CTRL + F, или в контекстном меню выберите команду 'Найти', на вкладке 'Имя и позиция' в поле 'Содержащийся текст' - наберите 'sum(CostAmountAdjustment)'(без апострофов) и нажмите кнопку 'Поиск', если у вас в коде приложения есть подобные запросы, то в результате вы сможете увидеть все места откуда данный запрос генерируется.

Если таковой запрос имеется, то поставьте для него насильно

index hint TransIdIdx

чтобы брался именно данный индекс.

P.S. Конечно можно просто на InventTrans добавить индекс с полями InventTransId, StatusReceipt, StatusIssue и во все не искать данный запрос в коде. НО этот вариант наверное лучше использовать только в самом крайнем случае.

[Didukh84]

Цитата:

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

Зайдите в аксапту, откройте AOT (Ctrl + D), на корне дерева(узел AOT) нажмите CTRL + F ...

Так я знал. Но все равно спасибо. На счет места в коде откуда генерится запрос - я знаю (через трассировку вышел ;-) ).

[ZVV]

Цитата:

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

Такой вопросик... А от БД не зависит поведение? Я имею ввиду на Оракле аха или на SQL.

По моим субъективным ощущениям - на Оракле примерно то же самое...