Тормозит прогнозное планирование

[Perc]

Запускаю расчет прогнозного плана с большим объемом данных. Вкратце стандартный код такой:
--------
Начало транзакции;
Очистить прогнозный план запущеный на расчет;
Рассчитать прогнозный план;
Конец транзакции;
-----------
Если предварительно план не отчистить стандартной периодической операцией, то расчет по прикидкам может идти больше 2 суток. (Во всяком случае за 10 часов не дождались завершения, но он точно идет не затыкается ). Если же план перед расчетом пуст, то расчет проходит чуть меньше 4 часов. Рассчитаный план в ReqTrans содержит 400-500 тыс строк, ну и в reqPO и reqTransCov меньше, но тоже прилично записей. По всему сходится, что косяк в настройке СУБД. Логистика такова, что этот план нужно считать регулярно!
Могет опытные админы сходу ткнут место где косячок ? Если эту проблему победить - можно рассчитывать, что еще где-нить положительный эффект проявится.
Axapta 3.0 sp4; SQLServer2005

[Andrew K.]

Цитата:

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

--------
Начало транзакции;
Очистить прогнозный план запущеный на расчет;
Рассчитать прогнозный план;
Конец транзакции;
-----------

Уберите "Начало транзакции;" и "Конец транзакции;" это возможно главная причина "косячка". Представьте себе объем лога когда 500 тыс записей удаляются и столько же вставляются..
Эта ф-я (ReqCalc) выполняет расчет для каждой номенкл. отдельно и изолированной транзакции. Не совсем ясно зачем операция "Очистить". Система сама все очистит если запущен полный пересчет. С вашим объемом данных и обычным hardware должно все за ночь пересчитать. Если не устраивает, то ищите способы уменьшения кол-ва данных.

[Perc]

Цитата:

Сообщение от Andrew K.

Уберите "Начало транзакции;" и "Конец транзакции;" это возможно главная причина "косячка". Представьте себе объем лога когда 500 тыс записей удаляются и столько же вставляются..
Эта ф-я (ReqCalc) выполняет расчет для каждой номенкл. отдельно и изолированной транзакции. Не совсем ясно зачем операция "Очистить". Система сама все очистит если запущен полный пересчет. С вашим объемом данных и обычным hardware должно все за ночь пересчитать. Если не устраивает, то ищите способы уменьшения кол-ва данных.

Несомненно объем лога больше, если удалять и вставлять в отной транзакции. Только в этом проблема и сервер не может ее преодолеть? А если его "подкрутить"?
"Начало транзакции;" и "Конец транзакции;" - стандарная реализациия и не хочется лезть в коде копошиться микрософтовском (не такой уж и большой объем, что бы разработчики не могли его допустить).
Ф-я (ReqCalc) действительно выполняет расчет для каждой номенкл. отдельно, но удаление плана идет полностью перед началом расчета, если запущен полный расчет.
Операцию удаления плана как раз и делаю предварительно, так как расчет на практике проходит значительно быстрее если план пуст. Без отчистки на порядок дольше. Ну не СУБД ли это косяк?
Объем данных уменьшить это не реальным мне представляется ))

[Andrew K.]

Как "подкрутить"? Поделитесь, если есть идеи.
Если про "начало" и "конец" транзакции в стандарте, то здесь применен немного другой подход.. Это не ttsbegin, ttscommit в обычном понимании, а та же операция выполняемая в отдельной сессии, . Я подумал ("прочел") что Вы сделали нечто похожее на отдельную ф-ю которая выполняет:
ttsbegin;
deletePlan()
calc()
ttscommit;
Это не совсем верно с моей точки зрения... По поводу уменьшения обьема данных, думаю, нужно озадачиться вопросом Есть примеры, когда компании включали прогноз годовой в планирование и получали 1,5млн транзакций и 2 дня вычислений. Затем уменьшили период и кол-во прогнозов, позакрывали вовремя Произ. заказы, Продажи и пр, пересмотрели организацию аналитик и пр. и получили вполне приемлимый р-т

[Perc]

Цитата:

Сообщение от Andrew K.

Как "подкрутить"? Поделитесь, если есть идеи.
Если про "начало" и "конец" транзакции в стандарте, то здесь применен немного другой подход.. Это не ttsbegin, ttscommit в обычном понимании, а та же операция выполняемая в отдельной сессии, . Я подумал ("прочел") что Вы сделали нечто похожее на отдельную ф-ю которая выполняет:
ttsbegin;
deletePlan()
calc()
ttscommit;

Знал бы как не спрашивал. Сопровождал раньше софт под СУБД Оракул - там админ порой делал изумительный тюнинг по моим пожеланиям. Улучшения бывали на порядки (Видимо предварительно чтото портил ).
Про "начало" и "конец" транзакции в стандарте - так там и есть на самом деле все в обычном понимании стандартно. В классе ReqCalc:
ttsbegin;
this.insertData();
...
ttscommit;
В функции insertData() и очистка ReqTrans и RecTransCov присутсвует в прямом виде.
А отдельная сессия есть в этом классе, но она имхо ничего кроме блокировки записи в таблице планов не делает вообще:
connectionLock = new UserConnection();
connectionLock.ttsbegin();
reqPlanLock.setConnection(connectionLock);
select forupdate reqPlanLock
where reqPlanLock.reqPlanId == reqPlanId;
Видимо это просто способ запрета паралельного запуска того же расчета с другого места всего лишь.

[Andrew K.]

Цитата:

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

Про "начало" и "конец" транзакции в стандарте - так там и есть на самом деле все в обычном понимании стандартно. В классе ReqCalc:
ttsbegin;
this.insertData();
...
ttscommit;
В функции insertData() и очистка ReqTrans и RecTransCov присутсвует в прямом виде.
А отдельная сессия есть в этом классе, но она имхо ничего кроме блокировки записи в таблице планов не делает вообще:
connectionLock = new UserConnection();
connectionLock.ttsbegin();
reqPlanLock.setConnection(connectionLock);
select forupdate reqPlanLock
where reqPlanLock.reqPlanId == reqPlanId;
Видимо это просто способ запрета паралельного запуска того же расчета с другого места всего лишь.

В классе ReqCalc есть this.ttsbegin() и this.ttscommit(), что не имеет ничего общего с ttsbegin / ttscommit, блокирующих текущую сессию. Иначе сильно распухнет лог и производительность упадет. Т.е. полный пересчет выполняется без блокировок. Как видно,
connectionLock = new UserConnection();
connectionLock.ttsbegin();
reqPlanLock.setConnection(connectionLock);
select forupdate reqPlanLock where reqPlanLock.reqPlanId == reqPlanId;
...
блокируют всего 1 таблицу reqPlan, и действительно для того чтобы остальные "желающие" подождали. Так как же у вас сделано? Полностью стандарт или что то дописали?

[Perc]

this.ttsbegin() и this.ttscommit() к начальной теме вопроса:
Начало транзакции;
Очистить прогнозный план запущеный на расчет;
Рассчитать прогнозный план;
Конец транзакции;
не имеют никакого отношения. Они в паралельной сессии блокируют/отпускают запись единственную. Все. Я имел ввиду код в методе UpdateData класса ReqCalc:
ttsbegin;
this.insertData();
...
ttscommit;
Этот код у меня на слое sys, и ttsbegin; ttscommit; там самые обыкновенные, а метод insertData() чистит план и выполняет часть расчета плана - запоняет ReqTrans.

[Andrew K.]

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

[Torin]

Трудно сравнивать конфигурации
На примерно таком же объеме данных, на SQL 2005, правда AX3SP3+KR3.
Путем проб и ошибок, разнообразных "бубнов" как с SQL, так и кодом Аксапты, добились времени расчета - 20 минут. ;-)
Сейчас нет времени - вечером распишу примерный "план боя"

[Torin]

Perc, проведите, плиз, эксперемент - сделайте джоб, который по полям простого индекса (или кластерного) таблицы ReqTrans проверяет наличие хотя бы одной записи. Например, в цикле по всем номенклатурам. Главное - чтобы селект был только по полям индекса (Если проверяете поле recID - то его тоже ндо включить в индекс). И смотрите в Мониторе производительности параметр Batch Requests \sec. Конечно, при небольшой или нулевой нагрузки пользователей.
Мне это подскажет производительность Вашего сервера.
Заодно, сообщите кол-во номенклатур (с аналитиками), среднее кол-во строк прогнозов по ним.
Тогда советы будут точнее.
Спасибо

[Perc]

Цитата:

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

Perc, проведите, ..
Заодно, сообщите кол-во номенклатур (с аналитиками), среднее кол-во строк прогнозов по ним.
Тогда советы будут точнее.

Batch Requests \sec - до запуска джоба был средний 20-70, запустил джоб:
while select inventTable
select count(recId) from reqTrans
where reqTrans.ItemId == inventTable.ItemId && reqTrans.ReqPlanId == "Общий";
Batch Requests \sec - прыгает от 100 до 2000
По расчету прогноза смотрю системный журнал на закладке Статистика:
Число строк прогноза продаж - 7 309
Кол-во сплан. производств - 29 232
Число строк спецификации - 221 188
Количество сплан. закупок - 113 590
Число номенклатур - 4 296
Остальное по нулям

[Torin]

ай ай. Спецификаций у нас нет, номенклатур столько же (а все в 4 раза больше), прогнозов в 10 раз больше, спланированных закупок + переносов 20 тыс.Правда все осложняется тем, что таких 5 компаний и расчет идет по каждой из них.
Но если "Batch Requests \sec" начинает падать меньше 500-700 я волнуюсь. А прогнозное идет на 1500-4000. Что за сервер ? И, прошу подождать уже до сегодняшнего вечера - я таки напишу.

[Torin]

Уф. Пообещал..
Не буду говорить конкрентно о прогнозом - у нас оба подверглись изменениям, да и класс один. Также, чтобы не постить проекты и код (что даже не могу сделать по соображениям этичность к коллегам), буду просто предлагать свои личные соображения.
Сразу хочу предупредить - проблема многогранна. Что-то одно может и не помочь.

В соседней ветке уже писал, но сильно рекомендую:
1) Поставить KR3 + база в режиме 9-ки, + (уже писал на форуме) использовать SQL Native Driver (по статистике сервера самые большие ожидания на старом).
Кстати, не пропутите проект SQLTraceUtility из KR3, он лежит в папке "Navision\Client\Appl\"
2) Обязательно действовать наверняка !. Нужно точно знять - где проблемы. Сильно помогают скрипты отсюда http://www.microsoft.com/technet/scr....mspx?mfr=true ННапример, в секции "Waitstats"
3) Если сводное используется с пересозданием плана, то, конечно, стоит написать на x++ код, которые генерирует TRUNCATE TABLE с нужными джойнами и просто за раз чистит таблицу.
4) Если п.3 невозможен - проверьте или просто поверьте, что при большой кол-ве одинаковых запросов на удаление или сложных агрегатов (с INVENTSUM), бывает очень выгодно - сначала по индексу проверить есть ли записи вообще (без джойнов на INVENTDIM, разумееется), а потом уже делать, что хотели. Возможно, покажеться, глупым, - попробуйте, удивитесь Например (из RecCalc)
select firstonly recId from reqTrans where ...
5) Изучая план доступа для режима найдите такие запросы, которые не выбирают все поля таблицы - например только ITEMID, ITEMPLAINID, QNT - и сделайте по ним индекс. Не бойтесь новых индексов (даже с полями типа qnt), но и разумно тоже - начинает больше тормозить UPDATE, INSERT, DELETE, зато летает на SELECT.
Общий подход - как можно реже допускать вычитку из хипа (по кластерному ключу, и, особенно, по букмарку), раз уж надо перебрать 100 тыс записей - то только по индексу.
6) У меня, практически на всех "тяжелых" таблицах кластерный ключ по индексу RECID. Это было непростое решение, несколько раз отказывался, но все равно возвращался к этому. В первую очередь помогает там, где есть высокопроизводительные курсорные обновление (Аксапта генерирует обновление строки по reciD). Причин для этого много - можно даже небольшую статью писать ;-) Да, и не забудьте, что кластерный ключь по-умолчанию, включается в каждый не кластреный индекс.
7) Правила создания индексов надо знать - учитывать и селективность и порядок полей и стиль использование вообще. Если не уверен насчет индекса, - чатсо просто удляю его из кода - Юкон сам решит, и эффективнее меня.
8) Особенная проблема с INVENTDIM. Красиво по дизайну, но правильный план доступа делать тяжело. В конце концов: Кластер по recID, а вот рабочий индекс должны быть таким, чтобы первым шел inventDimID, а потом поля, наиболее селективные с точки зрения дизайна номенклатуры - у нас inventBatchId, InventLocationId. Практически везде, где было принципиально, запретил Акспте хинтовать эту таблицу на джойнах.
9) InventTrans - довольно самостоятельный принцип - кол-во индексов увеличил в 2 раза под различные группы плана доступа.
10) Если встречал "универлальные" хинтовалки - выключал, например в INVENTSUM::queryAddHint - это профанация.
11) Сразу убираем forcenestedlookup и forceselectorder
12) На InventSum у нас иключение - по причине большого числа открытых проводок, кластерный индекс, пока, Closed, ItemId

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

[Torin]

В догонку - Вам полюбому надо стремиться к производительность около 3000 запросов в сек, чтобы на таком кол-ве данных за полчаса все пресчитать. Если все, приведенное выше не получиться:
1) Попробуйте уменьшить сегментацию индексов
2) Можно решительно покупать новый сервер.
;-)

[Perc]

Цитата:

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

В догонку - Вам полюбому надо стремиться к производительность около 3000 запросов в сек, чтобы на таком кол-ве данных за полчаса все пресчитать. Если все, приведенное выше не получиться:
1) Попробуйте уменьшить сегментацию индексов
2) Можно решительно покупать новый сервер.
;-)

Torin, cпасибо за развернутые рекомендации. Постараюсь воспользоваться.
Я надеялся, что на мой первоночальный вопрос есть ответ из небольшого заклинания ), но видимо увы...
Замечу только, что ваша ситуации отличается как раз из-за отсутствия спецификаций (но компаний много). Раскрутка спецификаций процесс видимо не самый быстрый (до 11 уровней у нас и по несколько раз в год меняются..).
Еще в статистике расчета видно у нас время "обработка" составляет 10-15% от время "покрытие".

[Torin]

Количество уровней спецификации - это не проблема, если подойти творчески ;-)
А вот литеральные ключи таблиц проблема - от длинны индексов ок как сильно зависит скорость (кол-во значений на странице) и это вопрос совершенно нерешаемый.