О (не)быстродействии отчетов и функции CreateArray()

Материал предназначен разработчикам, и его целью является описание вариантов ускорения вывода данных при использовании функции CreateArray() для формирования отчетов, рассмотренных на конкретном примере.

Крупной задачей по одному из проектов было создание комплекта отчетов по разработанным модификациям (в общей сложности – 20 аналитических отчетов, некоторые из которых при этом не сильно отличались друг от друга по своей сути). Обязательными условиями являлись «доступность» и наглядность этих отчетов. Самый наглядный и красивый из них служит для того, чтобы можно было понять, в каком состоянии находится согласование того или иного документа, ну или всех документов сразу.

Пример формы такого отчета (из форматирования – гиперссылка на карточку записи справочника, на один из связанных документов и заливка ячеек, отображающая состояние задания пользователя на момент формирования отчета: 1) выполнено вовремя, 2) не выполнено, но не просрочено, 3) не выполнено и просрочено, 4) задания еще нет, но скоро будет):

Данные в отчет выводились по следующему алгоритму:

1. Создать массив размеренностью <кол-во строк> на <кол-во столбцов> отчета;
2. Заполнить в цикле элементы массива;
3. Вывести данные из массива на лист рабочей книги «одним махом».

Часть листинга расчета отчета:

  ...
  Query = CreateQuery()
  Query.CommandText = ReportQuery
  Query.Open()
  ...
  // Заполнение массива для вывода 
  Arr = CreateArray(0; (n - 1); 0; (ExelColumns - 1))
  i = 0
  while not Query.EOF
    j = 0
    while j < Columns
      Value = Query.FieldByIndex(j).AsString
      // Срок  ожидаемого завершения согласования - сумма всех сроков
      if (j = DAYS_OFFSET_FIELD_INDEX) and Assigned(Value)
        TaskStartDate = Query.Fields("TaskStartDate").AsString
        if Assigned(TaskStartDate) 
          DIRDate = FormatDate("D.M.YY"; TaskStartDate)
          Value = ServiceFactory.GetRelativeDate(DIRDate; Value; dotDays)
          Value = FormatDate("D.M.YY"; Value)
        endif
      endif 
      Arr[i; j] = Value
      j = j + 1 
    endwhile
     
    Query.Next()
    i = i + 1
    Prog.Next()
  Endwhile
  ...
  // Вывод данных на лист
  ... 
  ExApp = CreateObject("Excel.Application")
  ... 
  ReportTable = WorkSheet.Range(ХЛСКолонкаКод(STARTCOLUMN) & STARTROW & ":" & ХЛСКолонкаКод(ENDCOLUMN) & (STARTROW + n - 1))
  ReportTable.Value = Arr
  ...
  ФайлОткрыть(FileName)

Единый вывод данных из массива использовался в целях сокращения времени на заполнение ячеек по сравнению с методом вывода данных, при котором данные записываются в каждую ячейку отдельно (Cell.Value = CellValue)

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

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

Время формирования отчетов на таком количестве данных оказалось неприемлимо долгим, а именно (данные «замера» длительности построения одного из таких отчетов):

После того, как обе стороны согласились с тем, что 14 минут ждать формирования отчета на самом деле очень нехорошо, начали искать причину такой задержки. Оказалось, что дольше всего выполнялось заполнение массива значениями (цикл while not Query.EOF ... endwhile), причем время увеличивалось не просто прямо пропорционально количеству элементов (даже если отбросить используемый в тот момент метод GetRelativeDate()).

Чуть подробнее о длительности заполнения массива (данные по тому же отчету):

В результате расследований выяснилось, что «роковым» для нас оказалось использование функции CreateArray(), а именно: чем больше размеренность массива, тем медленнее идет его заполнение.

Для того, чтобы убедиться в этом, можно использовать следующий сценарий, уменьшая и увеличивая значение переменной XArray (или YArray – важно общее кол-во элементов, а не предел одной из границ):

  XArray = 100
  YArray = 20 // или что-то на выбор
  
  Query = CreateQuery()
  Query.CommandText = "select top " & XArray & " * from MBAnalit"
  Query.Open()
  
  n = Query.RecordCount 
  t1 = Time() 
  Arr = CreateArray(0; (n - 1); 0; (YArray - 1)) 
  t2 = Time() 
  
  i = 0
  while not Query.EOF
    j = 0
    while j < YArray
      Value = Query.FieldByIndex(j).AsString
      Arr[i; j] = Value
      j = j + 1 
    endwhile      
    Query.Next()
    i = i + 1
  endwhile
  t3 = Time()

  EditText(n & ": " & t1 & " - " & t2 & " - " & t3)

Результаты «замеров» работы такого сценария при переменной XArray, равной 100, 200 и 400:

Регресс было очевиден.

Методом подбора были определены границы числа элементов массива, не приводящие к такой временной дыре при его заполнении: ~250-350 элементов.

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

1. Передать приложению Excel объект IQuery (переменная Query в приведенном листинге) и выполнить формирование массива и вывод данных непосредственно на стороне приложения (работа с объектом типа IQuery происходит в автоматически запускаемом макросе, который хранится в шаблоне отчета);
2. Выводить данные не один раз из массива размеренностью (x,y), а x/j кол-во раз из массива с меньшей размеренностью (j,y) (вывод данных – на стороне DIRECTUM).

В первом случае алгоритм получается таким:

1. Формирование SQL-запроса;
2. Создание на его основе объекта IQuery;
3. Запуск макроса с формированием массива и выводом данных в приложении-редакторе.

а код – примерно таким:

  // Код расчета отчета
  ...
  Query = CreateQuery()
  Query.CommandText = ReportQuery
  Query.Open()
  ... 
  ExApp = CreateObject("Excel.Application")
  wb = ExApp.Workbooks.Open(FileName)
  // "FormReportData" – имя макроса 
  ExApp.Application.Run("FormReportData"; Query)
  ...
  ФайлОткрыть(FileName)

  // Код макроса "FormReportData" 
  Public Sub FormReportData (Query)
    
    Dim reportArray As Variant
    ReDim reportArray(Query.RecordCount, Query.FieldCount)
    
    RecordIndex = 0
    Query.First
    While Not Query.EOF
        For i = 0 To (Query.FieldCount - 1)
            reportArray(RecordIndex, i) = Query.FieldByIndex(i).Value
        Next
        RecordIndex = RecordIndex + 1
        Query.Next
    Wend
    ...
    Set ReportTable = ReportSheet.Range(Cells(STARTROW, 1), Cells(Query.RecordCount + STARTROW - 1, Query.FieldCount))
    ReportTable.Value = reportArray

  End Sub

Для второго варианта вывод данных получился следующим:

  ...
  Query = CreateQuery()
  Query.CommandText = ReportQuery
  Query.Open()
  ...
  // Заполнение массива для вывода 
  Columns = Query.FieldCount // в текущем отчете 25 столбцов, т.е. число элементов = 250
  arrayMax = 10    
  Arr = CreateArray(0; (arrayMax - 1); 0; Columns)
  i = 0
  arrayCounter = 0
    
  rowFrom = STARTROW
  ENDCOLUMN = Columns
  while not Query.EOF
    j = 0
    while j < Columns //(Query.FieldCount - 1)
      Value = Query.FieldByIndex(j).AsString
      Arr[i; j] = Value
      j = j + 1 
    endwhile 
   
    if (i = (arrayMax - 1)) or (arrayCounter = (n - 1))
      ReportTable = WorkSheet.Range(ХЛСКолонкаКод(STARTCOLUMN) & (rowFrom) & ":" & ХЛСКолонкаКод(ENDCOLUMN) & (rowFrom + (i)))
      ReportTable.Value = Arr
      i = -1
      rowFrom = rowFrom + arrayMax
    endif
    
    i = i + 1  
    arrayCounter = arrayCounter + 1
    Query.Next()
    Prog.Next()
  endwhile
  ... 
  ExApp = CreateObject("Excel.Application")
  ... 
  ФайлОткрыть(FileName)

Для максимального ускорения все форматирование (в обоих вариантах) было перенесено в отдельный макрос. Однако это потребовало содержания всех необходимых данных в запросе Query, включая, в данном случае, даже адреса гиперссылок для последующего форматирования.

Несмотря на то, что при прочих равных условиях (один и тот же запрос и макрос для форматирования) отчет с выводом данных по варианту №1 (на стороне Excel) отрабатывал быстрее (на 1,7 строк/сек. больше, из расчета кол-во строк/время формирования), в качестве финального варианта был выбран вариант номер два, по следующим причинам:

1. При присвоении значений элементам массива на стороне DIRECTUM остается возможность преобразовывать их, используя объектную модель и функции DIRECTUM;
2. Можно «плавно» передвигать ползунок Progress bar’а, что более комфортно для пользователей (при формировании массива в приложении-редакторе ползунок можно передвинуть только до запуска макроса и после его завершения);
3. Вывод данных осуществляется относительно небольшими кусками, что снижает нагрузку на машину пользователя.

В итоге схемой, устроившей всех, была выбрана следующая:

1. Формирование SQL-запроса, возвращающего по возможности все необходимые данные;
2. Создание массива с числом элементов от 250 до 300;
3. Преобразование, по необходимости, результатов запроса с помощью объектной модели DIRECTUM, запись значения в массив до его заполнения или окончания строк. Вывод данных. Повтор;
4. Запуск макроса для форматирования отчета;
5. Отображение отчета.

Итого: рассматриваемый отчет с количеством строк 2621 стал формироваться за 3 минуты 26 секунд против 14 минут для 1100 строк. Profit!