В процессе разработки на платформе 1С:Предприятие часто возникает задача консолидации данных из множества однотипных источников. Например, когда в цикле обхода документов, файлов или внешних систем формируются отдельные таблицы значений, которые в итоге необходимо слить в одну большую таблицу для последующего вывода или анализа.
При попытке решить эту задачу «в лоб» разработчики часто сталкиваются с дилеммой: передать все эти таблицы в один гигантский пакетный запрос и объединить их средствами СУБД с помощью оператора ОБЪЕДИНИТЬ ВСЕ, либо выполнить слияние программно средствами встроенного языка 1С (BSL). В этой статье мы подробно разберем оба подхода, проанализируем их производительность, выявим скрытые угрозы для серверной инфраструктуры и рассмотрим высокопроизводительные альтернативные алгоритмы, которые сделают вашу работу с данными максимально быстрой и стабильной.
Начнем с классической ошибки, с которой столкнулся автор темы. Попытка выполнить запрос, напрямую объединяющий таблицы значений, переданные в качестве параметров, вызывает исключение платформы: "Содержимое объекта данных может быть выбрано только во временную таблицу".
Рассмотрим некорректный код запроса:
ВЫБРАТЬ
ТЗ.Элемент1 КАК Элемент1,
ТЗ.Элемент2 КАК Элемент2
ИЗ
&ТЗ КАК ТЗ
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
ТЗ2.Элемент1,
ТЗ2.Элемент2
ИЗ
&ТЗ2 КАК ТЗ2
Выясним причину этой ошибки: Платформа 1С не умеет выполнять реляционные операции (такие как соединение или объединение) непосредственно над объектами оперативной памяти на стороне СУБД. Чтобы СУБД (например, MS SQL Server или PostgreSQL) могла обработать внешние данные, эти данные сначала должны быть физически записаны в СУБД. Единственный способ сделать это в запросе — предварительно поместить каждую таблицу значений во временную таблицу с помощью ключевого слова ПОМЕСТИТЬ.
Только после того, как данные будут записаны в базу данных (в системную базу tempdb для MS SQL), СУБД сможет выполнить над ними операцию ОБЪЕДИНИТЬ.
Разберем по шагам решение, к которому пришел автор темы в сообщении (25). Он обходит исходную коллекцию параметров в цикле, динамически формируя две части текста запроса: первую часть (помещение каждой таблицы во временную таблицу) и вторую часть (финальное объединение всех этих временных таблиц).
Посмотрим на практический пример реализации такого генератора запросов:
ТекстЗапросаПомещения = "";
ТекстЗапросаОбъединения = "";
Счетчик = 0;
Для Каждого СтрокаПараметра Из Параметры Цикл
ТекущаяТЗ = ПолучитьТаблицуИзПараметра(СтрокаПараметра);
Если ТекущаяТЗ = Неопределено Или ТекущаяТЗ.Количество() = 0 Тогда
Продолжить;
КонецЕсли;
Счетчик = Счетчик + 1;
ИмяВТ = "ТЗ_" + Счетчик;
// Формируем пакет помещения во временную таблицу
ТекстЗапросаПомещения = ТекстЗапросаПомещения +
"ВЫБРАТЬ ТЗ.Номенклатура, ТЗ.Склад ПОМЕСТИТЬ " + ИмяВТ + " ИЗ &" + ИмяВТ + " КАК ТЗ; ";
// Формируем секцию объединения
Если Счетчик > 1 Тогда
ТекстЗапросаОбъединения = ТекстЗапросаОбъединения + " ОБЪЕДИНИТЬ ВСЕ ";
КонецЕсли;
ТекстЗапросаОбъединения = ТекстЗапросаОбъединения +
"ВЫБРАТЬ Номенклатура, Склад ИЗ " + ИмяВТ;
Запрос.УстановитьПараметр(ИмяВТ, ТекущаяТЗ);
КонецЦикла;
Если Счетчик > 0 Тогда
Запрос.Текст = ТекстЗапросаПомещения + " //////////////////////////////////////////////////////////////////////////////// " + ТекстЗапросаОбъединения;
Результат = Запрос.Выполнить().Выгрузить();
КонецЕсли;
На первый взгляд, этот код решает задачу: он отрабатывает без ошибок и возвращает одну общую таблицу значений. Однако этот подход скрывает в себе серьезные проблемы производительности:
ОБЪЕДИНИТЬ ВСЕ, порождает дерево плана гигантского масштаба. Процесс rphost сервера 1С и сервер СУБД потратят огромное количество процессорного времени и оперативной памяти только на компиляцию этого плана, что нередко приводит к зависанию сеанса или падению рабочих процессов по таймауту.Вывод: Динамическое объединение сотен временных таблиц через запрос — это крайне неэффективное решение, использовать которое в рабочих высоконагруженных базах данных категорически не рекомендуется.
Гораздо более правильным и быстрым подходом является объединение таблиц непосредственно в оперативной памяти сервера приложений 1С. Рассмотрим несколько практических вариантов реализации этого подхода.
Самый простой и очевидный способ программного слияния — последовательный перенос строк в цикле с использованием системной процедуры ЗаполнитьЗначенияСвойств.
// Создаем результирующую таблицу-приемник с нужной структурой
ТаблицаПриемник = ИсходныеТаблицы[0].СкопироватьКолонки();
Для Каждого ТекущаяТаблица Из ИсходныеТаблицы Цикл
Для Каждого СтрокаИсточника Из ТекущаяТаблица Цикл
НоваяСтрока = ТаблицаПриемник.Добавить();
ЗаполнитьЗначенияСвойств(НоваяСтрока, СтрокаИсточника);
КонецЦикла;
КонецЦикла;
Проанализируем этот метод: он максимально прост в написании, универсален (не требует жесткого перечисления колонок) и стабилен. Однако для сотен тысяч строк он работает медленно, так как интерпретатор 1С выполняет динамический поиск соответствий имен свойств для каждой строки на каждой итерации цикла.
Если в вашей конфигурации установлена Библиотека Стандартных Подсистем (БСП), вам не нужно писать велосипеды. В БСП присутствует готовый, оптимизированный метод ОбщегоНазначенияКлиентСервер.ДополнитьТаблицу(). Посмотрим на пример его использования:
ТаблицаПриемник = ИсходныеТаблицы[0].СкопироватьКолонки();
Для Каждого ТекущаяТаблица Из ИсходныеТаблицы Цикл
ОбщегоНазначенияКлиентСервер.ДополнитьТаблицу(ТекущаяТаблица, ТаблицаПриемник);
КонецЦикла;
Этот метод работает быстрее ручного обхода через ЗаполнитьЗначенияСвойств, так как внутри него используются оптимизированные механизмы платформы, разработанные с учетом минимизации накладных расходов.
В сообщении (17) предложен крайне интересный и быстрый способ объединения таблиц значений, который задействует недокументированные возможности оптимизации платформы 1С. Идея заключается в использовании методов НайтиСтроки() и Скопировать().
Метод Скопировать(), принимающий на вход массив строк, написан на низкоуровневом коде платформы (на C++) и работает напрямую с памятью, минуя медленный интерпретатор BSL. Нам нужно лишь быстро собрать строки добавляемой таблицы в массив. Рассмотрим этот алгоритм подробнее:
Функция ПолучитьМассивВсехСтрок(ТаблицаИсточника)
// Добавляем временную уникальную булеву колонку, чтобы легко отобрать все строки
ИмяВремКолонки = "W" + СтрЗаменить(Строка(Новый УникальныйИдентификатор), "-", "");
ТаблицаИсточника.Колонки.Добавить(ИмяВремКолонки, Новый ОписаниеТипов("Булево"));
Отбор = Новый Структура(ИмяВремКолонки, Ложь);
МассивСтрок = ТаблицаИсточника.НайтиСтроки(Отбор);
// Удаляем временную колонку, чтобы вернуть структуру таблицы в исходное состояние
ТаблицаИсточника.Колонки.Удалить(ИмяВремКолонки);
Возврат МассивСтрок;
КонецФункции
Процедура ОбъединитьДвеТаблицыЭффективно(ТаблицаОснова, ТаблицаДобавка)
МассивСтрокОсновы = ПолучитьМассивВсехСтрок(ТаблицаОснова);
Для Каждого СтрокаДобавки Из ТаблицаДобавка Цикл
МассивСтрокОсновы.Добавить(СтрокаДобавки);
КонецЦикла;
// Копирование массива строк выполняется на уровне платформы (C++) мгновенно
ТаблицаОснова = ТаблицаОснова.Скопировать(МассивСтрокОсновы);
КонецПроцедуры
Почему это быстро? Вместо того чтобы построчно создавать новые объекты строк методом Добавить() и копировать значения ячеек, платформа единоразово выделяет блок памяти под новую объединенную таблицу значений и копирует данные на уровне указателей памяти. Этот метод может работать в разы быстрее классического обхода на больших объемах данных.
Рассмотрим ситуации, когда количество объединяемых таблиц исчисляется тысячами, а общий объем данных превышает миллионы строк. В этих случаях даже самые быстрые циклы на встроенном языке могут вызывать задержки. Разберем архитектурные решения этой проблемы.
Если мы последовательно добавляем мелкие таблицы к одной большой результирующей таблице, то с каждым шагом размер результирующей таблицы растет. Операционная система вынуждена постоянно перевыделять все большие и большие блоки непрерывной памяти (memory reallocation) и копировать туда данные. Это создает лавинообразное падение производительности.
Чтобы избежать этого эффекта, применим алгоритм попарного слияния (древовидная схема). Мы будем объединять таблицы парами: первую со второй, третью с четвертой и так далее. На следующем шаге мы объединим полученные результаты. Это позволит сбалансировать нагрузку на менеджер памяти 1С.
Разберем пример реализации попарного объединения:
Функция ОбъединитьМассивТаблицПопарно(МассивТаблиц)
Шаг = 1;
КоличествоТаблиц = МассивТаблиц.Количество();
Если КоличествоТаблиц = 0 Тогда
Возврат Новый ТаблицаЗначений;
КонецЕсли;
Пока КоличествоТаблиц > 1 Цикл
НовыйМассив = Новый Массив;
Индекс = 0;
Пока Индекс < КоличествоТаблиц Цикл
Если Индекс + 1 < КоличествоТаблиц Тогда
// Объединяем пару таблиц с помощью быстрого метода БСП
Таблица1 = МассивТаблиц[Индекс];
Таблица2 = МассивТаблиц[Индекс + 1];
ОбщегоНазначенияКлиентСервер.ДополнитьТаблицу(Таблица2, Таблица1);
НовыйМассив.Добавить(Таблица1);
Иначе
// Если осталась нечетная таблица, просто переносим ее на следующий уровень
НовыйМассив.Добавить(МассивТаблиц[Индекс]);
КонецЕсли;
Индекс = Индекс + 2;
КонецЦикла;
МассивТаблиц = НовыйМассив;
КоличествоТаблиц = МассивТаблиц.Количество();
КонецЦикла;
Возврат МассивТаблиц[0];
КонецФункции
Этот простой алгоритм сокращает сложность операции слияния сотен таблиц с квадратичной $O(N^2)$ до логарифмической $O(N \log N)$, что дает колоссальный прирост скорости на больших массивах.
Один из самых неочевидных, но невероятно мощных инструментов для быстрой манипуляции таблицами значений — это СКД. Система компоновки данных полностью написана на C++ и оптимизирована для сверхбыстрой обработки табличных документов.
Мы можем программно настроить схему компоновки так, чтобы она приняла наши исходные таблицы значений в качестве внешних наборов данных, объединила их с помощью объекта НаборДанныхОбъединение и выгрузила результат в одну результирующую таблицу значений.
Разберем по шагам, как это реализовать программно:
Функция ОбъединитьТаблицыЧерезСКД(МассивТаблиц)
СхемаКомпоновки = Новый СхемаКомпоновки Данных;
// Создаем корневой набор данных объединения
НаборОбъединение = СхемаКомпоновки.НаборыДанных.Добавить(Тип("НаборДанныхОбъединениеСхемыКомпоновкиДанных"));
НаборОбъединение.Имя = "ОбъединениеТаблиц";
ВнешниеНаборыДанных = Новый Структура;
// Получаем структуру колонок на основе первой таблицы
ОбразецТЗ = МассивТаблиц[0];
// Добавляем поля в набор объединения
Для Каждого Колонка Из ОбразецТЗ.Колонки Цикл
ПолеОбъединения = НаборОбъединение.Поля.Добавить(Тип("ПолеНабораДанныхСхемыКомпоновкиДанных"));
ПолеОбъединения.Поле = Колонка.Имя;
ПолеОбъединения.ПутьКДанным = Колонка.Имя;
КонецЦикла;
Счетчик = 0;
Для Каждого ТекущаяТЗ Из МассивТаблиц Цикл
Счетчик = Счетчик + 1;
ИмяНабора = "Набор_" + Счетчик;
// Создаем дочерний набор данных - объект
НаборОбъект = СхемаКомпоновки.НаборыДанных.Добавить(Тип("НаборДанныхОбъектСхемыКомпоновкиДанных"));
НаборОбъект.Имя = ИмяНабора;
НаборОбъект.ИмяОбъекта = ИмяНабора;
НаборОбъект.ИсточникДанных = "ИсточникДанных1"; // Стандартное имя
// Связываем поля дочернего набора с родителем
Для Каждого Колонка Из ОбразецТЗ.Колонки Цикл
ПолеОбъекта = НаборОбъект.Поля.Добавить(Тип("ПолеНабораДанныхСхемыКомпоновкиДанных"));
ПолеОбъекта.Поле = Колонка.Имя;
ПолеОбъекта.ПутьКДанным = Колонка.Имя;
EndDo;
// Помещаем этот набор внутрь нашего объединения
НаборОбъединение.НаборыДанных.Добавить(НаборОбъект);
// Регистрируем внешнюю таблицу значений
ВнешниеНаборыДанных.Вставить(ИмяНабора, ТекущаяТЗ);
КонецЦикла;
// Добавляем единственный источник данных по умолчанию
ИсточникДанных = СхемаКомпоновки.ИсточникиДанных.Добавить();
ИсточникДанных.Имя = "ИсточникДанных1";
ИсточникДанных.ТипИсточникаДанных = "Local";
// Настраиваем вывод всех полей схемы
Настройки = СхемаКомпоновки.НастройкиПоУмолчанию;
Группировка = Настройки.Структура.Добавить(Тип("ГруппировкаКомпоновкиДанных"));
Группировка.Использование = Истина;
Для Каждого Колонка Из ОбразецТЗ.Колонки Цикл
ВыбранноеПоле = Настройки.Выбор.Элементы.Добавить(Тип("АвтоВыбранноеПолеКомпоновкиДанных"));
КонецЦикла;
// Инициализируем компоновщик макета и выполняем компоновку в памяти
КомпоновщикМакета = Новый КомпоновщикМакетаКомпоновкиДанных;
МакетКомпоновки = КомпоновщикМакета.Выполнить(СхемаКомпоновки, Настройки, , , Тип("ГенераторМакетаКомпоновкиДанныхДляКоллекцииЗначений"));
ПроцессорКомпоновки = Новый ПроцессорКомпоновкиДанных;
ПроцессорКомпоновки.Инициализировать(МакетКомпоновки, ВнешниеНаборыДанных);
РезультирующаяТЗ = Новый ТаблицаЗначений;
ПроцессорВывода = Новый ПроцессорВыводаРезультатаКомпоновкиДанныхВКоллекциюЗначений;
ПроцессорВывода.УстановитьОбъект(РезультирующаяТЗ);
ПроцессорВывода.Вывести(ПроцессорКомпоновки);
Возврат РезультирующаяТЗ;
КонецФункции
Преимущества метода СКД:
Подведем итог и определим, какой метод лучше использовать в зависимости от бизнес-задачи:
Применяйте оптимальные методы слияния данных, берегите ресурсы серверов и пишите качественный код, который будет работать быстро даже при многократном росте объемов базы данных!