Модификация огромного количества (сотни тысяч) записей в Axapta 3.0 SP4

[Hyper]

Давненько я с Аксаптой 3.0 не работал.

Мне необходимо модифицировать огромное количество записей в ряде таблиц в третьей Аксапте. В одной таблице может быть, скажем, 700 000 записей. Разумеется, одной транзакцией этого делать нельзя. Код у меня наподобие следующего:

Table1 table1;
int bulk = 5000;    // 5;
int i;
;

ttsbegin;

while select forupdate table1
{
    i++;
    table1.Field1 = strfmt("Blah %1", i);
    table1.doupdate();

    if (i mod bulk == 0)
    {
        ttscommit;
        ttsbegin;
    }
}

ttscommit;

Так вот, после каждого ttscommit следующий while select выбирает не следующую, пять тысяч первую запись, а вновь берет самую первую. Таким образом, цикл никогда не заканчивается, и первые пять тысясяч записей в таблице вновь и вновь обновляются.

Что за дела?

Я уже пытался использовать и while select и SelectForUpdate отдельно, и дополнительную переменную Table1 (одну для цикла, одну для find forupdate), и Query, и UserConnection, и вместо внешних ttsbegin/ttscommit использовать только внутренние, и чего я только не пытался - везде свои грабли. Чувствую, следующим шагом буду ориентироваться на диапазон RecId и просто делать кучу отдельных циклов по каждой таблице.

У кого-то есть идеи что вообще происходит? На всякий случай - все это происходит в классе унаследованном от RunBaseBatch (но не в пакетной обработке).

[kornix]

Можно сделать 1 основной цикл, в нем выбирать записи не для обновления. Потом внутри цикла открывать транзакцию, выбирать select forupdate из другой этой же таблицы но другой табличной переменной и обновлять? Т.е.:

    table1    table1, table1_Upd;
    ;
    while select table1
    {
        ttsbegin;
        select forupdate * from table1_Upd 
            where table1_Upd.RecId == table1.RecId;
        table1_Upd.Update();
        ttscommit;

    }

[Hyper]

Кстати, да, если делать отдельный ttsbegin/ttscommit для каждой записи, все вроде работает. Я, наверное, на этом и остановлюсь.

Table1 table1Loop;
Table1 table1;
int i;
;


while select table1Loop
{
    i++;
    ttsbegin;
    table1 = Table1::findRec(table1Loop.RecId, true);
    table1.Field1 = strfmt("Blah %1", i);
    table1.doupdate();
    ttscommit;
}

Но мне почему-то казалось, что групповое обновление записей (скажем, 500 или 1000 в одной транзакции) на больших объемах даст ощутимый прирост производительности. Я не прав?

И вот такой вот код уже не работает:

Table1 table1Loop;
Table1 table1;
int bulk = 5000;    // 5;
int i;
;

ttsbegin;

while select table1Loop
{
    i++;
    table1 = Table1::findRec(table1Loop.RecId, true);
    table1.Field1 = strfmt("Blah %1", i);
    table1.doupdate();

    if (i mod bulk == 0)
    {
        ttscommit;
        ttsbegin;
    }
}

Та же проблема, которую я первоначально описал.

[pitersky]

а чем не устраивает update_recordset в одной транзакции?

[Hyper]

Цитата:

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

а чем не устраивает update_recordset в одной транзакции?

Тем, что заранее неизвестно, какие поля и как именно должны быть изменены.

[pitersky]

Цитата:

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

Тем, что заранее неизвестно, какие поля и как именно должны быть изменены.

честно говоря, из вашего примера это никоим образом не следует
а поведение Аксапты ИМХО вполне логично. что такое закрытие транзакции как не обозначение границы операции с определённым набором данных? вполне естественно, что Аксапта, получив команду на открытие транзакции, заново формирует этот набор.

[Hyper]

Цитата:

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

честно говоря, из вашего примера это никоим образом не следует

Ради интереса, каким образом мой пример можно привести к update_recordset?

Цитата:

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

а поведение Аксапты ИМХО вполне логично. что такое закрытие транзакции как не обозначение границы операции с определённым набором данных? вполне естественно, что Аксапта, получив команду на открытие транзакции, заново формирует этот набор.

В Аксапте ttsbegin/ttscommit используется только для изменения/добавления/удаления данных. На простой while select без forupdate никакие ttsbegin/ttscommit влиять не должны. Посмотрите на мой последний прмер. В поздних версиях AX я таких проблем не встречал. Могу перепроверить, если интересно.

[oip]

Цитата:

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

Разумеется, одной транзакцией этого делать нельзя.

Почему "разумеется"? Иногда можно, иногда даже нужно, иногда нельзя. Зависит от ситуации.

Цитата:

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

да, если делать отдельный ttsbegin/ttscommit для каждой записи, все вроде работает. ...Но мне почему-то казалось, что групповое обновление записей (скажем, 500 или 1000 в одной транзакции) на больших объемах даст ощутимый прирост производительности...И вот такой вот код уже не работает:

А как-нибудь вот так?

    Table1 table1Loop;
    Table1 table1;
    int bulk = 5000;    // 5;
    int i;
;
    while select table1Loop
        order by recId
    {
        if (i==0)
            ttsbegin;
        i++;
        table1 = Table1::findRec(table1Loop.RecId, true);
        table1.Field1 =  strfmt("Blah %1", i);
        table1.doupdate();
        if (i mod bulk == 0)
        {
            ttscommit;
            ttsbegin;
        }
    }

    if (i>0)
        ttscommit;

[Hyper]

Цитата:

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

Почему "разумеется"? Иногда можно, иногда даже нужно, иногда нельзя. Зависит от ситуации.

700 000 записей в одной транзакции? Причем там не одна такая таблица.

Цитата:

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

А как-нибудь вот так?

Так тоже пробовал. Глючило - ругалось на что-то вроде unbalanced ttsbegin/ttscommit.

[oip]

Цитата:

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

700 000 записей в одной транзакции? Причем там не одна такая таблица.

А почему нет?

Цитата:

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

Так тоже пробовал. Глючило - ругалось на что-то вроде unbalanced ttsbegin/ttscommit.

Ну так надо сделать их balanced. Я свой код не проверял, Аксапты сейчас нет, но свиду вроде с транзакциями все нормально.

[Hyper]

Цитата:

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

А почему нет?

Вроде бы чем больше измененных записей в одной транзакции, тем хуже Аксапта(/SQL?) справляется, причем при достижении определенного порога все еле ворочается?

Цитата:

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

Ну так надо сделать их balanced.

Хотя верно, я, вроде, не совсем так пробовал. Сейчас проверю.

[Hyper]

Цитата:

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

А как-нибудь вот так?

Перепроверил - получилось, спасибо. Обидно, правда, что куча времени теряется на внутренний select:

table1 = Table1::findRec(table1Loop.RecId, true);

Подумалось, может быть сделать что-то вроде следующего:

Table1 table1;
int bulk = 5000;    // 5;
RecId lastRecId;
int i;
;

do
{
    i = 0;
	
    ttsbegin;

    while select forupdate table1
        order by RecId
        where table1.RecId > lastRecId
    {
        table1.Field1 = strfmt("Blah %1", i);
        table1.doupdate();

        lastRecId = table1.RecId;
        i++;

        if (i >= bulk)
            break;
    }

    ttscommit;
}
while (i >= bulk);

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

[Ivanhoe]

Коллеги, я не программист, но разве так нельзя?

while select table1
{
    i++;
    ttsbegin;

    table1.selectForUpdate(true);
    table1.reread();

    table1.Field1 = strfmt("Blah %1", i);
    table1.doupdate();
    ttscommit;
}

[S.Kuskov]

Цитата:

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

разве так нельзя?

Можно, но reread = дополнительный select, что может оказаться накладным. К слову сказать если гарантировать отсутствие конфликтов (например запускать обработку на свободной базе), то можно попробовать и без него , но это от лукавого .

Обнаруженное поведение while select'а - это явный баг. Есть ещё вариант попробовать переписать цикл с использованием select ... next. В принципе то же самое, только вид сбоку, но кто его знает ...

[Hyper]

Цитата:

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

Коллеги, я не программист, но разве так нельзя?

Я пытаюсь избавиться от двух зол:
1) отдельный ttsbegin/ttscommit для каждой записи (из 700 000 в одной таблице)
2) единственный ttsbegin/ttscommit для всей таблицы (700 000 записей)

Оптимальным решением было бы разбить таблицу на, скажем, 70 кусков, т.е. изменить все записи за 70 транзакций.

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

Для остальных, если не извращаться, надо будет выбрать (1) или (2).

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

[fed]

Цитата:

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

Оптимальным решением было бы разбить таблицу на, скажем, 70 кусков, т.е. изменить все записи за 70 транзакций.

Не знаю, сработает ли это на третьей версии, но на 2009ой такое выражение работает:

for(i=0;i<70;i++)
{
       ttsbegin;
       while select forupdate table1
       where (table1.recid mod 70)==i
       {
              ....
              table1.update();

       } 

       ttscommit;

}

Поскольку на больших объемах данных, recid распределены достаточно равномерно, можно поделить объем на 70 почти одинаковых кусков.

[sukhanchik]

А обязательно исполнять запрос на X++? Можно ли пойти по "принципу 1С" - сформировать текст SQL запроса вида "UPDATE MyTable SET Field1=Value" и исполнить его в одной транзакции в отдельном подключении (new UserConnection()).

Не... я конечно понимаю, что правильнее писать на Х++, но если разница идет между тем - открывать курсор или не открывать, да еще и на большом объеме записей - то этот вариант тоже стоит рассмотреть. В общем-то в системе - тоже есть примеры использования прямого SQL. А в данном случае - создание отдельного подключения (connection) - вещь весьма оправданная - т.е. пока это подключение будет "висеть" - остальные честно смогут работать (если не будут конечно все массово пытаться также обновлять эту табличку).

Причем, важно - что в этой конструкции у Вас не будет Where (или таблица дробится по компаниям?), а значит не нужно заморачиваться на наличие индекса по полю, фигурирующему в условии Where (ну а даже если и есть компании - то все равно есть индекс по dataareaid и recid).

[mazzy]

Цитата:

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

Но мне почему-то казалось, что групповое обновление записей (скажем, 500 или 1000 в одной транзакции) на больших объемах даст ощутимый прирост производительности. Я не прав?

Не-а. Вернее, в большинстве случаев не прав.

В зависимости от версии SQL, от выставленного Recovery Model и от размера файла для логов.

Цитата:

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

700 000 записей в одной транзакции? Причем там не одна такая таблица.

легко. для SQL - это не страшные объемы.

Цитата:

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

Вроде бы чем больше измененных записей в одной транзакции, тем хуже Аксапта(/SQL?) справляется, причем при достижении определенного порога все еле ворочается?

Причем здесь Аксапта?

Цитата:

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

Оптимальным решением было бы разбить таблицу на, скажем, 70 кусков, т.е. изменить все записи за 70 транзакций.

Ерунда это и онанизм какой-то.


возвращаемся к параметрам.
1.
Если у вас SQL2000, то там есть блокировка на чтение. (или более старший SQL, но у вас установлен режим совместимости с SQL2000). только тогда имеет смысл заниматься "разбиением на, скажем, 70 кусков"

Если же у вас более новый SQL, то заниматься ерундой не стоит.

2.
Если у вас Recovery Model = Full, то на сколько бы кусков вы ни разбивали, все будет записываться в Transaction log.
Если у вас Recovery Model = Simple, то вы выигрываете только на том, что каждая транзакция сразу очищается И файл транзакций НЕ растет.

3.
вот и приходим к размеру лога.
как вы изящно выразились "при достижении определенного порога все еле ворочается". просто транзакция заполняет весь лог, и СКЛ начинает увеличивать файл лога. на увеличение файла тратит значительное время.

в SQL Management Studio вы можете посмотреть отчет (по-моему, Disk Usage) и ужаснуться сколько времени SQL тратит на увеличение размера.

кроме того, исходя из своего опыта рискну предположить, что у вас таки SQL2005 или выше и параметры для файлов выставлены по-умолчанию. Дело в том, что начиная с SQL2005 файл Transaction Log по умолчанию растет по 1Мб (один мегабайт! - это ужасный параметр по умолчанию)

===============
следовательно, перестаньте заниматься фигней.
700тыс записей - смешной объем как для Аксапты, так и для СКЛ.

посмотрите в параметры СКЛ.
прежде всего увеличьте минимальный размер Transaction Log до вменяемого значения (поставьте гиг 5)
обязательно установите вменяемый размер прироста в фиксированных единицах (например, по 200-300Мб). Ни в коем случае не оставляйте по 1Мб. Так вы сократите фрагментацию как диска, так и внутреннюю SQL. Так вы минимизируете накладные расходы времени на рост Transaction Log.

И не парьтесь "кусками".
Сделайте или в одной нормальной транзакции. Или сделайте транзакцию на каждую запись.
Разница только логическая - если прервете обработку, то либо отменятся все изменения, либо останется то, что сделано.

[Hyper]

Цитата:

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

рискну предположить, что у вас таки SQL2005 или выше

Спасибо, но у клиента как раз SQL 2000. Надо мне было сразу уточнить. Смотрю пункт 1:

Цитата:

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

тогда имеет смысл заниматься "разбиением на, скажем, 70 кусков"

Вот и занимаюсь. SQL 2000 каким-то образом влияет на следующие рекомендации, или они остаются в силе?

Цитата:

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

прежде всего увеличьте минимальный размер Transaction Log до вменяемого значения (поставьте гиг 5)
обязательно установите вменяемый размер прироста в фиксированных единицах (например, по 200-300Мб). Ни в коем случае не оставляйте по 1Мб.

А вот следующее было для меня откровением, я был уверен, что разница принципиальная, а не "только логическая":

Цитата:

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

Сделайте или в одной нормальной транзакции. Или сделайте транзакцию на каждую запись.
Разница только логическая - если прервете обработку, то либо отменятся все изменения, либо останется то, что сделано.

[Hyper]

Цитата:

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

Не знаю, сработает ли это на третьей версии, но на 2009ой такое выражение работает

Спасибо за оригинальную идею, но в данном примере 70 раз производится выборка по RecId, так что при отсутствии индекса по RecId экспериментировать, пожалуй, не имеет смысла?