При разработке внешних обработок или документов в системе 1С:Предприятие часто возникает задача: пользователь заполняет табличную часть (например, загружает данные из файла Excel), после чего программа должна сопоставить эти строки со справочником базы данных и вывести только те записи, которых в справочнике еще нет. В процессе реализации этой логики начинающие разработчики регулярно сталкиваются с двумя основными проблемами: системной ошибкой при попытке передать табличную часть напрямую в параметры запроса и логическими/оптимизационными ошибками при составлении левого соединения в запросе. В этой статье мы подробно разберем, как решить эти проблемы, оптимизировать код и повысить скорость работы системы.
Рассмотрим ситуацию, с которой столкнулся автор темы. В коде обработчика выполняется следующий запрос:
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| ДанныеРаботника.FIN,
| ДанныеРаботника.Badge,
| ДанныеРаботника.Employee,
| ДанныеРаботника.Account
|ПОМЕСТИТЬ ВТРаботники
|ИЗ
| &ДанныеРаботника КАК ДанныеРаботника";
Запрос.УстановитьПараметр("ДанныеРаботника", Объект.ДанныеРаботника);
Запрос.Выполнить();
При выполнении этого кода платформа выдает ошибку:
«Неверные параметры "ДанныеРаботника"»
Выясним причину этого поведения платформы. Переменная Объект.ДанныеРаботника на управляемой форме представляет собой объект типа ДанныеФормыКоллекция (или ДанныеФормыСтруктураСКоллекцией). Этот тип данных предназначен исключительно для отображения табличной части на клиенте и связи ее с интерфейсом. СУБД ничего не знает о типах данных управляемых форм. Запрос в 1С может принимать в качестве табличного параметра только физические таблицы базы данных либо объект типа ТаблицаЗначений.
Чтобы исправить эту ошибку, нам необходимо преобразовать коллекцию данных формы в полноценную таблицу значений. Для этого используется метод Выгрузить(). Изменим строку установки параметра:
Запрос.УстановитьПараметр("ДанныеРаботника", Объект.ДанныеРаботника.Выгрузить());
Важное архитектурное замечание: Метод Выгрузить() для коллекций формы доступен только на сервере. Соответственно, весь этот код должен выполняться в процедуре с директивой компиляции &НаСервере или &НаСервереБезКонтекста.
Если табличная часть на форме заполняется динамически (например, парсингом Excel без жестко заданной структуры), колонки полученной таблицы значений могут получить тип Произвольный. При передаче такой таблицы в запрос возникнет новая ошибка: «Тип не может быть выбран в запросе». Чтобы этого избежать, перед передачей в запрос рекомендуется явно типизировать колонки таблицы значений:
// Пример принудительной типизации таблицы значений на сервере
ТаблицаДляЗапроса = Объект.ДанныеРаботника.Выгрузить();
ТаблицаДляЗапроса.Колонки.FIN.ТипЗначения = Новый ОписаниеТипов("Строка", Новый КвалификаторыСтроки(60));
ТаблицаДляЗапроса.Колонки.Badge.ТипЗначения = Новый ОписаниеТипов("Строка", Новый КвалификаторыСтроки(60));
ТаблицаДляЗапроса.Колонки.Employee.ТипЗначения = Новый ОписаниеТипов("Строка", Новый КвалификаторыСтроки(150));
ТаблицаДляЗапроса.Колонки.Account.ТипЗначения = Новый ОписаниеТипов("Строка", Новый КвалификаторыСтроки(60));
После этого типизированную таблицу значений можно гарантированно и без ошибок передавать в параметр запроса &ДанныеРаботника.
После того как данные успешно помещены во временную таблицу ВТРаботники, нам нужно сравнить их со справочником (например, Справочник.РаботникиАз) и выгрузить только те записи, которых в справочнике нет.
В Сообщении 12 автор предложил следующий вариант запроса:
"ВЫБРАТЬ
| ВТРаботники.FIN,
| ВТРаботники.Badge,
| ВТРаботники.Employee,
| ВТРаботники.Account
|ИЗ
| ВТРаботники КАК ВТРаботники
| ЛЕВОЕ СОЕДИНЕНИЕ Справочник.РаботникиАз КАК РаботникиАз
| ПО ВТРаботники.Account = РаботникиАз.РасчетныйСчет.НомерСчета
| И ВТРаботники.Account = РаботникиАз.РасчетныйСчет1.НомерСчета
|ГДЕ
| (РаботникиАз.РасчетныйСчет ЕСТЬ NULL
| И РаботникиАз.РасчетныйСчет1 ЕСТЬ NULL)"
Давайте подробно проанализируем этот запрос. В нем содержатся три серьезные ошибки — логическая, синтаксическая (в условии связи) и архитектурная (влияющая на производительность базы данных).
ГДЕ РаботникиАз.РасчетныйСчет ЕСТЬ NULL. Это в корне неверно. Если в справочнике есть сотрудник с подходящим счетом, но у него в базе данных просто не заполнен реквизит РасчетныйСчет (содержит значение NULL или ПустаяСсылка), то это условие выполнится. Запрос посчитает, что сотрудника нет, хотя он существует! NULL первичного ключа (Ссылки) правой таблицы. То есть проверять нужно строго: ГДЕ РаботникиАз.Ссылка ЕСТЬ NULL.ПО ВТРаботники.Account = РаботникиАз.РасчетныйСчет.НомерСчета заставляет СУБД выполнять неявные соединения таблиц «на лету» в момент сопоставления. Если поле РасчетныйСчет имеет ссылочный тип, то обращение к его реквизиту НомерСчета заставит SQL-сервер делать внутренние соединения (Implicit Joins) с таблицей банковских счетов. Это полностью ломает план оптимизации СУБД, отключает использование индексов и приводит к зависанию системы на больших объемах данных.ПО автор указал условие соединения через оператор И. Это означает, что строка из временной таблицы соединится со справочником только в том случае, если расчетный счет в Excel одновременно равен и первому, и второму расчетному счету сотрудника в базе, что физически невозможно. Если использовать ИЛИ, СУБД построит неэффективный план запроса с полным сканированием таблиц (Table Scan).ВТРаботники со справочником, СУБД должна быстро находить соответствия. Без индекса по полю связи (Account) СУБД будет выполнять полный перебор строк временной таблицы при сопоставлении с каждой строкой справочника.Для того чтобы решить задачу правильно и быстро, выполним оптимизацию по шагам:
Шаг 1: Сначала подготовим "плоскую" временную таблицу из справочника РаботникиАз, вытащив строковые номера счетов на первый уровень, чтобы избежать разыменования через точку в соединениях.
Шаг 2: Индексируем ключевые поля во временных таблицах.
Шаг 3: Сделаем левое соединение нашей таблицы из Excel с подготовленной таблицей справочника и проверим Ссылка ЕСТЬ NULL.
Ниже представлен готовый, оптимизированный шаблон кода для интеграции в вашу обработку:
&НаСервере
Функция ПолучитьНесуществующихРаботников() Экспорт
// 1. Выгружаем данные ТЧ формы в Таблицу Значений
ТаблицаДанных = Объект.ДанныеРаботника.Выгрузить();
// Создаем менеджер временных таблиц
МенеджерВТ = Новый МенеджерВременныхТаблиц;
Запрос = Новый Запрос;
Запрос.МенеджерВременныхТаблиц = МенеджерВТ;
// 2. Помещаем данные из ТЧ во временную таблицу и индексируем ее
Запрос.Текст =
"ВЫБРАТЬ
| ДанныеРаботника.FIN КАК FIN,
| ДанныеРаботника.Badge КАК Badge,
| ДанныеРаботника.Employee КАК Employee,
| ДанныеРаботника.Account КАК Account
|ПОМЕСТИТЬ ВТРаботникиИзФайла
|ИЗ
| &ДанныеРаботника КАК ДанныеРаботника
|
|ИНДЕКСИРОВАТЬ ПО
| Account";
Запрос.УстановитьПараметр("ДанныеРаботника", ТаблицаДанных);
Запрос.Выполнить();
// 3. Создаем плоскую временную таблицу существующих сотрудников с их счетами
ЗапросПодготовки = Новый Запрос;
ЗапросПодготовки.МенеджерВременныхТаблиц = МенеджерВТ;
ЗапросПодготовки.Текст =
"ВЫБРАТЬ
| Работники.Ссылка КАК Ссылка,
| Работники.РасчетныйСчет.НомерСчета КАК НомерСчета1,
| Работники.РасчетныйСчет1.НомерСчета КАК НомерСчета2
|ПОМЕСТИТЬ ВТБазаРаботников
|ИЗ
| Справочник.РаботникиАз КАК Работники
|
|ИНДЕКСИРОВАТЬ ПО
| НомерСчета1,
| НомерСчета2";
ЗапросПодготовки.Выполнить();
// 4. Финальный запрос: левое соединение для поиска отсутствующих сотрудников
ФиналЗапрос = Новый Запрос;
ФиналЗапрос.МенеджерВременныхТаблиц = МенеджерВТ;
ФиналЗапрос.Текст =
"ВЫБРАТЬ
| ВТФайл.FIN КАК FIN,
| ВТФайл.Badge КАК Badge,
| ВТФайл.Employee КАК Employee,
| ВТФайл.Account КАК Account
|ИЗ
| ВТРаботникиИзФайла КАК ВТФайл
| // Соединяем по первому расчетному счету
| ЛЕВОЕ СОЕДИНЕНИЕ ВТБазаРаботников КАК БазаРаботников1
| ПО ВТФайл.Account = БазаРаботников1.НомерСчета1
| // Соединяем по второму расчетному счету (если счет может быть во втором реквизите)
| ЛЕВОЕ СОЕДИНЕНИЕ ВТБазаРаботников КАК БазаРаботников2
| ПО ВТФайл.Account = БазаРаботников2.НомерСчета2
|ГДЕ
| // Если обе связи вернули NULL, значит такого счета нет ни в одном реквизите у сотрудников
| БазаРаботников1.Ссылка ЕСТЬ NULL
| И БазаРаботников2.Ссылка ЕСТЬ NULL";
// Результат запроса будет содержать только те строки, которых нет в базе 1С
РезультатТаблица = ФиналЗапрос.Выполнить().Выгрузить();
Возврат РезультатТаблица;
КонецФункции
Давайте подведем итог проделанной работы. Проанализировав исходную проблему, мы внесли ключевые архитектурные изменения:
Выгрузить() для преобразования коллекции формы в поддерживаемую СУБД ТаблицуЗначений.ПО, заменив его предварительным сбором плоских данных во временную таблицу ВТБазаРаботников.ИНДЕКСИРОВАТЬ ПО) по связующим полям, что увеличит скорость работы алгоритма в десятки раз на реальных базах данных.БазаРаботников1.Ссылка ЕСТЬ NULL.Этот подход гарантирует высокую скорость обработки данных, отсутствие блокировок СУБД и является стандартом качественной разработки на платформе 1С:Предприятие 8.3.