При разработке информационных систем на платформе 1С:Предприятие часто возникает задача выборки данных из регистров сведений таким образом, чтобы для каждого измерения получить только одну, наиболее приоритетную запись. Например, из регистра, хранящего несколько типов документов для одного и того же физического лица, нам необходимо выбрать только один документ по определенным правилам приоритета (сначала паспорт РФ, затем иностранный паспорт, затем иной документ). Эта задача становится сложной, когда требуется получить такие данные для всех физических лиц в одной таблице, избегая дублирования и обеспечивая однозначность выбора.
Давайте подробно рассмотрим, как мы можем решить эту задачу с использованием многопакетных запросов и временных таблиц в 1С, а также проанализируем альтернативные подходы и рекомендации по оптимизации.
Рассмотрим предложенное решение, которое использует несколько пакетов запроса и временные таблицы для достижения необходимого результата. Основная идея заключается в том, чтобы сначала определить приоритеты для каждого типа документа, затем найти наивысший приоритет для каждого физического лица, а потом использовать эти приоритеты для отбора нужных документов.
Первым делом нам необходимо определить порядок приоритетов для различных типов документов. Для этого мы создаем временную таблицу, в которой каждому типу документа будет сопоставлен числовой приоритет. Чем меньше число, тем выше приоритет.
// Создаем новую таблицу значений для передачи в запрос
ТЗПриоритеты = Новый ТаблицаЗначений;
// Добавляем колонки для типа документа и его приоритета
ТЗПриоритеты.Колонки.Добавить("ТипДокумента", Новый ОписаниеТипов("СправочникСсылка.ТипыДокументов"));
ТЗПриоритеты.Колонки.Добавить("Приоритет", Новый ОписаниеТипов("Число", Новый КвалификаторыЧисла(1, 0, ДопустимыйЗнак.Любой)));
// Добавляем строки с приоритетами
НоваяСтрока = ТЗПриоритеты.Добавить();
НоваяСтрока.Приоритет = 1;
НоваяСтрока.ТипДокумента = Справочники.ТипыДокументов.ПаспортРФ;
НоваяСтрока = ТЗПриоритеты.Добавить();
НоваяСтрока.Приоритет = 2;
НоваяСтрока.ТипДокумента = Справочники.ТипыДокументов.ПаспортИностранногоГражданина;
НоваяСтрока = ТЗПриоритеты.Добавить();
НоваяСтрока.Приоритет = 3;
НоваяСтрока.ТипДокумента = Справочники.ТипыДокументов.Иной;
// Устанавливаем таблицу значений как параметр запроса
Запрос = Новый Запрос;
Запрос.УстановитьПараметр("ТЗПриоритеты", ТЗПриоритеты);
Далее мы помещаем эту таблицу значений во временную таблицу в самом запросе, что позволит нам использовать ее для дальнейших соединений.
Запрос.Текст = "
ВЫБРАТЬ
ВТПриоритеты.ТипДокумента КАК ТипДокумента,
ВТПриоритеты.Приоритет КАК Приоритет
ПОМЕСТИТЬ ВТПриоритеты
ИЗ
&ТЗПриоритеты КАК ВТПриоритеты
ИНДЕКСИРОВАТЬ ПО
Приоритет
;
Обратите внимание на использование ключевого слова ИНДЕКСИРОВАТЬ ПО Приоритет. Создание индекса по полю Приоритет в этой временной таблице поможет оптимизировать последующие операции соединения и поиска.
На втором шаге мы соединяем регистр сведений "ПаспортныеДанные" с нашей временной таблицей приоритетов. Для каждого физического лица, имеющего записи в регистре, мы определяем наивысший (минимальный по значению) приоритет из доступных типов документов.
////////////////////////////////////////////////////////////////////////////////
// Соединяем Регистр сведений с таблицей приоритетов и для физлица определяем номер наивысшего приоритета
// по имеющимся в базе типов документов
ВЫБРАТЬ
ПаспортныеДанныеСрезПоследних.ФизическоеЛицо КАК ФизическоеЛицо,
МИНИМУМ(ВТПриоритетДокументов.Приоритет) КАК Приоритет
ПОМЕСТИТЬ ВТПриоритетДокаФизЛица
ИЗ
РегистрСведений.ПаспортныеДанные.СрезПоследних КАК ПаспортныеДанныеСрезПоследних
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТПриоритеты КАК ВТПриоритетДокументов
ПО ПаспортныеДанныеСрезПоследних.ТипДокумента = ВТПриоритетДокументов.ТипДокумента
СГРУППИРОВАТЬ ПО
ПаспортныеДанныеСрезПоследних.ФизическоеЛицо
ИНДЕКСИРОВАТЬ ПО
Приоритет
;
Здесь мы используем виртуальную таблицу СрезПоследних, если нам нужны актуальные данные на текущий момент времени. Агрегатная функция МИНИМУМ(ВТПриоритетДокументов.Приоритет) позволяет нам найти наивысший приоритет для каждого ФизическоеЛицо. Результат также индексируется по полю Приоритет для дальнейшей оптимизации.
После того как мы определили числовой приоритет для каждого физического лица, нам нужно преобразовать это число обратно в ссылку на конкретный тип документа. Мы делаем это, снова соединяя временную таблицу ВТПриоритетДокаФизЛица с нашей исходной таблицей приоритетов ВТПриоритеты.
////////////////////////////////////////////////////////////////////////////////
// Теперь связываем получившуюся таблицу с таблицей приоритетов, чтобы значению числа соответствовала ссылка на тип документа
ВЫБРАТЬ
ВТПриоритетДокаФизЛица.ФизическоеЛицо КАК ФизическоеЛицо,
ВТПриоритеты.ТипДокумента КАК ТипДокумента
ПОМЕСТИТЬ ВТПриоритетныеТипыДоков
ИЗ
ВТПриоритеты КАК ВТПриоритеты
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТПриоритетДокаФизЛица КАК ВТПриоритетДокаФизЛица
ПО ВТПриоритеты.Приоритет = ВТПриоритетДокаФизЛица.Приоритет
ИНДЕКСИРОВАТЬ ПО
ФизическоеЛицо,
ТипДокумента
;
Результатом этого шага является временная таблица ВТПриоритетныеТипыДоков, которая для каждого физического лица содержит ссылку на тот тип документа, который имеет наивысший приоритет согласно нашим правилам. Эта таблица является ключевой для финального отбора.
Наконец, мы используем таблицу ВТПриоритетныеТипыДоков для отбора нужных записей из регистра сведений. Мы можем передать ее в качестве параметра отбора в виртуальную таблицу СрезПоследних регистра сведений.
////////////////////////////////////////////////////////////////////////////////
// После того как мы получили таблицу с приоритетными типами документов,
// мы можем использовать ее в качестве параметра для отбора в виртуальной таблице
ВЫБРАТЬ
ПаспортныеДанныеСрезПоследних.Серия КАК Серия,
ПаспортныеДанныеСрезПоследних.Номер КАК Номер,
ПаспортныеДанныеСрезПоследних.ФизическоеЛицо КАК ФизическоеЛицо
ПОМЕСТИТЬ ВТПаспортныеДанные
ИЗ
РегистрСведений.ПаспортныеДанные.СрезПоследних(
,
(ФизическоеЛицо, ТипДокумента) В
(ВЫБРАТЬ
ВТПриоритетныеТипыДоков.ФизическоеЛицо КАК ФизическоеЛицо,
ВТПриоритетныеТипыДоков.ТипДокумента КАК ТипДокумента
ИЗ
ВТПриоритетныеТипыДоков КАК ВТПриоритетныеТипыДоков)) КАК ПаспортныеДанныеСрезПоследних
ИНДЕКСИРОВАТЬ ПО
ФизическоеЛицо
";
В этом пакете мы формируем окончательную временную таблицу ВТПаспортныеДанные, которая содержит серию и номер документа для каждого физического лица, соответствующего найденному приоритетному типу документа. Теперь мы можем использовать эту временную таблицу в дальнейших соединениях с отбором только по ФизическоеЛицо, будучи уверенными, что для каждого физлица будет выбрана только одна запись согласно заданному приоритету.
Этот многопакетный запрос позволяет нам четко разделить логику по шагам, делая ее более понятной и управляемой. Однако, как и любой сложный запрос, он требует внимания к производительности и может быть оптимизирован.
Существует также альтернативный подход, который может быть более лаконичным для некоторых сценариев, особенно если количество приоритетов невелико. Он основан на использовании конструкции ВЫБОР КОГДА ТОГДА для определения приоритета напрямую в первом пакете запроса, затем агрегации по минимальному приоритету и последующего соединения.
Давайте разберем его по шагам:
Шаг 1: Выборка всех необходимых данных с расчетом приоритета. Мы можем выбрать все нужные данные из регистра сведений и одновременно рассчитать числовой приоритет для каждого типа документа, используя оператор ВЫБОР КОГДА ТОГДА. Это позволяет избежать создания отдельной таблицы приоритетов на стороне 1С и передавать ее как параметр.
ВЫБРАТЬ
ПаспортныеДанныеСрезПоследних.ФизическоеЛицо КАК ФизическоеЛицо,
ПаспортныеДанныеСрезПоследних.ТипДокумента КАК ТипДокумента,
ПаспортныеДанныеСрезПоследних.Серия КАК Серия,
ПаспортныеДанныеСрезПоследних.Номер КАК Номер,
ВЫБОР
КОГДА ПаспортныеДанныеСрезПоследних.ТипДокумента = &ПаспортРФ ТОГДА 1
КОГДА ПаспортныеДанныеСрезПоследних.ТипДокумента = &ПаспортИностранногоГражданина ТОГДА 2
КОГДА ПаспортныеДанныеСрезПоследних.ТипДокумента = &Иной ТОГДА 3
ИНАЧЕ 99 // Низкий приоритет для необработанных типов
КОНЕЦ КАК Приоритет
ПОМЕСТИТЬ ВТВсеПаспортныеДанныеСПриоритетом
ИЗ
РегистрСведений.ПаспортныеДанные.СрезПоследних КАК ПаспортныеДанныеСрезПоследних
ИНДЕКСИРОВАТЬ ПО
ФизическоеЛицо,
Приоритет
;
Здесь мы используем параметры запроса для ссылок на справочники ТипыДокументов. Такой подход делает запрос более универсальным и не привязывает к конкретным значениям в коде запроса.
Шаг 2: Определение минимального (высшего) приоритета для каждого физлица. Аналогично второму шагу в предыдущем решении, мы группируем данные по физическому лицу и находим минимальный приоритет из всех доступных для него документов.
ВЫБРАТЬ
ВТВсеПаспортныеДанныеСПриоритетом.ФизическоеЛицо КАК ФизическоеЛицо,
МИНИМУМ(ВТВсеПаспортныеДанныеСПриоритетом.Приоритет) КАК НаивысшийПриоритет
ПОМЕСТИТЬ ВТПриоритетыФизЛиц
ИЗ
ВТВсеПаспортныеДанныеСПриоритетом КАК ВТВсеПаспортныеДанныеСПриоритетом
СГРУППИРОВАТЬ ПО
ВТВсеПаспортныеДанныеСПриоритетом.ФизическоеЛицо
ИНДЕКСИРОВАТЬ ПО
ФизическоеЛицо,
НаивысшийПриоритет
;
Шаг 3: Соединение для получения окончательных данных. На последнем шаге мы соединяем исходную временную таблицу со всеми данными и рассчитанными приоритетами с таблицей, содержащей наивысшие приоритеты для каждого физлица. Соединение происходит по ФизическоеЛицо и по Приоритет = НаивысшийПриоритет.
ВЫБРАТЬ
ВТВсеПаспортныеДанныеСПриоритетом.ФизическоеЛицо КАК ФизическоеЛицо,
ВТВсеПаспортныеДанныеСПриоритетом.ТипДокумента КАК ТипДокумента,
ВТВсеПаспортныеДанныеСПриоритетом.Серия КАК Серия,
ВТВсеПаспортныеДанныеСПриоритетом.Номер КАК Номер
ИЗ
ВТВсеПаспортныеДанныеСПриоритетом КАК ВТВсеПаспортныеДанныеСПриоритетом
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТПриоритетыФизЛиц КАК ВТПриоритетыФизЛиц
ПО ВТВсеПаспортныеДанныеСПриоритетом.ФизическоеЛицо = ВТПриоритетыФизЛиц.ФизическоеЛицо
И ВТВсеПаспортныеДанныеСПриоритетом.Приоритет = ВТПриоритетыФизЛиц.НаивысшийПриоритет
;
Этот подход также позволяет получить одну запись для каждого физлица с заданным приоритетом, при этом избегая создания и передачи таблицы значений приоритетов из кода 1С. Он может быть более компактным, но для очень большого числа приоритетов или часто меняющихся правил, подход с таблицей приоритетов может быть гибче.
При работе со сложными запросами в 1С всегда следует помнить о производительности. Неоптимизированный запрос может существенно замедлить работу системы. Давайте рассмотрим ключевые аспекты оптимизации:
Минимизация объема выборки: Мы должны выбирать только те поля и данные, которые действительно необходимы для решения задачи. Избегайте использования конструкций типа ВЫБРАТЬ *, так как они могут значительно увеличить объем передаваемых данных и нагрузку на СУБД и 1С:Предприятие.
Использование индексов: Убедитесь, что все поля, которые мы используем в условиях ГДЕ, в условиях соединений ПО и в виртуальных таблицах, правильно проиндексированы. В 1С платформе индексы автоматически создаются для измерений регистров и полей справочников, но для временных таблиц мы можем использовать оператор ИНДЕКСИРОВАТЬ ПО, как мы это делали в наших примерах. Эффективное использование индексов значительно ускоряет поиск и сортировку данных.
Ограничение количества временных таблиц: Хотя временные таблицы являются мощным инструментом для структурирования сложных запросов, их избыточное количество может негативно сказаться на производительности. Каждая временная таблица требует создания и заполнения, что потребляет ресурсы. Мы должны стремиться объединять логику там, где это возможно, и уменьшать число промежуточных ВТ, если это не вредит читаемости и логике запроса.
Ограничение количества соединений: СУБД, как правило, более эффективно обрабатывает простые запросы. Большое количество соединений в одном пакете запроса или чрезмерное количество соединений между временными таблицами может привести к неоптимальному плану выполнения. Анализируйте необходимость каждого соединения и постарайтесь упростить их.
Избегание коррелированных подзапросов в ГДЕ: Хотя коррелированные подзапросы (например, ВЫБРАТЬ * ИЗ Дано КАК ВСЁ ГДЕ ВСЁ.Ф = Дано.Ф И ВСЁ.Приоритет = (ВЫБРАТЬ МИНИМУМ(Внутр.Приоритет) ИЗ Дано КАК Внутр ГДЕ Внутр.Ф = ВСЁ.Ф)) могут упростить написание запроса, они часто являются причиной падения производительности, особенно в файловом варианте работы 1С. В таких случаях предпочтительнее использовать многопакетные запросы с временными таблицами.
Менеджер временных таблиц: Если мы работаем с несколькими запросами, которым требуется одна и та же временная таблица (или набор временных таблиц), использование объекта МенеджерВременныхТаблиц позволяет переиспользовать уже созданные временные таблицы вместо их повторного создания в каждом новом запросе. Это значительно экономит время и ресурсы.
С выходом новых версий платформы 1С:Предприятие появляются дополнительные возможности для оптимизации работы с временными таблицами. В версии 8.3.25 и выше были добавлены важные функции:
Уникальные индексы для ВТ: Появилась возможность создавать уникальные индексы для временных таблиц. Это может быть очень полезно для обеспечения уникальности данных во временных таблицах, что особенно важно при работе с агрегатными функциями или при необходимости гарантировать, что для каждого измерения будет только одна запись. Уникальные индексы также могут потенциально ускорять операции соединения или поиска по этим таблицам, поскольку СУБД может использовать их для более эффективного доступа к данным.
Множественные индексы на ВТ: Теперь стало возможным создавать несколько индексов на одной временной таблице. Это дает нам большую гибкость в оптимизации запросов, использующих разные поля в условиях соединения и отбора. Например, если временная таблица соединяется с другими таблицами по полю А, а затем фильтруется по полю B, мы можем создать отдельные индексы для каждого из этих полей, или составной индекс, который лучше всего соответствует нашим задачам.
В дополнение к техническим аспектам оптимизации, давайте не забывать об общих принципах, которые помогают нам создавать более качественные и поддерживаемые решения:
Читаемость кода: При составлении сложных запросов, особенно с использованием временных таблиц и множества пакетов, крайне важно поддерживать читаемость текста запроса. Используйте осмысленные имена для временных таблиц и полей, форматируйте текст запроса (отступы, переносы строк), добавляйте комментарии, объясняющие логику каждого пакета или сложного участка. Хорошо структурированный и прокомментированный запрос гораздо проще отлаживать, модифицировать и поддерживать в будущем.
Тестирование производительности: Мы всегда должны тестировать производительность наших запросов на реальных или максимально приближенных к реальным объемах данных. Решение, которое оптимально работает на небольшой тестовой базе, может оказаться крайне неэффективным на больших объемах продуктивной системы. Используйте встроенные средства платформы 1С для измерения времени выполнения запроса, а также системные средства СУБД.
Использование плана выполнения запроса: Для анализа производительности сложных запросов рекомендуется использовать средства СУБД (например, SQL Server Management Studio, PgAdmin и т.д.) для просмотра плана выполнения запроса. План выполнения покажет нам, как СУБД интерпретирует и обрабатывает запрос, где находятся узкие места (например, сканирование таблицы вместо использования индекса, неэффективные соединения) и какие операции занимают больше всего ресурсов. Это является мощным инструментом для выявления и устранения проблем производительности.
Таким образом, для задачи выбора данных с приоритетом в 1С, помимо предложенного в теме форума многопакетного запроса, мы можем рассмотреть различные комбинации оператора ВЫБОР КОГДА ТОГДА, агрегатных функций (МИНИМУМ) и виртуальных таблиц, а также учитывать общие принципы оптимизации запросов для повышения их эффективности и надежности.