Axapta - SQL transfer

[Oleksandr]

Добрый день

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

while selec a join b join c //возвращает под сотню тысяч записей.
{
while (bla bla ) // несколько повторов на одну запись
{
someoperation();
recordinsertlist.ins();
}
recordinsertlist.insertdatabse();
}


Моя идея - переписать это счастье на сиквеле ради скорости.
Есть ли у кого опыт или соображения по поводу того насколько это сложно и насколько выгодно в плане времени исполнения?

Заранее спасибо

[glibs]

В Аксапте?

А как RecId вы будете выделять?

И задачка у вас... нетипичная. Что вы делаете (зачем вставлять в базу столько записей)?

[Oleksandr]

Цитата:

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

В Аксапте?

А как RecId вы будете выделять?

И задачка у вас... нетипичная. Что вы делаете (зачем вставлять в базу столько записей)?

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

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

[fomenka]

Вы точно уверены в отсутствии возможности оптимизации вашего

while selecе a join b join c //возвращает под сотню тысяч записей.
{
while (bla bla ) // несколько повторов на одну запись
{
someoperation();
recordinsertlist.ins();
}
recordinsertlist.insertdatabse();
}
???

Как показывает опыт, проигрыш X++ по сравнению с чистым T-SQL конечно же бесспорный, но не настолько катастрофичный, что бы пренебречь высокоуровневой средой
Job - задача одного-двух использований? Тогда наверняка есть верхняя грань приемлемости времени выполнения Job'а. Не нужен 100% оптимальный результат, достаточно достичь указанной величины. Оптимизируя 20% кода можно достичь 80% возможной оптимальности, imho

[glibs]

Значит смотрите. Есть два подхода решения проблемы:
1. Лбом сквозь стену.
2. Рациональный.

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

Второй предполагает выработку оптимального решения. Вы говорите, что у вас за 1 000 000 проводок. Кто-то их поштучно просматривает? Они действительно нужны? М.б. можно свернутые сделать? И как вы их анализируете? М.б. их в OLAP можно перенести? Ну и т.д.

Если решать в лоб, то сравните скорость отработки вашего джоба, но с полностью закомментированной вставкой. Причем в одном случае это делайте в транзакции, а во втором не в транзакции. Из разницы во времени вы поймете грубоватую оценку того, сколько времени уходит на чтение, сколько на вставку, и сколько на блокировки.

Предположительно, у вас тормозит вставка.

Потом посмотрите, в какую таблицу вы пишете. Там есть индексы (особенно кластерные)?

[Oleksandr]

Цитата:

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

Вы точно уверены в отсутствии возможности оптимизации вашего

while selecе a join b join c //возвращает под сотню тысяч записей.
{
while (bla bla ) // несколько повторов на одну запись
{
someoperation();
recordinsertlist.ins();
}
recordinsertlist.insertdatabse();
}
???

Как показывает опыт, проигрыш X++ по сравнению с чистым T-SQL конечно же бесспорный, но не настолько катастрофичный, что бы пренебречь высокоуровневой средой
Job - задача одного-двух использований? Тогда наверняка есть верхняя грань приемлемости времени выполнения Job'а. Не нужен 100% оптимальный результат, достаточно достичь указанной величины. Оптимизируя 20% кода можно достичь 80% возможной оптимальности, imho

Код оптимизировал настолько, насколько мне позволили мои познания. Интересно, что операции с БД занимают в процентном соотношении немного времени. Из-за количества переменных даже операции в памяти с инсертлистом занимают немало.

Кстати, для меня сюрпризом были, что запрос из while select исполняется по мере выполнения цикла (если верить систем монитору). Собственно селект занимает пару минут, не больше. Возможно, есть способ заставить выполнить весь запрос (т.е. набрать рекордсет), а потом по нему производить операции?

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

[Recoilme]

Цитата:

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

Возможно, есть способ заставить выполнить весь запрос (т.е. набрать рекордсет), а потом по нему производить операции?

может логичнее select firstOnly firstFast, вместо select firstOnly
Управление опциями SQL запроса

[Wamr]

Есть простой способ.
Надо сделать вставку recordinsertlist.insertdatabse(); в несколько раз реже, чтобы запихивалась не 1-10 записей а сразу 1000-2000.

Цитата:

для меня сюрпризом были, что запрос из while select исполняется по мере выполнения цикла

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

[macklakov]

Цитата:

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

while selec a join b join c //возвращает под сотню тысяч записей.
{
while (bla bla ) // несколько повторов на одну запись
{
someoperation();
recordinsertlist.ins();
}
recordinsertlist.insertdatabse();
}

Почему у Вас recordinsertlist.insertdatabse(); внутри while select?

Цитата:

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

времени занимает до 80-ти часов,

Цитата:

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

Последний раз было 1.1М записей...

3 строки в секунду? Что-то очень медленно, через COM с такой скоростью грузится. Что именно там происходит?

[Oleksandr]

Цитата:

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

Значит смотрите. Есть два подхода решения проблемы:
1. Лбом сквозь стену.
2. Рациональный.

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

Второй предполагает выработку оптимального решения. Вы говорите, что у вас за 1 000 000 проводок. Кто-то их поштучно просматривает? Они действительно нужны? М.б. можно свернутые сделать? И как вы их анализируете? М.б. их в OLAP можно перенести? Ну и т.д.

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

Цитата:

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

Если решать в лоб, то сравните скорость отработки вашего джоба, но с полностью закомментированной вставкой. Причем в одном случае это делайте в транзакции, а во втором не в транзакции. Из разницы во времени вы поймете грубоватую оценку того, сколько времени уходит на чтение, сколько на вставку, и сколько на блокировки.

Предположительно, у вас тормозит вставка.

Потом посмотрите, в какую таблицу вы пишете. Там есть индексы (особенно кластерные)?

Да, индексы есть, два, каждый из 5 полей, оба уникальных, и кластерный.
Возможно, вы правы. Хотя по профайлеру физическая вставка РекордИнсертЛист.InsertDatbase() не так много занимает. Пытался проанализировать с помощью профайлера но на количестве записей большем чем 500 ну оооочень долго делает. И, как мне показалось, не совсем адекватные результаты иногда дает, нелогичные.

- 56% - собственно запрос while select (хотя если отдельно запустить, без while - исполняется очень бысто)
- 33% - операции над каждой записью, в том числе создание добавление єлемента РекордИнсертЛист
- вставка где-то в оставшихся %

За идеи спасибо!

[Oleksandr]

Цитата:

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

может логичнее select firstOnly firstFast, вместо select firstOnly
Управление опциями SQL запроса

там нету firstOnly, там while select...., но исполняется постепенно, а не сразу, за ссылку спасибо

[macklakov]

Цитата:

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

Да, индексы есть, два, каждый из 5 полей, оба уникальных, и кластерный.

Кластерный индекс на той таблице, которую заполняете?

[Wamr]

while selec a join b join c
У вас используются все поля из 3х таблиц? Если нет, то лучше их указать в запросе

[Oleksandr]

Цитата:

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

Есть простой способ.
Надо сделать вставку recordinsertlist.insertdatabse(); в несколько раз реже, чтобы запихивалась не 1-10 записей а сразу 1000-2000.

Он делается один раз вообще, я ошибся в коде

Цитата:

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

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

Гм, но почему так долго, елси набор данных уже есть, или это нормально?
Систем монитор, кстати, говорить что количество селектов соотв. количеству записей...

    AgreementTable      agreementTable;
    ContractTable       contractTable;
    ContractPartTable   contractPartTable;
    ;
    prognosisLineList   = new RecordInsertList(tablenum(PrognosisLines));
    prognosisList       = new RecordSortedList(tablenum(PrognosisLines));
    prognosisList.sortOrder(fieldnum(PrognosisLines, PrognosisId),
                            fieldnum(PrognosisLines, AgreementNum),
                            fieldnum(PrognosisLines, ProductNum),
                            fieldnum(PrognosisLines, StartDate),
                            fieldnum(PrognosisLines, YearMonth));

    ttsbegin;

    while select agreementTable
        where agreementTable.DeliveryCategory           == deliveryCategory
        join contractTable
            where contractTable.DeliveryCategory        == agreementTable.DeliveryCategory
                 ---
            join contractPartTable
                where contractPartTable.ContractNum     == contractTable.ContractNum
                 ---
                     {
        this.writePrognosisLines(agreementTable,
                                 contractTable,
                                 contractPartTable); // опрерации + вставка
    }

    prognosisLineList.insertDatabase();
    this.prognosisTotals();

    ttscommit;

[Oleksandr]

Цитата:

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

Кластерный индекс на той таблице, которую заполняете?

Да, причем сложный, и это нехорошо, без него попробую

[Oleksandr]

Цитата:

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

while selec a join b join c
У вас используются все поля из 3х таблиц? Если нет, то лучше их указать в запросе

Многие. Но в принципе может быть поиожет, таблички нелегкие, почему-то сразу не подумал об этом.

[macklakov]

а что с памятью творится? prognosisLineList на миллион записей не начинает на диск кэшироваться?

[Wamr]

Цитата:

Систем монитор, кстати, говорит что количество селектов соотв. количеству записей...

а у вас там в join временная табличка не попала случайно

[Oleksandr]

Цитата:

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

а что с памятью творится? prognosisLineList на миллион записей не начинает на диск кэшироваться?

Не знаю, на серваке не смотрел локально сиквел жрал дето 500 МБ и все, это правда неполная загрузка, в принципе из-за своппинга тоже может тормозить.

В общем много неизвестных, и эксперименты слишком долгие получаются чтоб ынормально проанализировать..
С профайлером вообще нереально на больших данных.
Вот и хотел сравнить с чисто сиквельным решением.

Ладно, всем спасибо за советы, попробую поэксперементировать еще, сообщу

[glibs]

Цитата:

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

...
Да, индексы есть, два, каждый из 5 полей, оба уникальных, и кластерный.
...

Ни фига себе... Быстро вставка у вас работать на таком хозяйстве не будет.

Вариант оптимизации — писать в таблицу без индексов, потом копировать в таблицу с индексами. Здесь можно применить и прямой SQL (с RecId уже проблем не будет). Но это очень жесткий (я бы сказал экстремальный) метод.

Цитата:

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

...
Хотя по профайлеру физическая вставка РекордИнсертЛист.InsertDatbase() не так много занимает. Пытался проанализировать с помощью профайлера но на количестве записей большем чем 500 ну оооочень долго делает. И, как мне показалось, не совсем адекватные результаты иногда дает, нелогичные.

- 56% - собственно запрос while select (хотя если отдельно запустить, без while - исполняется очень бысто)
- 33% - операции над каждой записью, в том числе создание добавление єлемента РекордИнсертЛист
- вставка где-то в оставшихся %
...

Это Аксаптовский то профайлер? Забейте на него, если вы запросы к SQL разбираете. Только время потеряете.

Так все-таки, если отказаться от вставки — т.е. прогнать скрипт вхолостую — сколько времени уйдет? Просто секундомером замерить нужно без профайлера.

Т.е. закоментарьте строчки
ttsbegin;
this.writePrognosisLines...
prognosisLineList.insertDatabase();
this.prognosisTotals();
ttscommit;

Если будет долго, то бросайте. Нужно будет разбирать сам select.