Исследование скорости экспорта данных из Axapta в Excel (коллективный эксперимент)

[AndyD]

Несколько предложений по исследованию Gustav различных вариантов использования ComExcelDocument_RU

1. При создании нового файла Excel использован код

excel.newFile("", true, -1);

Замечание по поводу второго параметра _bVisible=true - при массовой вставке данных в лист Excel будет довольно ощутимое замедление выгрузки. Еще одна особенность при таком параметре - если пользователь в листе Excel во время вызгрузи выделит ячейку мышкой, то выполнение прервется с ошибкой.
Первый (самый простой) вариант - открывать в невидимом режиме и показывать после выгрузки всех данных.
Второй вариант - запретить перед экспортом пользовательский ввод и обновления в открытой книге.

    COM app;
    ;
...
    app = doc.Application();
    app.Interactive(False); // запрет пользовательского ввода
    app.ScreenUpdating(False); // запрет обновления окна при изменении данных
...
    app.Interactive(True);
    app.ScreenUpdating(True);

2. Хотелось бы вступиться за Range . Значительное увеличение времени в первом варианте присходит из-за реализации метода findRange(), используемого в insertValue(). Если заменить этот код на более простой (часть кода взята из второго варианта)

    COM doc;
    COM actSheet;
    COM range;
...
    doc = excel.getComDocument();
    actSheet = doc.ActiveSheet();
...
    while ()
    {
...
        range = actSheet.range(ComExcelDocument_RU::numToNameCell( 1, row));
        range.Value2(ledgerTrans.RecId);
        range = actSheet.range(ComExcelDocument_RU::numToNameCell( 2, row));
        range.Value2(ledgerTrans.AccountNum);
...

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

3. Окончательная реабилитация Range.
Третий вариант с использованием только интерфейсов - вставка при помощи массивов

    COM actSheet;
    COM range;
    Array arr;
...
    while ()
    {
...
        arr = new Array(Types::String);
        arr.value( 1, strfmt("%1", ledgerTrans.RecId));
        arr.value( 2, strfmt("%1", ledgerTrans.AccountNum));
        arr.value( 3, strfmt("%1", ledgerTable.AccountName));
        arr.value( 4, strfmt("%1", ledgerTable.AccountPlType));
        arr.value( 5, strfmt("%1", ledgerTrans.BondBatchTrans_RU));
        arr.value( 6, strfmt("%1", ledgerTrans.BondBatch_RU));
        arr.value( 7, strfmt("%1", ledgerTrans.TransDate));
        arr.value( 8, strfmt("%1", ledgerTrans.Txt));
        arr.value( 9, strfmt("%1", ledgerTrans.AmountMST));
        arr.value(10, strfmt("%1", ledgerTrans.Crediting));
        range = actSheet.range(strfmt("A%1:J%1", row));
        range.value2(COMVariant::createFromArray(arr));

Этот вариант по сравнению со вторым в моем случае дает увеличение скорости до 7 раз

PS. Упс. Ошибка. Так делать нельзя.

excel.insertValue(range, ledgerTrans.AccountNum);

На самом деле хотел вставить

excel.insertValueInRange(range, ledgerTrans.AccountNum);

но этот метод объявлен как protected в стандарте. Если необходимо - можно изменить объявление класса, либо просто вставлять значения так

range.Value2(ledgerTrans.AccountNum);

[Gustav]

Цитата:

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

Замечание по поводу второго параметра _bVisible=true - при массовой вставке данных в лист Excel будет довольно ощутимое замедление выгрузки. Еще одна особенность при таком параметре - если пользователь в листе Excel во время вызгрузи выделит ячейку мышкой, то выполнение прервется с ошибкой.

Ну да, да, да, да! Ущучил, блин! Как раз сам собирался покаяться, потому что сегодня смотрел варианты с "SysExcel сотоварищи" и там вспомнил и засек. Да, разница в скорости порядка 10-15 процентов. Об остальном - завтра-послезавтра. С Array - круто! Замерим! Только это не "реабилитация", а, скорее, надо взять всё лучшее из разных подходов и синергически перемножить... Может, к минуте-двум и подтянем такой "ComExcelDocument_RU"

P.S. Собственно говоря, Range-то я и не хаял. Я хаял "заднепроходность" излишнего преобразования номеров строки и колонки в текстовый адрес только для того, чтобы воспользоваться синтаксисом Range("A1") вместо того, чтобы без лишних телодвижений сразу применить что-то вроде Range.Item(1,1)

[Gustav]

Цитата:

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

Значительное увеличение времени в первом варианте присходит из-за реализации метода findRange(), используемого в insertValue(). Если заменить этот код на более простой (часть кода взята из второго варианта)

COM doc;
    COM actSheet;
    COM range;
...
    doc = excel.getComDocument();
    actSheet = doc.ActiveSheet();
...
    while ()
    {
...
        range = actSheet.range(ComExcelDocument_RU::numToNameCell( 1, row));
        range.Value2(ledgerTrans.RecId);
        range = actSheet.range(ComExcelDocument_RU::numToNameCell( 2, row));
        range.Value2(ledgerTrans.AccountNum);
...

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

AndyD, всё же несколько слов по поводу "2."

Итак, что мы имеем? Выяснено:
1. Первый способ - джоб SpeedTest_Job1, основанный на использовании методов ComExcelDocument_RU, медленнее, чем SpeedTest_Job2, основанный на COM, примерно в 3 раза.
2. Причиной трёхкратного замедления являются методы класса ComExcelDocument_RU:
- InsertValue (записывает значение в ячейку),
- numToNameCell (вычисляет текстовый адрес ячейки).

То, что "загрузка хотя и будет происходить медленнее второго способа, но разница будет составлять уже проценты, не разы", просто говорит о том, что метод InsertValue (с findRange внутри), пардон, "гадит" на характеристики процесса гораздо больше, чем метод numToNameCell.

На мой взгляд, использование этого комбинированного варианта

COM range;
.........
range = actSheet.range(ComExcelDocument_RU::numToNameCell( 1, row));
range.Value2(ledgerTrans.RecId);

является полумерой, так как мы устраняем влияние лишь метода InsertValue (т.е. главного виновника), оставляя зачем-то определение всё того же текстового адреса ячейки через numToNameCell - вот и находятся эти самые "уже проценты, а не разы" расхождения.

Но использование Value2 в чистом виде - это уже не совсем класс ComExcelDocument_RU.
Тогда зачем нам оставлять в алгоритме numToNameCell? Сказав "А", скажем и "Б":

COM cells;
.........
COM::createFromVariant( cells.Item(row, 1) ).Value2( ledgerTrans.RecId );

- тут нам и Value2, и адресация ячейки в виде "номер строки, номер столбца" - как раз то, что нужно в цикле... И уже никакой прямой связи с ComExcelDocument_RU

[Gustav]

Замерил способ "через SysExcel":

static void SpeedTest_Job3_SysExcel(Args _args)
{
     
    LedgerTrans             ledgerTrans;
    LedgerTable             ledgerTable;
    
    int row;
    int timeFullStart, timeFullFinish, timeFullTotal;
    
    SysExcelCells          cells;
    
    COM rng, xlApp;
    
    ;
    
    timeFullStart = timenow();
    
    // обратите внимание, что если нам нужны, например, только ячейки рабочего листа 1,
    // то нет необходимости выделять отдельные переменные для всех вышестоящих объектов
    // можно всё указать в одной строке через точки - почти как в VBA :)
        
    cells = SysExcelApplication::construct().workbooks().add().worksheets().itemFromNum(1).cells();
        
    // но нам потребуется объект Application - хотя бы для того, чтобы сделать Excel видимым
    // в рамках примера получим его как "COM от COM-а" любой ячейки, например, А1
        
    rng = cells.range('A1').comObject(); // Range
    xlApp = rng.Application();           // Excel.Application
        
    xlApp.Visible(true); // в эсперименте на скорость - комментировался/раскомментировался этот оператор
        
    // повторяю - этот "изврат" выше сделан для целей примера
    // без использования COM-переменных можно было бы последовательно оформить вот так:
    //    SysExcelApplication  xlApp;
    //    SysExcelCells        cells;
    //    ................................
    //    xlApp = SysExcelApplication::construct();
    //    cells = xlApp.workbooks().add().worksheets().itemFromNum(1).cells();
    // и дальше спокойно распоряжаться этими двумя переменными
    // для работы с ячейками первого рабочего листа новой рабочей книги
    
    row = 0;
    
    while select  ledgerTrans
            join  ledgerTable
            where ledgerTrans.AccountNum == ledgerTable.AccountNum
    {
        row++;
        if (row > 50000) break;
    
        cells.item( row,  1).value( ledgerTrans.RecId                       );
        cells.item( row,  2).value( ledgerTrans.AccountNum                  );
        cells.item( row,  3).value( ledgerTable.AccountName                 );
        cells.item( row,  4).value( strfmt('%1', ledgerTable.AccountPlType) );
        cells.item( row,  5).value( ledgerTrans.BondBatchTrans_RU           );
        cells.item( row,  6).value( ledgerTrans.BondBatch_RU                );
        cells.item( row,  7).value( ledgerTrans.TransDate                   );
        cells.item( row,  8).value( ledgerTrans.Txt                         );
        cells.item( row,  9).value( ledgerTrans.AmountMST                   );
        cells.item( row, 10).value( strfmt('%1', ledgerTrans.Crediting)     );
    
    }
    
    xlApp.visible(true);
    
    timeFullFinish = timenow();
    
    timeFullTotal = timeFullFinish - timeFullStart;
    
    info('Время выполнения, сек');
    info(int2str(timeFullTotal));
    
}

Результата привожу два:
1. 1228 сек - т.е. чуть больше 20 минут - когда сразу делаем видимым окно Excel в начале процедуры (см. в коде "xlApp.Visible(true); // в эсперименте на скорость...")
2. 1060 сек - т.е. чуть меньше 18 минут - когда показываем Excel только в конце (см. в коде "// xlApp.Visible(true); // в эсперименте на скорость...")

Делать Visible(true) в начале - плохая практика, AndyD абсолютно прав! Но в рамках эксперимента, конечно, можно посмотреть и так, и сяк

С джобами SpeedTest_Job1 и SpeedTest_Job2 имеет смысл сравнивать только первый результат, поскольку и они тоже прогонялись при начальном Visible(true). Результат "1228 сек" несколько больше, чем у _Job2 (1090 сек), и это объяснимо: представители семейства системных классов SysExcel оформлены как "обертки" над COM и, соответственно, часть времени тратится на "продирание" через эти "обертки", в то время, как _Job2 фактически использует чистый COM.

Результат "1228 сек" ниже "1060 сек" примерно на (1228-1060)/1228 = около 14 % - что я и обещал:

Цитата:

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

Да, разница в скорости порядка 10-15 процентов.

Конечно, с точки зрения скорости способ "через SysExcel" также оставляет желать гораздо более лучшего и в качестве широкого использования "как быстрый способ" рекомендоваться не может.

Мне он, однако, понравился другим, в частности:
- возможностью писать "почти как на VBA c многими точками" (см. комментарии в тексте джоба),
- писать нормально Value, а не Value2 (при этом класс SysExcelCell сам берет на себя ответственность за обработку value - "до XP или после" ).

[Gustav]

Цитата:

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

Результат "1228 сек" ниже "1060 сек"

ВЫШЕ, конечно (в смысле: ВЫШЕ - по значению, НИЖЕ - по скорости... ну, вы меня поняли )

Редактировать прямо там боюсь, так как съедет форматирование кода X++.

Кстати, почему такое происходит с тегом XPP? Т.е. пишу сообщение, делаю предварительный просмотр - пока всё хорошо, пишу дальше, делаю повторный предварительный просмотр - и всё разрушается - код сливается с текстом, отступы пропадают - в общем, беда какая-то...