При интеграции 1С с внешними базами данных часто возникает задача: передать имеющийся в 1С набор строк (например, список из 1 000–2 000 контрагентов с сериями и номерами паспортов) в прямой запрос к MS SQL Server и соединить его через INNER JOIN с большой таблицей (содержащей десятки или сотни миллионов записей). Стандартные запросы 1С не позволяют в рамках одного запроса соединить временную таблицу 1С и внешний источник данных. Разберем наиболее эффективные и производительные способы решения этой задачи через прямое ADO-соединение.
Проанализируем типовые ошибки при попытке передать массив данных в SQL Server:
INSERT порождает сетевые задержки (round-trips) и транзакционные накладные расходы, из-за чего операция может занимать минуты вместо долей секунды.# (например, #temp_passports), которые изолированы в рамках текущего ADO-соединения и уничтожаются автоматически при его закрытии.Рассмотрим классический способ передачи таблицы через сериализацию в формат XML. В 1С формируется структурированная строка XML, которая передается в SQL-пакет как строковый параметр. SQL Server разбирает ее с помощью процедуры sp_xml_preparedocument и функции OPENXML, наполняя локальную временную таблицу.
Посмотрим на пример формирования XML и выполнения запроса в 1С:
// 1. Формируем XML-строку со списком документов
ЗаписьXML = Новый ЗаписьXML;
ЗаписьXML.УстановитьСтроку();
ЗаписьXML.ЗаписатьНачалоЭлемента("root");
Для Каждого Стр Из ТаблицаКонтрагентов Цикл
ЗаписьXML.ЗаписатьНачалоЭлемента("row");
ЗаписьXML.ЗаписатьАтрибут("uid", Строка(Стр.Ссылка.УникальныйИдентификатор()));
ЗаписьXML.ЗаписатьАтрибут("PASSP_SERIES", СокрЛП(Стр.Серия));
ЗаписьXML.ЗаписатьАтрибут("PASSP_NUMBER", СокрЛП(Стр.Номер));
ЗаписьXML.ЗаписатьКонецЭлемента();
КонецЦикла;
ЗаписьXML.ЗаписатьКонецЭлемента();
СтрокаXML = ЗаписьXML.Закрыть();
// 2. Выполняем скрипт через ADO
Соединение = Новый COMОбъект("ADODB.Connection");
Соединение.ConnectionString = "Provider=SQLOLEDB;Data Source=SQL_SERVER;Initial Catalog=PassportsDB;Integrated Security=SSPI;";
Соединение.Open();
ТекстСкрипта = "
|DECLARE @xmlDoc xml = '" + СтрокаXML + "';
|DECLARE @idoc int;
|EXEC sp_xml_preparedocument @idoc OUTPUT, @xmlDoc;
|
|SELECT uid, PASSP_SERIES, PASSP_NUMBER
|INTO #temp_passports
|FROM OPENXML (@idoc, '/root/row', 1)
|WITH (
| uid varchar(36),
| PASSP_SERIES varchar(10),
| PASSP_NUMBER varchar(20)
|);
|
|EXEC sp_xml_removedocument @idoc;
|
|SELECT tmp.uid, tmp.PASSP_SERIES, tmp.PASSP_NUMBER
|FROM #temp_passports tmp
|INNER JOIN ExpiredPassports t ON tmp.PASSP_SERIES = t.PASSP_SERIES
| AND tmp.PASSP_NUMBER = t.PASSP_NUMBER;
|";
Команда = Новый COMОбъект("ADODB.Command");
Команда.ActiveConnection = Соединение;
Команда.CommandText = ТекстСкрипта;
Рекордсет = Команда.Execute();
Разберем современный и более производительный способ для MS SQL Server 2016 и новее. Разбор строки JSON через функцию OPENJSON работает быстрее, чем XML, и не требует ручного управления системными дескрипторами в памяти сервера.
Посмотрим, как реализовать этот подход в 1С:
// 1. Формируем JSON-строку из массива структур
МассивДанных = Новый Массив;
Для Каждого Стр Из ТаблицаКонтрагентов Цикл
СтруктураСтроки = Новый Структура("uid,PASSP_SERIES,PASSP_NUMBER",
Строка(Стр.Ссылка.УникальныйИдентификатор()),
СокрЛП(Стр.Серия),
СокрЛП(Стр.Номер));
МассивДанных.Добавить(СтруктураСтроки);
КонецЦикла;
ЗаписьJSON = Новый ЗаписьJSON;
ЗаписьJSON.УстановитьСтроку();
ЗаписатьJSON(ЗаписьJSON, МассивДанных);
СтрокаJSON = ЗаписьJSON.Закрыть();
// 2. Передаем JSON в SQL-запрос
ТекстЗапроса = "
|DECLARE @json NVARCHAR(MAX) = '" + СтрокаJSON + "';
|
|SELECT uid, PASSP_SERIES, PASSP_NUMBER
|INTO #temp_passports
|FROM OPENJSON(@json)
|WITH (
| uid VARCHAR(36) '$.uid',
| PASSP_SERIES VARCHAR(10) '$.PASSP_SERIES',
| PASSP_NUMBER VARCHAR(20) '$.PASSP_NUMBER'
|);
|
|SELECT tmp.uid, t.Status
|FROM #temp_passports tmp
|INNER JOIN ExpiredPassports t ON tmp.PASSP_SERIES = t.PASSP_SERIES
| AND tmp.PASSP_NUMBER = t.PASSP_NUMBER;
|";
Если нет возможности формировать JSON или XML, строки можно вставить в локальную временную таблицу с помощью многострочного синтаксиса INSERT ... VALUES. Помним о важном ограничении MS SQL Server: один оператор VALUES может содержать не более 1 000 строк. Если в выборке 1 200 записей, скрипт необходимо разбить на два оператора INSERT.
Рассмотрим структуру такого SQL-пакета:
CREATE TABLE #temp_passports (
uid VARCHAR(36),
PASSP_SERIES VARCHAR(10),
PASSP_NUMBER VARCHAR(20)
);
-- Первый блок (до 1000 строк)
INSERT INTO #temp_passports (uid, PASSP_SERIES, PASSP_NUMBER) VALUES
('972ce6b0-dede-4980-8106-c79c6b44d1ce', '1234', '567890'),
('2e64a032-5f7e-4a56-a42d-d1501f90a40a', '1234', '567891');
-- Второй блок (оставшиеся строки)
INSERT INTO #temp_passports (uid, PASSP_SERIES, PASSP_NUMBER) VALUES
('a1b2c3d4-0000-1111-2222-333344445555', '4500', '112233');
-- Выполняем соединение
SELECT tmp.uid
FROM #temp_passports tmp
INNER JOIN ExpiredPassports t ON tmp.PASSP_SERIES = t.PASSP_SERIES
AND tmp.PASSP_NUMBER = t.PASSP_NUMBER;
Если объем передаваемых данных составляет сотни тысяч или миллионы строк, наиболее быстрым вариантом будет выгрузка списка из 1С в текстовый CSV/TSV файл на общий сетевой ресурс с последующей загрузкой через команду BULK INSERT прямо во временную таблицу SQL Server:
CREATE TABLE #temp_passports (
uid VARCHAR(36),
PASSP_SERIES VARCHAR(10),
PASSP_NUMBER VARCHAR(20)
);
BULK INSERT #temp_passports
FROM '\\server\share\passports.csv'
WITH (
FIELDTERMINATOR = ';',
ROWTERMINATOR = '\n',
FIRSTROW = 1
);
Для моментальной выгрузки ответа из ADO-рекордсета в 1С без построчного перебора используем метод GetRows(). Он возвращает двумерный SafeArray, который быстро обрабатывается платформой 1С:
Если НЕ Рекордсет.EOF Тогда
МассивРезультата = Рекордсет.GetRows();
// Полученный массив преобразуется в ТаблицуЗначений или используется для отбора
КонецЕсли;
Рекордсет.Close();
Соединение.Close();
Чтобы соединение временной таблицы на 1 200 строк с основной таблицей на 107 млн записей выполнялось за секунды, убедимся в наличии правильных индексов:
ExpiredPassports обязательно должен существовать составной индекс по полям соединения: CREATE NONCLUSTERED INDEX IX_Passports ON ExpiredPassports(PASSP_SERIES, PASSP_NUMBER). В этом случае оптимизатор выберет алгоритм Nested Loops и выполнит точечный поиск 1 200 раз по индексу, что займет доли секунды.#temp_passports не требуется — таблица полностью помещается в память, и оптимизатор эффективно считывает ее напрямую.