При разработке интеграций и механизмов загрузки данных в платформе «1С:Предприятие 8» часто возникает задача поиска существующих элементов справочника (например, контрагентов или организаций) по связке из нескольких связанных ключевых полей. Классический пример такого составного ключа — это пара ИНН и КПП. Выполнение запросов в цикле для каждого элемента является грубой ошибкой проектирования, так как порождает избыточную нагрузку на базу данных и критически снижает производительность системы.
В этой статье мы подробно разберем, как решить эту задачу с помощью одного эффективного запроса, проанализируем нюансы оптимизации СУБД, исключим типичные ошибки приведения типов и рассмотрим особенности реализации алгоритма при работе через внешнее COM-соединение.
В синтаксисе языка запросов 1С реализована возможность непосредственного сравнения групп полей (кортежей). Мы можем передать таблицу значений, содержащую колонки ИНН и КПП, напрямую в качестве параметра запроса и применить следующее условие фильтрации:
ВЫБРАТЬ
Организации.Ссылка КАК Ссылка
ИЗ
Справочник.Организации КАК Организации
ГДЕ
(Организации.ИНН, Организации.КПП) В (&ТЗ_ИНН_КПП)
На первый взгляд это решение выглядит лаконичным и удобным, однако мы должны рассмотреть, как этот запрос транслируется на уровне СУБД:
(col1, col2) IN ((val1, val2), ...). Запрос отработает быстро и эффективно.ИЛИ: ((ИНН = '123' И КПП = '456') ИЛИ (ИНН = '789' И КПП = '012') ИЛИ ...).Важно: Если в вашей таблице параметров (ТЗ_ИНН_КПП) содержится большое количество строк (более нескольких сотен), MS SQL Server может построить неоптимальный план выполнения. Из-за обилия логических условий ИЛИ оптимизатор СУБД может отказаться от использования индексов по полям ИНН и КПП и переключиться на полное сканирование таблицы (Table Scan / Index Scan), что приведет к сильным задержкам и блокировкам.
Для обеспечения гарантированно высокой производительности на любых СУБД мы рекомендуем использовать схему с помещением таблицы параметров во временную таблицу и последующим внутренним соединением. Этот метод решает проблему больших массивов данных.
Разберем по шагам правильную реализацию этого алгоритма:
Шаг 1. Создание и строгая типизация исходной таблицы значений
Прежде чем передать таблицу значений в запрос, её колонки необходимо строго типизировать. Если этого не сделать, СУБД воспримет текстовые поля как строки неограниченной длины. При сравнении строк неограниченной длины с полями справочника фиксированной длины СУБД выполнит неявное приведение типов (Implicit Conversion), что также заставит её игнорировать индексы.
Создадим таблицу значений с явным указанием длины строк:
// Определяем квалификаторы и описания типов для колонок
КвалификаторИНН = Новый КвалификаторыСтроки(12);
ОписаниеИНН = Новый ОписаниеТипов("Строка", , КвалификаторИНН);
КвалификаторКПП = Новый КвалификаторыСтроки(9);
ОписаниеКПП = Новый ОписаниеТипов("Строка", , КвалификаторКПП);
// Инициализируем Таблицу Значений
ТЗ_Параметры = Новый ТаблицаЗначений;
ТЗ_Параметры.Колонки.Добавить("ИНН", ОписаниеИНН);
ТЗ_Параметры.Колонки.Добавить("КПП", ОписаниеКПП);
// Пример заполнения таблицы данными поиска
НоваяСтрока = ТЗ_Параметры.Добавить();
НоваяСтрока.ИНН = "7701000000";
НоваяСтрока.КПП = "770101001";
Шаг 2. Разработка пакетного запроса с индексацией временной таблицы
Теперь мы передаем типизированную таблицу значений в запрос, помещаем ее во временную таблицу, обязательно индексируем поля связи и выполняем внутреннее соединение с целевым справочником:
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| ТЗ.ИНН КАК ИНН,
| ТЗ.КПП КАК КПП
|ПОМЕСТИТЬ ВТ_ФильтрПоиска
|ИЗ
| &ТЗ_Параметры КАК ТЗ
|;
|
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ
| Организации.Ссылка КАК Ссылка,
| Организации.ИНН КАК ИНН,
| Организации.КПП КАК КПП
|ИЗ
| Справочник.Организации КАК Организации
| ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_ФильтрПоиска КАК Фильтр
| ПО Организации.ИНН = Фильтр.ИНН
| И Организации.КПП = Фильтр.КПП";
// Индексацию полей временной таблицы можно выполнить прямо в запросе,
// но при соединении по равенству небольших таблиц оптимизатор СУБД
// автоматически выберет наиболее эффективный метод (например, Nested Loops или Hash Match).
// Для очень больших списков поиска рекомендуется добавить:
// ИНДЕКСИРОВАТЬ ПО Фильтр.ИНН, Фильтр.КПП
Запрос.УстановитьПараметр("ТЗ_Параметры", ТЗ_Параметры);
РезультатЗапроса = Запрос.Выполнить().Выгрузить();
В процессе обсуждения подобных задач иногда всплывает предложение объединить поля для упрощения поиска:
ГДЕ (Контрагенты.ИНН + Контрагенты.КПП) В (&СписокОбъединенныхСтрок)
Настоятельно рекомендуем никогда не использовать этот подход на рабочих базах!
Любые арифметические или строковые операции над индексируемыми реквизитами таблиц базы данных в секциях ГДЕ или ПО делают запрос не-SARGable. Это означает, что СУБД физически не может применить существующие индексы по полям ИНН и КПП для поиска записей. Чтобы вычислить результат сложения строк, СУБД будет вынуждена прочитать абсолютно каждую запись из справочника в операционную память и сложить строки «на лету» (Full Table Scan). При объеме справочника в сотни тысяч контрагентов это вызовет колоссальную нагрузку на дисковую систему и процессор сервера.
Если поиск элементов необходимо выполнить в базе-приемнике через COM-соединение, разработчики часто сталкиваются с ошибками передачи параметров. Напрямую передать объект ТаблицаЗначений, созданный в текущей (локальной) базе, в качестве параметра запроса COM-соединения нельзя, так как эти объекты принадлежат разным процессам и контекстам приложений.
Рассмотрим два надежных способа обхода этого ограничения.
Способ А. Создание и заполнение таблицы на стороне COM
Мы можем конструировать служебные объекты непосредственно внутри COM-процесса, используя менеджер соединения:
// Инициализируем COM-соединение (пример схемы подключения)
// COMСоединение = Новый COMОбъект("V83.COMConnector").Connect(СтрокаПодключения);
// Создаем Таблицу Значений в контексте COM-базы
ТЗ_COM = COMСоединение.NewObject("ТаблицаЗначений");
// Создаем описания типов на стороне COM
КвалификаторИНН = COMСоединение.NewObject("КвалификаторыСтроки", 12);
ОписаниеИНН = COMСоединение.NewObject("ОписаниеТипов", "Строка", , КвалификаторИНН);
КвалификаторКПП = COMСоединение.NewObject("КвалификаторыСтроки", 9);
ОписаниеКПП = COMСоединение.NewObject("ОписаниеТипов", "Строка", , КвалификаторКПП);
ТЗ_COM.Колонки.Добавить("ИНН", ОписаниеИНН);
ТЗ_COM.Колонки.Добавить("КПП", ОписаниеКПП);
// Наполняем таблицу строками из нашего локального источника
Для Каждого СтрокаИсточника Из ЛокальныйСписокДанных Цикл
НоваяСтрокаCOM = ТЗ_COM.Добавить();
НоваяСтрокаCOM.ИНН = СтрокаИсточника.ИНН;
НоваяСтрокаCOM.КПП = СтрокаИсточника.КПП;
КонецЦикла;
// Теперь таблицу можно безболезненно передавать в COM-запрос
ЗапросCOM = COMСоединение.NewObject("Запрос");
ЗапросCOM.Текст = "..."; // Текст запроса из Шага 2
ЗапросCOM.УстановитьПараметр("ТЗ_Параметры", ТЗ_COM);
Результат = ЗапросCOM.Выполнить().Выгрузить();
Способ Б. Быстрая XML/Внутренняя сериализация данных
Если объем передаваемых строк велик, построчный обход и создание объектов через COM-границу в цикле могут работать медленно из-за накладных расходов на межпроцессное взаимодействие. В этом случае эффективнее сериализовать таблицу значений в строку, передать ее как простой текст, а на стороне COM-соединения восстановить исходный объект:
// Шаг 1: Сериализуем локальную, заранее типизированную ТЗ в системную строку 1С
СтрокаСериализации = ЗначениеВСтрокуВнутр(ЛокальнаяТЗ_ИНН_КПП);
// Шаг 2: В один вызов передаем строку в COM-соединение и десериализуем объект
ТЗ_Для_Запроса_COM = COMСоединение.ЗначениеИзСтрокиВнутр(СтрокаСериализации);
// Шаг 3: Передаем восстановленную ТЗ в COM-запрос
ЗапросCOM = COMСоединение.NewObject("Запрос");
ЗапросCOM.Текст = ТекстЗапроса; // Текст запроса из Шага 2
ЗапросCOM.УстановитьПараметр("ТЗ_Параметры", ТЗ_Для_Запроса_COM);
РезультатCOM = ЗапросCOM.Выполнить().Выгрузить();
Использование сериализации значительно ускоряет код разработки, снижает количество COM-вызовов и исключает ошибки несовместимости типов в параметрах запроса.