Оптимизация запросов к БД в коде

[Ace of Database]

Цитата:

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

Вероятнее всего проблема с изменением порядка полей и последующим ускорением/замедлением запросов лежит в плоскости оптимизатора аксапты, а не sql. В dax3 с какого-то kr оптимизатор аксапты начал сам подставлят индексы в запросы согласно условиям where.

Да, согласен, такое может быть.

[Logger]

Цитата:

Сообщение от Ace of Database

Да, согласен, такое может быть.

А вот чтоб это проверить неплохо бы посмотреть какие у вас реально запросы уходили к БД и какой она им план запроса ставила.

Наверняка там был какой-нить левый хинт !

Не должна БД так странно глючить.

[fed]

Не претендую на истину в последней инстанции, но с точки зрения стоимости владения дешевле один раз настроить регулярное обновление статистики, отключить использование хинтов и настроить перестроение фрагментированных индексов, чем каждый раз править кусок кода. Кроме того, какого-то рационального объяснения приведенному методу не существует. Он МОГ теоретически срабатывать для Oracle до версии 7.10 включительно при использовании rule based optimizer. Для MS SQL метод бессмысленнен для любой версии и любых условий. Даже если он сработает - это единичное совпадение, на основании которого нельзя делать выводов.

[fed]

Есть такая передача - Малахов Плюс. Дык там народ тоже делиться рассказами что после того как он попил отвара чертополоха или посыпал подушку пеплом от дубовой коры у него прошел геморой или радикулит.
Надо бы Малахову предложить сделать туда отдельную секцию Малахов+АйТи, в которой народные целители делились бы их собственными секретами повышения производительности SQL-запросов путем перестановки условий, уточняли бы, какой заговор следует читать в процессе закрытия склада и какие именно дни по лунному календарю наиболее благоприятны для построения книги продаж...
P.S. Я обычно не стебусь над другими участниками и не перехожу на личности. Но как-то уж у автора теории ник очень уж не соответствует взглядам на принципы оптимизации СУБД.

[Ace of Database]

Цитата:

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

Есть такая передача - Малахов Плюс. Дык там народ тоже делиться рассказами что после того как он попил отвара чертополоха или посыпал подушку пеплом от дубовой коры у него прошел геморой или радикулит.
Надо бы Малахову предложить сделать туда отдельную секцию Малахов+АйТи, в которой народные целители делились бы их собственными секретами повышения производительности SQL-запросов путем перестановки условий, уточняли бы, какой заговор следует читать в процессе закрытия склада и какие именно дни по лунному календарю наиболее благоприятны для построения книги продаж...
P.S. Я обычно не стебусь над другими участниками и не перехожу на личности. Но как-то уж у автора теории ник очень уж не соответствует взглядам на принципы оптимизации СУБД.

Fed: скажите, что вредного я предложил в моей "теории"?

[fed]

Ладно - расскладываю по шагам:
1. Сделано наблюдение о повышении производительности после замены порядка условий в запросе.(Возможно вполне справедливое).
2. Попыток использования доступных средств анализа ситуации (попросту говоря - посмотреть план запроса до и после) сделано не было (документация по тому как это делать входит в BOL)
3. Попыток проанализировать возможные причины повышения производительности после смены порядка полей сделано не было. (Документация по оптимизатору запросов входит в BOL).
4. Был сделан вывод что повышение производительности связано с изменением порядка полей. (Уверждение спорное, поскольку причины низкой производительности не были выявлены).
5. Был сделан вывод о применимости подхода в общем случае.
6. Не было попыток сравнить чем этот подход лучше/хуже альтернативных (например - обновления статистики или отключения хинтов).
Пункты 4 и 5 - просто классические логические ошибки (попытка сделать вывод о причинно-следственной связи последовательных событий и некорректное обобщение от частного случая к общему). Пункты 2,3 и 6 говорят, на мой взгляд, об отсутствии инженерного подхода к проблеме.

А народные целители, у которых прошла болячка после того как они попили какой-то отвар совершают абсолютно такие же ошибки в рассуждениях:
1. Болячка прошла после того как попили отвар.
2. Анализа причин возникновения болячки и попыток проверить действительно ли она прошла (может только симптомы пропали) не делается
3.Попыток проанализировать причины положительного эффекта сделано не было (Чертополох повышает секрецию желчи, повышение секреции желчи разжижает стул, жидкий стул снижает нагрузку на геморройные узлы)
4. Был сделан вывод что прохождение болячки связано с приемом отвара (возможно болячка прошла из за того что больной сменил пищевой режим или стал больше двигаться).
5. Был сделан вывод о применимости метода в общем случае.
6. Не было сделано анализа применимости метода в сравнении с альтернативами (возможно проще недельку попринимать дешовые таблетки из аптеки, чем месяц париться со сбором, приготовлением и приемом чертополоха).

[raz]

Вот пристали к человеку...

ЕЩЁ РАЗ!!! Проблема в DAX, она считает себя умнее SQL!!!

Вот вам доказательства, DAX3 KR кажется 2 SP4 Standart:

static void Job1(Args _args)
{
    InventTrans                 InventTrans;
    ;
    select InventTrans
        where InventTrans.InventTransId == "л00000380";

    info(InventTrans.InventTransId);

    select InventTrans
        where InventTrans.InventTransId == "л00000380"
           && InventTrans.ItemId        == "СвОф-1000"
           && InventTrans.StatusIssue   == StatusIssue::OnOrder
           && InventTrans.DateStatus    == 30\04\2006
           && InventTrans.StatusReceipt == StatusReceipt::None;

    info(InventTrans.InventTransId);

    select InventTrans
        where InventTrans.StatusReceipt == StatusReceipt::None
           && InventTrans.StatusIssue   == StatusIssue::OnOrder
           && InventTrans.ItemId        == "СвОф-1000"
           && InventTrans.DateStatus    == 30\04\2006
           && InventTrans.InventTransId == "л00000380";

    info(InventTrans.InventTransId);
}

Планы запросов записанные аксаптой:

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.ITEMROUTEID,A.ITEMBOMID,A.INVENTTRANSIDRETURN,A.PROJID,A.PROJCATEGORYID,A.INVENTDIMID,A.INVENTDIMFIXED,A.DATEINVENT,A.CUSTVENDAC,A.TRANSCHILDREFID,A.TRANSCHILDTYPE,A.TIMEEXPECTED,A.REVENUEAMOUNTPHYSICAL,A.ASSETID,A.PROJADJUSTREFID,A.DEL_TAXAMOUNTPHYSICAL,A.ASSETBOOKID,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.RECVERSION,A.RECID FROM INVENTTRANS A WITH( INDEX(I_177TRANSIDIDX)) WHERE ((DATAAREAID=?) AND (INVENTTRANSID=?)) OPTION(FAST 20)

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.ITEMROUTEID,A.ITEMBOMID,A.INVENTTRANSIDRETURN,A.PROJID,A.PROJCATEGORYID,A.INVENTDIMID,A.INVENTDIMFIXED,A.DATEINVENT,A.CUSTVENDAC,A.TRANSCHILDREFID,A.TRANSCHILDTYPE,A.TIMEEXPECTED,A.REVENUEAMOUNTPHYSICAL,A.ASSETID,A.PROJADJUSTREFID,A.DEL_TAXAMOUNTPHYSICAL,A.ASSETBOOKID,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.RECVERSION,A.RECID FROM INVENTTRANS A WITH( INDEX(I_177STATUSITEMIDX)) WHERE ((DATAAREAID=?) AND (((((INVENTTRANSID=?) AND (ITEMID=?)) AND (STATUSISSUE=?)) AND (DATESTATUS=?)) AND (STATUSRECEIPT=?))) OPTION(FAST 20)

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.ITEMROUTEID,A.ITEMBOMID,A.INVENTTRANSIDRETURN,A.PROJID,A.PROJCATEGORYID,A.INVENTDIMID,A.INVENTDIMFIXED,A.DATEINVENT,A.CUSTVENDAC,A.TRANSCHILDREFID,A.TRANSCHILDTYPE,A.TIMEEXPECTED,A.REVENUEAMOUNTPHYSICAL,A.ASSETID,A.PROJADJUSTREFID,A.DEL_TAXAMOUNTPHYSICAL,A.ASSETBOOKID,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.RECVERSION,A.RECID FROM INVENTTRANS A WITH( INDEX(I_177STATUSITEMIDX)) WHERE ((DATAAREAID=?) AND (((((STATUSRECEIPT=?) AND (STATUSISSUE=?)) AND (ITEMID=?)) AND (DATESTATUS=?)) AND (INVENTTRANSID=?))) OPTION(FAST 20)

[fed]

Цитата:

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

Вот пристали к человеку...

ЕЩЁ РАЗ!!! Проблема в DAX, она считает себя умнее SQL!!!

Вот вам доказательства, DAX3 KR кажется 2 SP4 Standart:

Может быть глобальное отключение хинтов (возможное и в трешке кстати), более эффективно чем ручное переупорядочивание полей в запросах ?

[raz]

Цитата:

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

Может быть глобальное отключение хинтов (возможное и в трешке кстати), более эффективно чем ручное переупорядочивание полей в запросах ?

согласен, но кто поручится, что в анализаторе DAX не осталось еще каких "улучшающих" алгоритмов?

[fed]

Цитата:

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

согласен, но кто поручится, что в анализаторе DAX не осталось еще каких "улучшающих" алгоритмов?

А кто гарантирует что эти алгоритмы можно заблокировать переставляя поля в условиях запроса ?

[Wamr]

предлагаю с поста 52 Нужен совет: Oracle или MS SQL
отрезать обсуждение в другую ветку

raz хорошая попытка, только у них INDEX hint отрезан, так как Аксапта старая, а база в режиме 90.

[raz]

2 fed
Ты имеешь ввиду это?

Цитата:

HINT_INDEX (1) – если параметр включен, будут работать операторы INDEX HINT из X ++ кода. Кроме того, при использовании FORCELITERALS имеет побочное действие – при использовании SELECT по одной таблице с условиями в части WHERE по индексированному полю использование этого индекса будет указано в запросе автоматически;

отсюда http://axapta.mazzy.ru/lib/indexhints/

PS. Посоветуй "идеальный" hints, я посмотрю запросы.

[fed]

Цитата:

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

2 fed
Ты имеешь ввиду это? отсюда http://axapta.mazzy.ru/lib/indexhints/

PS. Посоветуй "идеальный" hints, я посмотрю запросы.

Идеальный hints - это отключение всех hints кроме firstfast/firstonly. Если у тебя в БД со статистикой в порядке, MS SQL/Oracle подберет вполне себе хороший маршрут исполнения запроса. Кстати именно поэтому начиная с DAX4 по умолчанию хинты просто игнорируются. А в трешке, в зависимости от номера KR значения флажков в indexhints менялось, так что прямо счас универсального хинта не посоветую...
Да и кстати - разговор то не про хинты, а вообще о разнице между инженерным и знахарским подходом к решению проблем.

[Ace of Database]

У меня всегда хинты были выключены.
Трассировку своих запросов показывать не хочу по политическим причинам.
Вот что показывает трассировка запросов, которые привел raz:

для запроса №1:

PHP код:

 FROM INVENTTRANS A WHERE ((DATAAREAID=?) AND (((((INVENTTRANSID=?) AND (ITEMID=?)) AND (STATUSISSUE=?)) AND (DATESTATUS=?)) AND (STATUSRECEIPT=?))) OPTION(FAST 20) 


для запроса №2:

PHP код:

 FROM INVENTTRANS A WHERE ((DATAAREAID=?) AND (((((STATUSRECEIPT=?) AND (STATUSISSUE=?)) AND (ITEMID=?)) AND (DATESTATUS=?)) AND (INVENTTRANSID=?))) OPTION(FAST 20) 


Для эксперимента, я включил хинты. И они стали попадать в запрос - так же как у raz'а.

для запроса №1:

PHP код:

FROM INVENTTRANS A(INDEX(I_177STATUSITEMIDX))  WHERE ((DATAAREAID=?) AND (((((INVENTTRANSID=?) AND (ITEMID=?)) AND (STATUSISSUE=?)) AND (DATESTATUS=?)) AND (STATUSRECEIPT=?))) OPTION(FAST 20) 


для запроса №2:

PHP код:

FROM INVENTTRANS A(INDEX(I_177STATUSITEMIDX))  WHERE ((DATAAREAID=?) AND (((((STATUSRECEIPT=?) AND (STATUSISSUE=?)) AND (ITEMID=?)) AND (DATESTATUS=?)) AND (INVENTTRANSID=?))) OPTION(FAST 20) 


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

[fed]

Цитата:

Сообщение от Ace of Database

У меня всегда хинты были выключены.
Трассировку своих запросов показывать не хочу по политическим причинам.
Вот что показывает трассировка запросов, которые привел raz:

Для эксперимента, я включил хинты. И они стали попадать в запрос - так же как у raz'а.

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

Тогда надо вынимать планы запросов из самого MS SQL и сравнивать по обоим вариантам. Советую почитать в BOL про dynamic views sys.dm_exec_query_stats, sys.dm_exec_sql_text и sys.dm_exec_query_plan.
Коротко говоря - есть возможность вытащить из MS SQLного кэша запросов текст и план исполнения некоторого количества запросов которые недавно исполнялись. По плану запроса можно будет понять что изменилось и попытаться построить теории - почему изменилось...

[sukhanchik]

Цитата:

Сообщение от Ace of Database

У меня всегда хинты были выключены.

На 4.0 (хинты отключены в конфиг утилите АОСа) джоб raz-а не вставил никаких указаний по индексам в планы запроса. Вывод - DAX не посчитала себя умнее SQL Server (SQL Server 2005 SP3)

[Ace of Database]

Цитата:

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

На 4.0 (хинты отключены в конфиг утилите АОСа) джоб raz-а не вставил никаких указаний по индексам в планы запроса. Вывод - DAX не посчитала себя умнее SQL Server (SQL Server 2005 SP3)

Так и я про то же, что у меня хинты выключены. Даже когда тормозили запросы.
Просто я сейчас проверил, что у меня работает включение хинтов.

[raz]

Цитата:

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

Да и кстати - разговор то не про хинты, а вообще о разнице между инженерным и знахарским подходом к решению проблем.

Согласен! Но когда после очередного обновления замечаешь, что вендор в очередной раз сделал всем "сюрприз", сразу вспоминаются "Хочу бубен!!!!!".

[AndyD]

По поводу подстановки хинтов ядром - Как работает индекс и кеш запросов?

[Wamr]

Ace of Database
можешь показать скриншот настройки РАБОЧЕГО АОСа
закладка Database (там нет серверов-баз-паролей) ?