Миф об уникальности RecId?

[Ivanhoe]

Возможно, у автора появился повод для перехода на новую версию?

[Gustav]

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

Честно скажу, в промышленной эксплуатации не пользовал (не припёрло), но на тестовой всё отрабатывало довольно прилично. Axapta - 3.0 SP4, СУБД - Oracle 10.

Общий смысл такой - заставить Аксапту автоматически использовать имеющиеся дыры RecId. Для этого нужно провести подготовительную работу: при помощи самописного скрипта пробежаться по всем таблицам системы и собрать информацию о неиспользованных интервалах RecId. Для Ax 3.0 (двухзвенка, без АОС) имеет смысл отбирать только непрерывные диапазоны размером не менее 25, т.е. не меньше размера кэша. Далее грузим полученные диапазоны в таблицу дыр (не аксаптовскую, просто созданную на уровне БД в той же схеме):

CREATE TABLE RECIDHOLES
(
  FROMRECID NUMBER(10),
  TORECID   NUMBER(10)
)

Для пущей убедительности добавим проверочку на размер диапазона не менее 25 упомянутых выше идентификаторов

ALTER TABLE RECIDHOLES ADD (
  CHECK (TORECID-FROMRECID+1>=25))

Ну и наконец на таблицу SystemSequences вешаем триггер BEFORE UPDATE:

CREATE OR REPLACE TRIGGER SystemSequences_TBU
BEFORE UPDATE
ON SYSTEMSEQUENCES REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
WHEN (
SUBSTR(NLS_LOWER(Old.DataAreaId),1,3) = 'ppp' 
      AND Old.Id = -1 
      AND Old.TabId = 0
      )
DECLARE
    currNextVal NUMBER(10);
    cntBetween  NUMBER(10);
    cntAbove    NUMBER(10);   
    minDelta    NUMBER(10);
    b4switching NUMBER(10);
    holesRange  RecIdHoles%ROWTYPE;
    
BEGIN
    b4switching := 1118091751; -- NextVal перед переключением (передвинуть с запасом на 100-200, чтобы не схватили)
    minDelta    := 25; -- Axapta 3.0 Cache Size
    
    currNextVal := :New.NextVal; -- значение, которое собирается вставить Аксапта
    
    -- если здесь 1, то более ничего не делаем - :New.NextVal проходит в таблицу
    SELECT COUNT(*) INTO cntBetween 
        FROM RecIdHoles h 
        WHERE currNextVal BETWEEN h.FromRecId AND h.ToRecId
          AND h.ToRecId - currNextVal + 1 >= minDelta;  

    IF cntBetween = 0 THEN    
        -- иначе попадаем сюда и устанавливаем новое значение, равное FromId следующего диапазона
        SELECT COUNT(*) INTO cntAbove 
            FROM RecIdHoles h 
            WHERE h.FromRecId > currNextVal
              AND h.ToRecId - h.FromRecId + 1 >= minDelta; -- на всякий случай

        IF cntAbove > 0 THEN -- если мы в диапазонах, охватываемых таблицей дыр RecIdHoles
    
            SELECT * INTO holesRange
                FROM (SELECT * FROM RecIdHoles h 
                      WHERE h.FromRecId > currNextVal
                        AND h.ToRecId - h.FromRecId + 1 >= minDelta -- на всякий случай                 
                      ORDER BY h.FromRecId)
                WHERE ROWNUM = 1;
            
            currNextVal := holesRange.FromRecId;
            
        ELSE -- если уже нет
         
            IF currNextVal <= b4switching THEN -- значит кончились записи в таблице дыр RecIdHoles
                currNextVal := b4switching;
            END IF;
            -- если currNextVal > b4switching, то уже всё поехало нормально
            
        END IF;
        
        :New.NextVal := currNextVal;             
    END IF;              

   EXCEPTION
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END SystemSequences_TBU;

Всё! Аксапта начинает генерить RecId под управлением диапазонов таблицы RECIDHOLES.

P.S. ОГРАНИЧЕНИЯ:

1. В текущей редакции триггер можно использовать на монотонно возрастающем участке генерации RecId, достаточно далеком от крайних значений, т.е. перевалка через максимальное значение (2G) и продолжение с минимального (-2G) текущим алгоритмом не предусмотрена.

2. У таблицы SystemSequence в Ax 3.0 имеется метод setCacheSize, позволяющий установить размер кэша иным, нежели 25. Перед использованием триггера рекомендуется проверить код приложения Аксапты на присутствие вызовов этого метода (у меня не было ни одного). При необходимости можно увеличить minDelta в триггере до значения максимального параметра этих вызовов, либо (более муторно) в триггере предусмотреть генерирование ошибки (исключения) при попытке Аксапты сделать шаг больше, чем 25. По иронии судьбы в Ax 4.0 метод setCacheSize отсутствует, но в "четверке" уже и подобный триггер не нужен

[Vadik]

А код, который отыщет свободные диапазоны RecId в компании на объеме данных, сопоставимом с постановкой задачи (500Гб) - будет?

[Gustav]

Цитата:

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

А код, который отыщет свободные диапазоны RecId в компании на объеме данных, сопоставимом с постановкой задачи (500Гб) - будет?

Поделюсь всем, что имею на эту тему, без утайки. Я готовил диапазоны дыр в своем любимом MS Access и далее заполнял ими таблицу RECIDHOLES в схеме Аксапты в Оракле. В Аксесе обработка очень простая: в одну табличку собираем все существующие RecId и затем в другую складываем диапазоны НЕсуществующих RecId, т.е. дыры.

Для этих манипуляций у меня имеется джоб оббегания по таблицам Аксапты и сохранения данных в Аксесе:

static void Job350_AllRecIds(Args _args)
{
    str             strFileName = @'C:\AxForumTests\Gustav\RecId.mdb';
    str             strFolderName;

    Filename        filename;
    FilenameType    filenameType;
    FilePath        filePath;
    container       conPath;

    COM             dbe, db;
    COM             cnn, rst;
    COM             flds, fld;

    int             i, nLines;
    int             timeFullStart, timeFullFinish;

    Dictionary      dictionary = new Dictionary();
    TableId         tableId;
    DictTable       dictTable;
    Common          common;
    int             row, timeStart;
    int             recordCount;

    #CCADO
    #define.dbLangGeneral(';LANGID=0x0409;CP=1252;COUNTRY=0')

    void recreateAccessTable(str _table, str _fields)
    {
        try
        {
            nLines = infolog.line();
            db.Execute('DROP TABLE [' + _table + ']');
        }
        catch   (Exception::Error)
        {
            infolog.clear(nLines);
        }

        db.Execute( 'CREATE TABLE [' + _table + '] (' + _fields + ')' );
    }
    ;

    timeFullStart = timenow();

    // parse file name -----------------------------------------------------------------------------
    [filePath, filename, filenameType] = fileNameSplit(strFileName);

    conPath = str2con_RU(filePath, '\\'); // you can use str2con if you do not have Russian DIS-layer

    if (subStr(filePath,2,1) != ':')
    {
        info(@'For drive use only one character syntax: C:\...! Do not use: \\server\folder\...!');
        return;
    }

    // create folders if they not exist ------------------------------------------------------------
    if (conlen(conPath)>1)
    {
        strFolderName = conpeek(conPath,1); // C:

        for (i=2; i<=conlen(conPath); i++)
        {
            strFolderName += strFmt(@'\%1', conpeek(conPath,i)); // C:\AxForumTests...

            if (!WinAPI::folderExists(strFolderName))
                 WinApi::createDirectory(strFolderName);
        }
    }

    // create MDB-file if not exists ---------------------------------------------------------------
    dbe = new COM('DAO.DBEngine.36');

    if (!WinAPI::fileExists( strFileName ))
    {
        try
        {
            nLines = infolog.line();
            db = dbe.CreateDatabase(strFileName, #dbLangGeneral);
        }
        catch  (Exception::Error)
        {
            infolog.clear(nLines);
            info( 'Probably mdb-file already exists and is open at the moment!' );
        }
    }
    else
    {
        db = dbe.OpenDatabase( strFileName );
    }

    // (re)create tables in MDB-file ----------------------------------------------------------------
    recreateAccessTable(
        'UsedRecId',
            'TblId          LONG,   ' +
            'DataAreaId     TEXT(3),' +
            'RecId          LONG    ' );

    recreateAccessTable(
        'RecIdHoles',
            'FromRecId      LONG,   ' +
            'ToRecId        LONG    ' );

    db.Close();
    db = null;
    dbe = null;

    // export EmplTable (a few fields) from Axapta to similar table in Access ----------------------
    cnn = new COM('ADODB.Connection');
    cnn.connectionString('Provider=Microsoft.Jet.OLEDB.4.0;' +
                         'Data Source=' + strFileName);
    cnn.Open();

    rst = new COM('ADODB.Recordset');
    rst.LockType(#adLockOptimistic);
    rst.Open('UsedRecId', cnn);

    flds = rst.Fields();

    row = 0;
    for (i=1; i<= dictionary.tableCnt(); i++)
    {
        tableId   = dictionary.tableCnt2Id(i);

        dictTable = new DictTable(tableId);

        print strFmt('%1 -- %2 -- %3', tableId, dictTable.name(), row);

        // если в очередной таблице нет записей
        // то переходим к следующей
        try
        {
            nLines = infolog.line();
            recordCount = new SysDictTable(tableId).recordCount();
        }
        catch //может случиться, если таблица есть в репозитарии, но нет в базе
        {
            infolog.clear(nLines);
            recordCount = 0;
        }
        if (! recordCount)
            continue;

        common = dictTable.makeRecord();

        // цикл по записям таблицы (МОЖНО ОГРАНИЧИТЬ ДИАПАЗОН ПОИСКА RecId ПО ВСЕМ ТАБЛИЦАМ)
        while select common
            where common.RecId >= 800000001 && common.RecId <= 900000000
        {
            row++;
            rst.AddNew();
                fld = flds.Item('TblId'     ); fld.Value(tableId);
                fld = flds.Item('DataAreaId'); fld.Value(common.dataAreaId);
                fld = flds.Item('RecId'     ); fld.Value(common.RecId);
            rst.Update();
        }
    }

    rst.Close();
    rst = null;

    cnn.Close();
    cnn = null;

    timeFullFinish = timenow();
    box::info(strfmt('Total running time: %1 sec -- Records: %2', timeFullFinish - timeFullStart, row));
}

Обратите внимание, что можно задать диапазон поиска RecId и таким образом разобраться с базами Аксапты любых объемов, просто деля задачу на части (т.е. на диапазоны поиска RecId по всем таблицам) и даже создавая для каждой части отдельный mdb-файл. Тут необходимо помнить, что имеется ограничение на максимальный размер mdb-файла = 2 Гб (по крайней мере, раньше было такое ограничение, не знаю как сейчас).

Далее уже в Аксесе создаем VBA-модуль со следующей начинкой:

Option Compare Database
Option Explicit

Sub packHolesInIntSequenceToRanges()
'===================================================================
' Сворачивание "дырок" в последовательности целых чисел в диапазоны
'===================================================================

    Dim cnn As ADODB.Connection
    Dim rstSource As ADODB.Recordset
    Dim rstRanges As ADODB.Recordset
   
    Dim rangeNum   As Integer 'счетчик непрерывных диапазонов
    
    Dim curr      As Long  'текущее (очередное) значение из последовательности
    Dim prev      As Long  'предыдущее значение (конец непрерывного диапазона)
    
    Const minDelta As Long = 25    'минимальный размер непрерывного диапазона
                                   '(диапазоны меньшей длины игнорируются)
                                   
    Const startFrom As Long = 15082620 '15000001 'стартовое значение процессса - либо "дырка", либо нет
                                       'первый prev все равно на 1 меньше и считается не дыркой
    Const stopOn As Long = 19997895 'следующий NextVal - 25*кол-во пользователей - последний curr - считается дыркой
    
    
    Set cnn = Application.CurrentProject.AccessConnection
    
    Set rstSource = New ADODB.Recordset
    With rstSource
         Set .ActiveConnection = cnn
         .source = "SELECT * FROM UsedRecId WHERE DataAreaId=""ppp""" & _
                   " AND RecId >= " & CStr(startFrom) & _
                   " AND RecId <= " & CStr(stopOn) & " ORDER BY RecId"
                   
         .Open
    End With
    
    Set rstRanges = New ADODB.Recordset
    With rstRanges
         Set .ActiveConnection = cnn
         .source = "RecIdHoles"
         .LockType = adLockOptimistic
         .Open
    End With
       
    rangeNum = 0
    prev = startFrom - 1
    
    Do While Not rstSource.EOF
    
        curr = rstSource("RecId").Value
        
        If curr - prev > 1 Then
            'записываем диапазон, закончившийся на предыдущем
            If curr - prev - 1 >= minDelta Then 'здесь именно минус 1
                rangeNum = rangeNum + 1
                Debug.Print rangeNum
                rstRanges.AddNew
                    rstRanges("FromRecId").Value = prev + 1 'From
                    rstRanges("ToRecId").Value = curr - 1 'To
                rstRanges.Update
            End If
        End If
        
        prev = curr
        rstSource.MoveNext
    Loop
    
    'для последнего диапазона
    curr = stopOn + 1 'не дырка
    If curr - prev > 1 Then
        'записываем диапазон, закончившийся на предыдущем
        If curr - prev - 1 >= minDelta Then 'здесь именно минус 1
            rangeNum = rangeNum + 1
            Debug.Print rangeNum
            rstRanges.AddNew
                rstRanges("FromRecId").Value = prev + 1 'From
                rstRanges("ToRecId").Value = curr - 1 'To
            rstRanges.Update
        End If
    End If
    
    Set rstRanges = Nothing
    Set rstSource = Nothing
    Set cnn = Nothing
    
End Sub

Собственно, эта единственная процедура packHolesInIntSequenceToRanges и запускается. Она ищет непрерывные последовательности дырок RecId длиной не менее 25 в аксесной табличке UsedRecId и складывает их в аксесную же табличку RecIdHoles (таблички были созданы в mdb-файле упомянутым выше аксаптовским джобом, а UsedRecId еще и заполнена им же).

Последующий перенос данных из аксессной RecIdHoles в RECIDHOLES в схеме Аксапты - любым желаемым способом, вплоть до приаттачивания RECIDHOLES к файлу MDB как таблицы ODBC и элементарного ручного копипаста из одной таблицы в другую. Или через Excel - вставляем данные RecIdHoles в колонки A и B, в ячейке C1 пишем формулу ="INSERT INTO RECIDHOLES VALUES ("&A1&","&B1&");" и копируем ее на следующие строки; далее копируем содержимое колонки С в QA (для MS SQL Server) или TOAD (для Oracle) и исполняем этот набор операторов INSERT.

[Vadik]

Цитата:

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

CREATE TABLE RECIDHOLES
(
  FROMRECID NUMBER(10),
  TORECID   NUMBER(10)
)

..

CREATE OR REPLACE TRIGGER SystemSequences_TBU
BEFORE UPDATE
ON SYSTEMSEQUENCES REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
WHEN (
SUBSTR(NLS_LOWER(Old.DataAreaId),1,3) = 'ppp' 
      AND Old.Id = -1 
      AND Old.TabId = 0
      )

А если компаний более одной?

while select common
where common.RecId >= 800000001 && common.RecId <= 900000000
{
    row++;
    rst.AddNew();
    fld = flds.Item('TblId'     ); fld.Value(tableId);
    fld = flds.Item('DataAreaId'); fld.Value(common.dataAreaId);
    fld = flds.Item('RecId'     ); fld.Value(common.RecId);
    rst.Update();
}

А сколько времени нужно чтобы отработал этот код в компании с числом записей в несколько сотен миллионов? А триггер проверялся для случая с несколькими активными "писателями" (толстыми клиентами и AOS-ами) в SystemSequences ? Я конечно предвижу ответ что и диапазоны можно в разрезе компаний хранить и триггер переписать, но что-то вся обвязка становится слишком похожей на самолет-этажерку. Не проще вправить мозг стандартному "дефрагментатору" и успокоиться еще на пару лет?
P.S. Особенно если в простых случаях (нет виртуальных компаний и правильное наследование типов для ссылок по RecId) все и так само собой работает

[Gustav]

Цитата:

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

А если компаний более одной?

Ну, несколько компаний можно прописать либо через условие триггера:

WHEN (
(SUBSTR(NLS_LOWER(Old.DataAreaId),1,3) = 'ppp' AND Old.Id = -1 AND Old.TabId = 0)
OR
(SUBSTR(NLS_LOWER(Old.DataAreaId),1,3) = 'rrr' AND ...)
OR
(SUBSTR(NLS_LOWER(Old.DataAreaId),1,3) = 'sss' AND ...)
     )

либо создав для каждой компании свой отдельный триггер (их вроде может быть несколько на одно событие). Соответственно, внутри придется развить логику на эти несколько компаний. Но обычно критической по заканчивающимся RecId является какая-то одна. У нас, например, есть вторая (помимо dat, конечно), в которую транслируются данные из основной для ГААПа, так там любо-дорого посмотреть - дыр практически нет, всё последовательно и текущее максимальное значение раз в 30 меньше текущего максимального из основной (русской) компании. А в основной построишь какую-нибудь подробную оборотно-сальдовую ведомость по складу или ОС - 100 тыщ RecId улетело в трубу (Привет, GMCS!)

Цитата:

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

А сколько времени нужно чтобы отработал этот код в компании с числом записей в несколько сотен миллионов?

Навскидку не помню... Не одна минута конечно, но и не какое-то невероятное кол-во времени (типа несколько суток, недель), способное удручить и заставить отказаться от эксперимента. Я обычно оставлял на ночь, утром приходил - заказанная порция была готова. Порция внушительная, в джобе же виден диапазон реального перебора в 100 миллионов (от 800 до 900 млн). Для 500 млн. пусть будет нуу... часов 10-12...Не имею возможности сейчас проверить, но тот, кто заинтересуется, может погонять на своей системе и сообщить получающееся время.

И совсем же необязательно сразу искать дыры во всем диапазоне от 0 до текущего значения NextVal. Допустим, текущий NextVal в районе 1.5 миллиардов, тогда можно, не торопясь, составить таблицу дыр для RecId = 0..500 млн, настроить триггер, запустить его в эксплуатацию. А через годик обработать уже диапазон 500 млн..1 млрд и т.д. Это ж всё разовые задачки - обработал, запустил, забыл ("украл, выпил, в тюрьму" (с))

Цитата:

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

А триггер проверялся для случая с несколькими активными "писателями" (толстыми клиентами и AOS-ами) в SystemSequences ?

Мы без АОСа, на двухзвенке, клиенты у нас все толстые. Несколько одновременно активных "писателей" захватывали свои порции по 25 штук вполне благополучно.

Цитата:

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

..но что-то вся обвязка становится слишком похожей на самолет-этажерку. Не проще вправить мозг стандартному "дефрагментатору" и успокоиться еще на пару лет?

Я ж только предлагаю, не навязываю же...

[Владимир Максимов]

Цитата:

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

А если компаний более одной?
А сколько времени (...)? А триггер проверялся для случая (...)? (...) Не проще вправить мозг стандартному "дефрагментатору" и успокоиться еще на пару лет?

Я тут за автора попробую ответить.

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

Теперь по вопросам:

1. Насчет компаний, сам же и ответил. Не вижу особых проблем добавить фильтр по компаниям

2. Время выполнения определения "дыр"

Очевидно, по крайней мере, будет не больше, чем время дефрагментации. Кроме того, очевидно, есть предмет для оптимизации алгоритма. Чего не скажешь про алгоритм дефрагментации.

Ведь поиск дыр - это чистые запросы, а дефрагментация - это запросы+модификация. Если запросы еще можно оптимизировать (да и сам алгоритм изменить), то с модификацией особых вариантов нет. Надо модифицировать ВСЕ записи. Быстро это выполнено быть не может

Кроме того, как справедливо заметил Gustav, можно разбить процесс поиска дыр на этапы, чего в принципе невозможно сделать для дефрагментации.

3. Преимущества, по сравнению с дефрагментацией (явно не прозвучало, но вопрос очевиден)

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

Цитата:

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

P.S. Особенно если в простых случаях (нет виртуальных компаний и правильное наследование типов для ссылок по RecId) все и так само собой работает

Как мне кажется, это из области фантастики. Большинство российских внедрений кастомизировано по самое "не балуйся". Причем не один раз и самыми разным людьми с очень разным опытом. Поэтому простых случаев не может быть "по определению"

[Vadik]

И правда, если

    EDT  ,  RecId

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

Цитата:

Судя по не утихающим до сих пор дискуссиям, в решении данной проблемы простота и надежность не являются приоритетными критериями

да уж, богата земля рассейская Кулибиными

[glibs]

Цитата:

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

...
Не проще вправить мозг стандартному "дефрагментатору" и успокоиться еще на пару лет?
...

Судя по не утихающим до сих пор дискуссиям, в решении данной проблемы простота и надежность не являются приоритетными критериями.

[Vadik]

Цитата:

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

Возможно, у автора появился повод для перехода на новую версию?

- неспортивно
- неуплаченная "дань за двенадцать лет" (поддержка) зело велика будет
- перевод тройки на 2009 - челлендж покруче любой дефрагментации RecId

[Ivanhoe]

Цитата:

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

- неспортивно
- неуплаченная "дань за двенадцать лет" (поддержка) зело велика будет
- перевод тройки на 2009 - челлендж покруче любой дефрагментации RecId

1. Ну а что делать Повод просто хороший. Для заинтересованной стороны. С таким объемом на тройке пора бы задуматься о переходе, imho. На 6.0 переходить будет еще сложнее. AX2009 сейчас уже более-менее оттестирована - два SP и кучка RU вышла.
2. Про неуплаченную дань не в курсе, я что-то пропустил? =)
3. Ну, можно вообще дождаться чьего-нибудь пожелания перейти на SAP - мне кажется с т.з. сотрудников поддержки Ax это еще большее зло

[Vadik]

Цитата:

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

Про неуплаченную дань не в курсе, я что-то пропустил? =)

Я взял на себя смелость предположить что будь в свое время проплачена поддержка, топикстартер в 2010 году на версии 3.0 не сидел бы и сейчас просто взять и получить лицензии на 2009 задешево не получится

[Alexius]

А нет ли в базе чего-нибудь "лишнего" ? Типа :
1. InventSettlement (Cancelled == true)
2. InventSumLogTTS (не используется сводное планирование)
3. SysDataBaseLog и прочие логи
4. SalesParmTable и подобные *Parm*
5. и т.д.

Может можно почистить базу и облегчить АХ задачу по дефрагментации ?