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

[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 запроса

[Oleksandr]

Цитата:

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

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

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

[Oleksandr]

Цитата:

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

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

По ссылке - поэксперементировал с buffer size в конфигурации. При значении 2048 было ускорение процентов на 5-10, видимо потому что записи из запроса весьма тяжелые. Не знаю, правда как это может сказатсья на прочей производительности.

[glibs]

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

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

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

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

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

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

[Oleksandr]

Цитата:

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

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

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

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

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

Цитата:

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

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

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

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

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

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

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

[macklakov]

Цитата:

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

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

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

[Oleksandr]

Цитата:

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

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

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

[glibs]

Цитата:

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

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

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

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

Цитата:

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

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

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

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

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

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

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

[Oleksandr]

Цитата:

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

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

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

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

Цитата:

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

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

Ну я так и понял... Потому что закономерностей в резуьтатах никаких.
У вас тоже такой опыт был?

Цитата:

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

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

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

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

ОК, попробую.
Проблема четко определить самые тяжелые вещи, много факторов разных.

[glibs]

Цитата:

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

...
Ну я так и понял... Потому что закономерностей в резуьтатах никаких.
У вас тоже такой опыт был?
...

У меня много чего было такого :-)

Профайлер — вещь очень полезная, но для поиска недостатков в коде. Как правило, его имеет смысл начинать использовать, если с запросами уже все ОК, и за счет их ускорения не ожидается ощутимого прироста в производительности.

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

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

[zelen]

предлагаю такой вариант:
использовать класс Connection, соответственно делаете конекшны к двум БД типа
LoginProperty dbLoginProperty;
Statement statement;
ResultSet rs;
str _query;
;
// соединяемся с источником данных
dbLoginProperty = NEW LoginProperty();
dbLoginProperty.setDSN(parmODBC);
dbLoginProperty.setServer(parmServ);
dbLoginProperty.setDatabase(parmDBServ);
dbLoginProperty.setUsername(parmLogin);
dbLoginProperty.setPassword(parmPass);
dbConnection = new odbcConnection(dbLoginProperty);
и соответственно настраиваете 2-й конекшн
_query = ваш запрос но уже в сиквельной нотации
statement = DBConnection.createStatement();
rs = statement.executeUpdate(_query);
для ваших внутренних вычислений можно добавить еще такое:
while (resultSet.next())
{
тут че нить вычисляете и пишете в другую БД
strfmt("INSERT INTO () VALUES (%2,%3,%4,%5,%6,%7,%8,%9,%10,%11,%12) ",
rs.getString(1) ... и т.п.
}

еще для оптимизации можно всё это дело создать в пакетном классе на сервере, чтобы он отрабатывал в определенное время!

[zelen]

можно еще так же настроить ваши БД как линкед сервера, чтобы в одном сиквел запросе мона было сразу делать insert to server.db.table (select from linckedServer.db.table)

[macklakov]

Цитата:

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

предлагаю такой вариант:
использовать класс Connection, соответственно делаете конекшны к двум БД типа

Зря предлагаете.
1. Если уж пользовать прямое обращение, то лучше использовать стандартные job-ы на SQL-сервере
2. Аксапта не любит когда со стороны лезут в ее базу, даже если на чтение

[Wamr]

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

Цитата:

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

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

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