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

[Ace of Database]

Цитата:

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

Ну хотя бы сколько памяти на сервере БД вы можете сказать ?

64 гига. SQL 2005.

[Ace of Database]

Цитата:

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

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

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

[belugin]

Цитата:

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

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

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

Если в следующий раз встретится, не могли бы вы привести SQL, который уходит на сервер и план запроса?

[Logger]

Цитата:

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

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

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

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

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

[Logger]

Цитата:

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

64 гига. SQL 2005.

А сколько активных сессий в БД в пиковой нагрузке ?
Вопрос связан с тем что, как я понимаю, просто объем памяти обо всем не говорит. Его может быть достаточно если у вас 100 одновременных сессий работает и недостаточно если их 1000.

[Ace of Database]

Видите, сколько много разных вопросов возникает?
Мне проще писать запросы в соответствии с соблюдением порядка полей в индексах. Чем думать обо всех остальных нюансах.
Да, возможно устарела стастистика. Но после исправления уже 2 месяца нет тормозов - перестала устаревать значит.
И, повторюсь, критично, чтобы запрос отрабатывал моментально, даже задержка в 1 секунду смертельна.

Я не претендую на последнюю инстанцию.

И еще, это не единственный такой исправленный запрос. Было исправлено десятки разных мест. И нигде сейчас тормозов не наблюдается.

[fed]

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

[Logger]

Цитата:

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

Видите, сколько много разных вопросов возникает?

Не так уже много.
Приведите запросы которые у вас уходят на сервер БД в обоих случаях.
Наверняка хинты какие нить ядро вставило.

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

[Ace of Database]

Цитата:

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

Не так уже много.
Приведите запросы которые у вас уходят на сервер БД в обоих случаях.
Наверняка хинты какие нить ядро вставило.

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

Я уже два месяца ничего не подкручиваю. Больше ничего в этой теме здесь писать не буду
Вы меня сделали. Сдаюсь.

[Logger]

Цитата:

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

Я уже два месяца ничего не подкручиваю. Больше ничего в этой теме здесь писать не буду
Вы меня сделали. Сдаюсь.

Ну что так трудно что ли SQL запрос посмотреть и выложить ? Неужели самим неинтересно, в чем причина ?

[Ace of Database]

Цитата:

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

Ну что так трудно что ли SQL запрос посмотреть и выложить ? Неужели самим неинтересно, в чем причина ?

У нас с вами разный ход мыслей.
Если бы у Ньютона не было друга по имени Галлей, то он бы никогда не опубликовал свои труды. Из-за страха перед спорами с коллегами-учеными.
К сожалению, у меня нет друга по имени Галлей

[fed]

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

[Ace of Database]

Цитата:

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

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

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

[Ace of Database]

Первые два пункта "теории" разве не соответствуют взглядам на принципы оптимизации БД?
А 3-й и 4-й пункт кому-то наносят вред?

PHP код:

Чтобы база работала быстро, надо:
1) Большое количество памяти на сервере, чтобы информацию о блокировках записей SQL-сервер целиком помещал в памяти. Тогда не будет возникать страничных и табличных блокировок.
2) периодически перестраивать индексы и обновлять статистику запросов
3) программисту всегда проверять, есть ли индекс по полям, которые перечислены в выражении отбора "where". достаточно иметь индекс по первому полю, идущему в выражении отбора "where"
3)в выражении отбора "where" сначала перечислять более уникальные поля, а потом менее уникальные - например надо писать "where inventTrans.TransRefId == 'journalId' && inventTrans.TransType == InventTransType.InventTransfer". А так писать нельзя: "where inventTrans.TransType == InventTransType.InventTransfer && inventTrans.TransRefId == 'journalId' ". 


[sukhanchik]

Цитата:

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

Первые два пункта "теории" разве не соответствуют взглядам на принципы оптимизации БД?
А 3-й и 4-й пункт кому-то наносят вред?

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

[fed]

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

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

[egorych]

Цитата:

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

Первые два пункта "теории" разве не соответствуют взглядам на принципы оптимизации БД?
А 3-й и 4-й пункт кому-то наносят вред?

PHP код:

Чтобы база работала быстро, надо:
1) Большое количество памяти на сервере, чтобы информацию о блокировках записей SQL-сервер целиком помещал в памяти. Тогда не будет возникать страничных и табличных блокировок. 


Из того что я читал о блокировках в MSSQL - таблица блокировок ВСЕГДА находится в памяти и ни при каких условиях не свопится. Объем памяти безусловно полезен, но для другой цели - закэшировать больше данных.

PHP код:

2) периодически перестраивать индексы и обновлять статистику запросов 


К этому пункту нет притензий

PHP код:

3) программисту всегда проверять, есть ли индекс по полям, которые перечислены в выражении отбора "where". достаточно иметь индекс по первому полю, идущему в выражении отбора "where" 


Не все так однозначно, особенно по последнему утверждению

PHP код:

3)в выражении отбора "where" сначала перечислять более уникальные поля, а потом менее уникальные - 


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

[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:

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

[Wamr]

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

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