Как быстро выгрузить большой объем данных из PostgreSQL в таблицу значений 1С без конфигуратора?

Программист 1С v8.3 (Управляемые формы) IT и автоматизация бизнеса
← На главную

При работе с внешними базами данных, такими как PostgreSQL, разработчики 1С часто сталкиваются с необходимостью интеграции "на лету". Представим типичную ситуацию: перед нами стоит задача разработать внешнюю обработку (без возможности вносить изменения в конфигурацию и использовать механизм «Внешние источники данных»), которая должна забирать сотни тысяч строк из PostgreSQL и помещать их в Таблицу Значений 1С для последующей обработки.

Первое, что приходит на ум — использовать стандартное COM-соединение через ADODB.Connection и классический построчный обход выборки:


Пока Выборка.EOF = Ложь Цикл
    // Заполняем строку нашей Таблицы Значений данными из полей выборки
    Выборка.MoveNext();
КонецЦикла;

Однако при попытке обработать таким образом 100 000 строк система начинает безнадежно зависать. Выясним причину этой проблемы. Основная беда здесь кроется в огромных накладных расходах (overhead) на преодоление COM-границы между платформой 1С и библиотекой ADO. При получении каждого значения поля и при каждом вызове метода MoveNext() происходит маршалинг данных через COM-интерфейс. Если в таблице 10 колонок и 100 000 строк, платформа совершит более 1 000 000 COM-вызовов! Это колоссальные затраты ресурсов процессора и оперативной памяти.

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

Решение 1. Использование метода GetRows() и COMSafeArray

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

Метод GetRows() выгружает все записи объекта Recordset в двумерный массив COMSafeArray. После этого работа с массивом происходит в оперативной памяти силами самой платформы 1С, что выполняется практически мгновенно.

Разберем по шагам, как реализовать этот алгоритм на практике:


// 1. Инициализируем COM-соединение с PostgreSQL
Соединение = Новый COMОбъект("ADODB.Connection");
Соединение.ConnectionString = "Driver={PostgreSQL Unicode};Server=my_server;Port=5432;Database=my_db;Uid=postgres;Pwd=my_password;";

// КРИТИЧЕСКИ ВАЖНО: устанавливаем клиентский курсор (adUseClient = 3)
// Без этой настройки ADO будет подгружать данные лениво, и выигрыша в скорости не будет
Соединение.CursorLocation = 3; 
Соединение.Open();

Рекордсет = Новый COMОбъект("ADODB.Recordset");
ТекстЗапроса = "SELECT id, code, name, quantity FROM sales_data";
Рекордсет.Open(ТекстЗапроса, Соединение);

Если Не Рекордсет.EOF Тогда
    // Получаем данные в виде двумерного SafeArray
    ДанныеSafeArray = Рекордсет.GetRows();
    
    // Выясняем размерность полученного массива
    // Первое измерение (0) — это колонки, второе (1) — строки
    ВторичныйИндексКолонок = ДанныеSafeArray.UpperBound(0);
    ВторичныйИндексСтрок = ДанныеSafeArray.UpperBound(1);
    
    // Создаем результирующую Таблицу Значений в 1С
    ТаблицаРезультат = Новый ТаблицаЗначений;
    Для НомерКолонки = 0 По ВторичныйИндексКолонок Цикл
        ИмяПоля = Рекордсет.Fields(НомерКолонки).Name;
        ТаблицаРезультат.Колонки.Добавить(ИмяПоля);
    КонецЦикла;
    
    // Наполняем таблицу значений данными из SafeArray в памяти 1С
    Для ИндексСтроки = 0 По ВторичныйИндексСтрок Цикл
        НоваяСтрока = ТаблицаРезультат.Добавить();
        Для ИндексКолонки = 0 По ВторичныйИндексКолонок Цикл
            // Используем метод GetValue для извлечения данных из многомерного SafeArray
            НоваяСтрока[ИндексКолонки] = ДанныеSafeArray.GetValue(ИндексКолонки, ИндексСтроки);
        КонецЦикла;
    КонецЦикла;
КонецЕсли;

Рекордсет.Close();
Соединение.Close();

Благодаря этому подходу мы совершаем всего один ресурсоемкий COM-вызов для получения всех данных, а последующий цикл по заполнению таблицы значений выполняется нативным кодом 1С в оперативной памяти.

Решение 2. Формирование JSON на стороне PostgreSQL (Самый быстрый способ)

Если на вашем сервере установлен современный PostgreSQL, то мы можем полностью переложить задачу сериализации данных на саму СУБД. Postgres обладает потрясающими возможностями для работы с JSON. Мы можем заставить его упаковать весь результат запроса в одну большую JSON-строку.

Посмотрим на пример реализации этого элегантного подхода. Мы обернем наш SQL-запрос в агрегирующую функцию json_agg():


SELECT json_agg(t) FROM (SELECT id, code, name, quantity FROM sales_data) t;

СУБД выполнит запрос, сформирует JSON и вернет нам ровно одну строку с одним полем. Нам останется лишь прочитать эту строку в 1С с помощью высокооптимизированного встроенного механизма JSON.

Пример кода на стороне 1С:


// Настройка соединения и выполнение запроса с JSON-агрегацией
Соединение = Новый COMОбъект("ADODB.Connection");
Соединение.Open("Driver={PostgreSQL Unicode};Server=my_server;Database=my_db;Uid=postgres;Pwd=my_password;");

ТекстЗапроса = "SELECT json_agg(t) FROM (SELECT id, code, name, quantity FROM sales_data) t";
Рекордсет = Соединение.Execute(ТекстЗапроса);

Если Не Рекордсет.EOF Тогда
    // Извлекаем гигантскую JSON-строку из единственного поля рекордсета
    СтрокаJSON = Рекордсет.Fields(0).Value;
    
    // Парсим JSON силами платформы 1С
    Чтение = Новый ЧтениеJSON;
    Чтение.УстановитьСтроку(СтрокаJSON);
    МассивСтруктур = ПрочитатьJSON(Чтение);
    Чтение.Закрыть();
    
    // Теперь быстро переносим массив структур в Таблицу Значений
    ТаблицаРезультат = Новый ТаблицаЗначений;
    Если МассивСтруктур.Количество() > 0 Тогда
        // Создаем колонки на основе ключей первой структуры
        Для Каждого КлючИЗначение Из МассивСтруктур[0] Цикл
            ТаблицаРезультат.Колонки.Добавить(КлючИЗначение.Ключ);
        КонецЦикла;
        
        // Наполняем таблицу значений
        Для Каждого СтрокаДанных Из МассивСтруктур Цикл
            ЗаполнитьЗначенияСвойств(ТаблицаРезультат.Добавить(), СтрокаДанных);
        КонецЦикла;
    КонецЕсли;
КонецЕсли;

Рекордсет.Close();
Соединение.Close();

Этот способ показывает феноменальные результаты скорости работы, поскольку парсинг JSON в платформе 1С написан на низкоуровневом C++ и оптимизирован под работу с большими объемами данных.

Решение 3. Выгрузка Recordset в XML-поток

Еще один классический и надежный способ обойти медленный построчный обход — это использование встроенной в ADO сериализации данных в формат XML. Объект Recordset умеет сохранять свое состояние в XML-структуру.

Чтобы избежать дисковых операций ввода-вывода (которые могут замедлить процесс), мы задействуем COM-объект ADODB.Stream для сохранения XML прямо в оперативную память.

Проанализируем ситуацию на примере следующего программного кода:


Соединение = Новый COMОбъект("ADODB.Connection");
Соединение.Open("Driver={PostgreSQL Unicode};Server=my_server;Database=my_db;Uid=postgres;Pwd=my_password;");

Рекордсет = Новый COMОбъект("ADODB.Recordset");
Рекордсет.CursorLocation = 3;
Рекордсет.Open("SELECT id, code, name FROM sales_data", Соединение);

// Создаем поток ADO в оперативной памяти
Поток = Новый COMОбъект("ADODB.Stream");
Поток.Type = 1; // adTypeBinary (бинарный тип)
Поток.Open();

// Сохраняем рекордсет в поток в формате XML (1 = adPersistXML)
Рекордсет.Save(Поток, 1);

// Перематываем поток в начало и считываем текстовое представление XML
Поток.Position = 0;
Поток.Type = 2; // Переключаем на текстовый режим
Поток.Charset = "utf-8";
ТекстXML = Поток.ReadText(-1); // -1 означает прочитать весь поток

Поток.Close();
Рекордсет.Close();
Соединение.Close();

// Читаем полученный XML встроенными средствами 1С
ЧтениеXML = Новый ЧтениеXML;
ЧтениеXML.УстановитьСтроку(ТекстXML);

ТаблицаРезультат = Новый ТаблицаЗначений;
// Здесь мы можем использовать ПостроительЗапроса или ручной разбор узлов ЧтениеXML 
// для быстрого заполнения Таблицы Значений.

Механизм ЧтениеXML в 1С также реализован на уровне платформы и работает очень шустро, избавляя нас от необходимости вызывать COM-методы для каждой строки базы данных.

Решение 4. Использование команды COPY TO (выгрузка в CSV)

Если у пользователя базы данных PostgreSQL достаточно прав и есть доступ к общей сетевой папке (или папка находится непосредственно на сервере СУБД), можно использовать встроенную утилиту Postgres для высокоскоростного экспорта — команду COPY.

Суть метода заключается в следующем:

  1. Мы посылаем через ADO команду на экспорт таблицы или запроса во временный файл CSV на диске.
  2. Платформа 1С считывает этот CSV-файл напрямую средствами встроенного объекта ЧтениеТекста или специализированных функций для парсинга CSV.

Пример SQL-команды для экспорта:


COPY (SELECT id, code, name FROM sales_data) TO '/tmp/sales_export.csv' WITH CSV HEADER DELIMITER ',';

Затем в 1С мы открываем этот файл:


Текст = Новый ЧтениеТекста("/tmp/sales_export.csv", КодировкаТекста.UTF8);
СтрокаДанных = Текст.ПрочитатьСтроку(); // Считываем заголовок
// Далее разбираем строки и наполняем Таблицу Значений

Чтение CSV в 1С происходит в сотни раз быстрее, чем построчный перебор рекордсета через COM-соединение.

Резюме и рекомендации

Мы рассмотрели несколько эффективных инструментов оптимизации обмена данными между 1С и PostgreSQL. Выбор конкретного метода зависит от ваших условий:

Применяя данные подходы, вы сможете ускорить обработку больших объемов данных во внешних обработках 1С, сохранив стабильность работы системы и избавив пользователей от утомительного ожидания.

← На главную