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

[Gustav]

Уважаемые коллеги,

Поводом для организации данной ветки послужила дисскуссия отсюда: Какой способ для связи Axapta с Excel вы предпочитаете?

Так вот. Предлагается количественно сравнить скорость экспорта данных из Axapta в Excel при использовании различных способов выгрузки. Все желающие могут поучаствовать в эксперименте. Каждый участник выбирает свой любимый (или нелюбимый – как кому нравится) способ (или способы), «прогоняет» его (их) и вкратце информирует остальных о результатах своих изысканий (можно приложить джоб или проект). Если есть возможность проверить свои способы на разных по характеристикам компьютерах – всячески приветствуется!

Сравнение предлагается производить в сходных (по объему экспортируемых данных) условиях. Вот эти условия:

1. Выводим в Excel данные из двух связанных таблиц: LedgerTrans и LedgerTable.

2. Объем вывода – примерно 50 тысяч записей, 10 полей (список ниже).

3. В Аксапте данные получаем в цикле:

    LedgerTrans ledgerTrans;
    LedgerTable ledgerTable;
    int timeFullStart, timeFullFinish, timeFullTotal;
    ……………………………
    // засекаем время
    timeFullStart = timenow();
    ……………………………
    while select  ledgerTrans
            join  ledgerTable
            where ledgerTrans.AccountNum == ledgerTable.AccountNum
               && ledgerTrans.TransDate >= str2date('dd.mm.yy',123) && ledgerTrans.TransDate <= str2date('DD.MM.YY',123)
    {
            // 10 полей
            ledgerTrans.RecId  --- >  в Excel, столбец 1
            ledgerTrans.AccountNum --- >  в Excel, столбец 2
            ledgerTable.AccountName --- >  в Excel, столбец 3
            ledgerTable.AccountPlType  --- >  в Excel, столбец 4   
            ledgerTrans.BondBatchTrans_RU ) --- >  в Excel, столбец 5
            ledgerTrans.BondBatch_RU  --- >  в Excel, столбец 6
            ledgerTrans.TransDate  --- >  в Excel, столбец 7
            ledgerTrans.Txt  --- >  в Excel, столбец 8
            ledgerTrans.AmountMST  --- >  в Excel, столбец 9
            ledgerTrans.Crediting  --- >  в Excel, столбец 10
    }
    ……………………………
    timeFullFinish = timenow();
    timeFullTotal = timeFullFinish - timeFullStart;
    info('Время выполнения, сек');
    info(int2str(timeFullTotal));

4. Конкретная реализация алгоритма выгрузки в Excel – естественно, ваша. Куда данные пойдут из цикла – скажем, сразу напрямую в Excel или сначала во временную таблицу – тоже ваша забота.

5. Кол-во выгружаемых записей неявно «задается» путем выполненного заранее подбора начальной и конечной дат в запросе: dd.mm.yy и DD.MM.YY – вы просто подбираете себе такой конкретный диапазон дат, который бы возвращал из вашего LedgerTrans-a примерно 50000 записей (плюс-минус 2-3 тысячи – не вопрос!). Поскольку бизнесы у всех разные, то и разные по жизни темпы наполнения LedgerTrans-a. Про ваши конкретные даты вы, естественно, никому из нас не рассказываете (чтобы шпиёны не могли оценить интенсивность вашей бухгалтерии J) – если только сами не захотите.

6. Рассматриваем только выгрузку средствами самой Аксапты. Доступ напрямую к таблицам БД средствами СУБД (MS SQL Server или Oracle), минуя Аксапту – не рассматриваем.

7. Данные выводим в новую книгу Excel без форматирования, только строка заголовка и дальше данные (начиная с ячейки A2 J). Моментом окончанием исследуемого процесса считается появление всех данных в открытом окне Excel. Время последующего сохранения этой новой книги на диске не учитываем. Если же алгоритм построен так, что данные сначала выводятся на диск (например, как текстовый файл), то этот файл следует открыть и «предъявить» в открытом окне Excel – это время уже учитываем!

Какие результаты интересны? (обсуждаемо):

1. Общее время процесса, в секундах – от момента запуска метода до момента появления всех данных в окне Excel.
2. Время непосредственного вывода в Excel, в секундах - если есть возможность подсчитать время непосредственного вывода в Excel, отделив его от времени выборки данных из таблиц в цикле, то просьба указать это время.
3. Фактическое кол-во выведенных записей (с точностью до 100 вполне достаточно).
4. Версия Excel – 97, 2000, 2002, 2003 (в способе, который буду иллюстрировать я, это оказалось существенным параметром)
5. Версия Axapta.
6. Характеристики компьютера: частота процессора, размер оперативной памяти, «реальная тачка или виртуальная»… что еще?
7. Характеристики локальной сети – пропускная способность… что еще?
8. Конечно, любые ваши дополнительные комментарии.
9. Что еще?

Ну что, изобразим?

Заранее спасибо всем, кто откликнется. Пошёл готовиться и сам…

P.S. Все условия эксперимента обсуждаемы, включая запрос, таблицы, кол-во записей и полей.

[mazzy]

Цитата:

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

1. Выводим в Excel данные из двух связанных таблиц: LedgerTrans и LedgerTable.

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

Иначе самый быстрый вариант - передать в Excel запрос в инструмент Excel \ Data \ Import External Data \ New Database Query (спасибо Константину, который подсказал этот способ)

Проблемы начинаются, если в Excel'е нужно отобразить данные, которые вычисляются на X++ в коде Аксапты...

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

[gl00mie]

Цитата:

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

самый быстрый вариант - передать в Excel запрос в инструмент Excel \ Data \ Import External Data \ New Database Query

Разве этот вариант согласуется с этим пунктом условий?

Цитата:

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

6. Рассматриваем только выгрузку средствами самой Аксапты. Доступ напрямую к таблицам БД средствами СУБД (MS SQL Server или Oracle), минуя Аксапту – не рассматриваем.

[mazzy]

Цитата:

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

Разве этот вариант согласуется с этим пунктом условий?

ок. согласен, пропустил.

[Gustav]

"Раз уж заговорили об этом..."

Может, кому пригодится. Привожу фрагменты из рабочего кода на VBA, включающего в себя алгоритм перехода на следующий лист. Это НЕРАБОЧАЯ процедура, т.е. она взята из моего работающего приложения и из нее удалены отдельные фрагменты. Поэтому не пытайтесь запускать ее в Excel в том виде, в каком она здесь представлена. Однако, список переменных процедуры сохранен полностью. Не пытайтесь его понять полностью - там много ненужного, т.е. не имеющего отношения к переходу на след.лист. В принципе там всё несложно, другое дело, что часто такие вещи бывает делать лениво и они всё откладываются, откладываются...

Словом, если есть желание - воспользуйтесь. Если будут вопросы - с удовольствием отвечу.

'Код - Excel VBA

'ВНИМАНИЕ: запускать не надо, он все равно не запустится!
'А поизучать можно :)
'Может, кому-нибудь пригодится.

Option Explicit

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset

Const MaxRowsPerSheet As Long = 65000 'максимальное количество, выводимое на один лист

Sub GenerateReport()
'эта процедура запускается, когда пользователь жмет на листе кнопку "Создать файл отчета"
'т.е. с нее всё и начинается
    Dim Id As Long
    Dim Rep_Id As Long
    Dim NewFile As Workbook
    Dim Res As Object
    Dim stmt As String
    Dim Title As String
    Dim flds() As Object
    Dim fldsNames() As String 'массив заголовков колонок
    Dim fldcount As Long
    Dim Colnum As Long
    Dim Rownum As Long 'строка Excel
    Dim HeaderRows As Long 'количество строк заголовков перед данными (2 штуки)
    
    Dim dtmProcStart As Date
    Dim lngProcSeconds As Long
    Dim strProcInfo As String
    
    Dim dtmQueryStart As Date
    Dim lngQuerySeconds As Long
    Dim strQueryInfo As String
    
    Dim dtmOutputStart As Date
    Dim lngOutputSeconds As Long
    Dim strOutputInfo As String
    
    Dim TitleOfPart As String
    
    Dim intOutputKind As Integer 'Вариант вывода: 1 - Традиционный, 2 - Быстрый
    Dim Recordnum As Long 'сквозной счетчик записей через все листы
    
    Dim func_needed As Integer
    Dim func_len As Long
    Dim func_name As String
    
    Dim rng As Range
    
    Dim actSheet As Worksheet
    Dim intSheetsCounter As Integer
        
        
    dtmOutputStart = Now
    Debug.Print "Начало вывода результатов: " & dtmOutputStart
    
    Set NewFile = Application.Workbooks.Add
    
    Application.ScreenUpdating = False
    
    intSheetsCounter = 1
    Set actSheet = NewFile.Worksheets(intSheetsCounter)
    actSheet.Select
    
    HeaderRows = 2
    
    Rownum = HeaderRows
    Recordnum = 0
    
    lngQuerySeconds = DateDiff("s", dtmQueryStart, Now)
    strQueryInfo = "Запрос был выполнен за " & CStr(lngQuerySeconds) & " сек (" & CStr(fldcount) & " полей). "
    Application.StatusBar = strQueryInfo & strProcInfo
    Debug.Print strProcInfo & strQueryInfo
    
    'собственно главный цикл вывода результатов
    '------------------------------------------------------------------------------------------------------------------
    Select Case intOutputKind
        Case 1
            '--- ORA
            Do While Not EmpDynaset.EOF
            
                Rownum = Rownum + 1
                Recordnum = Recordnum + 1
                
                For Colnum = 0 To fldcount - 1
                    actSheet.Cells(Rownum, Colnum + 1) = flds(Colnum).Value
                Next Colnum
                
                If (Recordnum Mod 100) = 0 Then
                    GoSub Every100rows
                End If
                
                EmpDynaset.DbMoveNext
            Loop
            
        Case 2
            '--- ADO
            Do While Not rst.EOF
            
                Recordnum = Recordnum + 100
                
                Set rng = actSheet.Cells(Rownum + 1, 1)
                rng.CopyFromRecordset Data:=rst, MaxRows:=100
                
                Rownum = Rownum + 100
                
                GoSub Every100rows
            Loop
            
    End Select
    
    '------------------------------------------------------------------------------------------------------------------
    
    Select Case intOutputKind
        Case 1
            '--- ORA
            Recordnum = EmpDynaset.RecordCount
            EmpDynaset.Close
            Set EmpDynaset = Nothing
        Case 2
            '--- ADO
            'rst.RecordCount - данный провайдер MSDAORA возвращает -1 для любого типа курсора, поэтому извращаемся на последнем листе
            Recordnum = (NewFile.Worksheets(intSheetsCounter).Range("A1").SpecialCells(xlCellTypeLastCell).Row - HeaderRows) _
                        + (intSheetsCounter - 1) * MaxRowsPerSheet
            rst.Close
            Set rst = Nothing
    End Select
    
    'форматирование последнего (или единственного) листа
    If intSheetsCounter > 1 Then
        TitleOfPart = "Ч." & CStr(intSheetsCounter) & ". " & Title
    Else
        TitleOfPart = Title
    End If
    
    Rownum = (Recordnum Mod MaxRowsPerSheet) + HeaderRows
    
    Call FormatResults(actSheet, TitleOfPart, fldcount, fldsNames, Rownum)
    
    strOutputInfo = "Вывод " & CStr(Recordnum) & " строк за " & CStr(lngOutputSeconds) & " сек. "
    Application.StatusBar = strOutputInfo & strQueryInfo & strProcInfo
    Debug.Print strProcInfo & strQueryInfo & strOutputInfo
    
'-- сделать возможность запуска того же запроса -- с дефолтно выключенной опцией "с теми же параметрами" -- а то упарился при тестировании
    

    Application.StatusBar = False
    Application.ScreenUpdating = True
    
    With NewFile.Worksheets(1)
        .Select
        .Range("A1").Select
    End With
    
    Exit Sub
    
Every100rows:
    'фрагмент вынесен в подпрограмму внутри процедуры - чтобы не создавать отдельную абстрактную процедуру
    'хоть это и ругаемый устаревший синтаксис, зато, блин, получилось весьма удобно :)))
    
    'каждые 100 строк обновляем StatusBar
    lngOutputSeconds = DateDiff("s", dtmOutputStart, Now)
    strOutputInfo = "Вывод " & CStr(Recordnum) & " строк за " & CStr(lngOutputSeconds) & " сек. "
    Application.StatusBar = strOutputInfo & strQueryInfo & strProcInfo
    
    If (Recordnum Mod MaxRowsPerSheet) = 0 Then
    'каждые 65000 строк переходим на след.лист
        'если сюда попали, то листов у нас точно больше одного
        
        'форматирование только что заполненного листа
        TitleOfPart = "Ч." & CStr(intSheetsCounter) & ". " & Title
        Call FormatResults(actSheet, TitleOfPart, fldcount, fldsNames, Rownum)
        
        If intSheetsCounter = 1 Then
            actSheet.Name = "Part_" & CStr(intSheetsCounter) 'переименовываем только что заполенный лист
        End If
        
        intSheetsCounter = intSheetsCounter + 1
        
        If intSheetsCounter > NewFile.Worksheets.Count Then
            'если листов не хватает, то добавляем в конец
            NewFile.Worksheets.Add.Move after:=NewFile.Worksheets(NewFile.Worksheets.Count)
        End If
        
        Set actSheet = NewFile.Worksheets(intSheetsCounter)
        actSheet.Name = "Part_" & CStr(intSheetsCounter) 'переименовываем вновь добавленный
        actSheet.Select 'это нужно в основном для версии 2 -- вывод через CopyFromRecordset (да и то не сильно обязательно)
        
        Rownum = HeaderRows 'сбрасываем счетчик строк Excel для следующего листа
    End If
    Return
    
End Sub

[Gustav]

Цитата:

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

Excel \ Data \ Import External Data \ New Database Query

Можно считать, что этот способ отклоняется пунктом "6. Рассматриваем только выгрузку средствами самой Аксапты. Доступ напрямую к таблицам БД средствами СУБД (MS SQL Server или Oracle), минуя Аксапту – не рассматриваем."

Способы доступа к таблицам Аксапты внешними средствами безусловно интересны и, как правило, быстры. За исключением одного НО - они плюют на рогатки безопасности Аксапты, в частности, на RLS, и предъявляют все данные запроса без учета профиля пользователя, их запрашивающего. Недавно как раз этот момент обсуждался здесь: Альтернативные конструкторы отчетов в Axapta

Цитата:

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

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

Должен переходить на следующий лист Excel. Я уже выводил и 500 тысяч, и больше. Но в "Условиях конкурса" мы говорим о 50 тыс.
К тому же, тестируется скорость вывода. Скорость же "переворачивания" алгоритмом очередного эксельного шита пренебрежима мала

Раз уж заговорили об этом, процитирую себя, любимого, из письма к своим благодарным пользователям (речь о неком местном эксельном репортере):

Цитата:

Данная версия Репортера приспособлена для вывода больших
выборок (свыше 65 тыс.строк) на несколько листов рабочей
книги Excel. Каждый очередной переход на следующий лист
происходит автоматически по достижении очередных 65 тыс.
Нужное количество листов также автоматически добавляется.

Не стоит, однако, увлекаться слишком большими объемами
выводимых в отчет данных, т. к. могут возникнуть ограничения
со стороны оперативной памяти Вашего компьютера
и размера получающегося файла Excel, причем, даже если
Вам удастся сохранить такой огромный файл Excel
(много листов по 65 тыс.строк), в будущем Вы можете просто
его не открыть.

Цитата:

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

Разве этот вариант согласуется с этим пунктом условий?

и я про то же

[AndyD]

Первый результат (предварительный)

Ч/з xml - время 56 с (просто пробег по данным занимает 38 с)
Клиент, AOS, сервер БД на одной машине
Процессор - Athlon 2500+, память - 500Мб
Кол-во записей == 50000
Excel XP

Завтра попробую на другой конфигурации (сетевой)

[belugin]

>>>Ч/з xml - время 56 с

Это как конкретно?
Чем формируется XML - AsciiIO или XMLDocument?
Что за XML формируется SpreadSheetML или данные, которые затем как-то (как?) переписываются в лист?

[AndyD]

Новый результат

Опять же ч/з xml (без использования XMLDocument)
Два режима - выгрузка в файл с последующим открытием в Excel и прямая загрузка ч/з COM-интерфейс. В первом случае скорость - 26 с, во втором - 29-30 с
Скорость выборки данных - 19 с
Клиент на отдельной машине, AOS и сервер БД - на отдельной
Конфигурация клиента - Athlon 64 3000+, 1Гб памяти
Кол-во записей == 50000
Сеть - 100Мб, сервер и клиент в одном сегменте
Excel 2003

В кач-ве практически бесплатного бонуса (по скорости) - заодно рисую сетку и устанавливаю фон (ч/з строку)

Тестовый джоб выложу позже

[Gustav]

Цитата:

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

В первом случае скорость - 26 с, во втором - 29-30 с
Скорость выборки данных - 19 с

19 c это внутри общих 26 с ? или общее = 19 с + 26 с ?

В любом случае - круто! Уже большое спасибо!

[AndyD]

26 (29) с - это то, что получаю в инфо на выходе (общее время выполнения джоба, все действия находятся внутри счетчика времени, в том числе инициализация всех используемых классов)

[AndyD]

Опробовал еще на одной машине
Конфигурация клиента - Celeron 2800, 256Мб
Сеть - сервер б/д тот-же, что и в предыдущем случае, но подключение ч/з 3 хаба.
Excel 2003

Время через файл 40-45 с, напрямую в Excel - 35 c

Почему через файл дольше - на мой взгляд из-за версии XML (на первой машине XML 6.0) и способа выгрузки во втором случае - передаются не все данные сразу, а по несколько тысяч строк. По всей видимости, XML 4 хуже работает с большими по размеру документами (в данном случае - больше 30 Мб)

Ну и в завершение - тестовый джоб

[gefr]

Цитата:

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

Опробовал еще на одной машине
Конфигурация клиента - Celeron 2800, 256Мб
...
Ну и в завершение - тестовый джоб

Автору - респект за Job.
Нам очень понадобилось, поэтому переделал в класс.
Собственно, выкладываю класс.
В нем из любого места можно вызвать - просто посмотреть в коде пример использования - это ExXML::Test().

[longson]

Цитата:

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

Опробовал еще на одной машине
Конфигурация клиента - Celeron 2800, 256Мб
Сеть - сервер б/д тот-же, что и в предыдущем случае, но подключение ч/з 3 хаба.
Excel 2003

Время через файл 40-45 с, напрямую в Excel - 35 c

Почему через файл дольше - на мой взгляд из-за версии XML (на первой машине XML 6.0) и способа выгрузки во втором случае - передаются не все данные сразу, а по несколько тысяч строк. По всей видимости, XML 4 хуже работает с большими по размеру документами (в данном случае - больше 30 Мб)

Ну и в завершение - тестовый джоб

\

У меня при использовании этого механизма для выгрузки в Excel, если количество полей больше определенного порога, выдается ошибка

 "value"  COM-  "Range"    0x800A03EC (<>),  : <>.

Никто не сталкивался с этой ошибкой?

[kaw]

я ищу причину ошибки:

Цитата:

Метод "item" в COM-объекте класса "Range" возвратил код ошибки 0x800A03EC (<неизвестно>), который означает: <неизвестно>.

в ходе поиска получил близкую ошибку:

Цитата:

Метод "value" в COM-объекте класса "Range" возвратил код ошибки 0x80004005 (E_FAIL), который означает: Общий сбой.

Ошибка появляется в момент, если в тег с типом Number выводить число "1 234.00", т.е. нужно убрать пробел "1234.00"

Именно этот job на больших данных мне конкретно пишет:

Цитата:

Ошибка времени выполнения: Недостаточно памяти для исполнения кода.

Другие варианты вывода в xml большого кол-ва отвечали:

Цитата:

Exception of type `SystemOutMemoryException` was thrown

Пытался добиться своей ошибки выводом в xml строк больше1 048 576
но система спокойно выводит последнюю строку, а остальные "скромно" пропускает.

[belugin]

блин для кого я писал про RawStrings

b =                                                          
    "<?xml version=\"1.0\" encoding=\"WINDOWS-1251\"?>\n" +  
    "<?mso-application progid=\"Excel.Sheet\"?>\n" +

b =                                                          
    @'<?xml version="1.0" encoding="WINDOWS-1251"?> 
        <?mso-application progid="Excel.Sheet"?>

пофиксите xpp-расцветчик, пожалуйста

[belugin]

еще надо искейпить спецсимволы - это тоже может есть время

[AndyD]

Казнить нельзя помиловать.
Из C'ев я вышел

[Recoilme]

Цитата:

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

Уважаемые коллеги,


P.S. Все условия эксперимента обсуждаемы, включая запрос, таблицы, кол-во записей и полей.

У меня лиджертранс пустой

[Gustav]

Цитата:

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

У меня лиджертранс пустой

Вау! Такое бывает?? А что не пустое? Какой-нибудь другой транс есть непустой? InventTrans, например?