При разработке интеграций, загрузок из внешних файлов (Excel, XML, JSON) или обмене данными программисты 1С часто сталкиваются с классической задачей: есть локальная таблица значений (ТЗ) объемом в несколько тысяч строк, содержащая ключевые поля документов (например, входящий номер, дата и сумма), и необходимо быстро проверить, существуют ли такие документы в базе данных 1С, а также совпадают ли суммы в базе с данными из внешнего источника. В этой статье мы подробно рассмотрим и сравним различные подходы к решению этой задачи — от самых медленных и неоптимальных (антипаттернов) до максимально быстрых, соответствующих стандартам разработки фирмы "1С".
Представим, что у нас есть таблица значений ТаблицаНоменклатуры (из сообщения автора темы) со следующими колонками:
Накладная — входящий номер документа (строка);ДатаНакладной — дата входящего документа (дата);СуммаОпт — сумма документа по внешним данным (число).В этой таблице единовременно находится около 3 000 строк. Наша цель — сопоставить эти строки с существующими в базе документами ПоступлениеТоваровУслуг по реквизитам НомерВходящегоДокумента и ДатаВходящегоДокумента. Если документ не найден, нужно вывести сообщение об ошибке. Если документ найден, но его реквизит СуммаДокумента не совпадает с колонкой СуммаОпт, также необходимо сообщить об этом пользователю.
Рассмотрим вариант решения, к которому часто приходят начинающие разработчики. Это так называемый "тупенький цикл" (как назвал его автор в Сообщении 4):
Для каждого стр Из Объект.ТаблицаНоменклатуры Цикл
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| ПоступлениеТоваровУслуг.Ссылка КАК Ссылка,
| ПоступлениеТоваровУслуг.СуммаДокумента КАК СуммаДокумента
|ИЗ
| Документ.ПоступлениеТоваровУслуг КАК ПоступлениеТоваровУслуг
|ГДЕ
| ПоступлениеТоваровУслуг.НомерВходящегоДокумента = &НомерВходящегоДокумента
| И ПоступлениеТоваровУслуг.ДатаВходящегоДокумента = &ДатаВходящегоДокумента";
Запрос.УстановитьПараметр("ДатаВходящегоДокумента", стр.ДатаНакладной);
Запрос.УстановитьПараметр("НомерВходящегоДокумента", стр.Накладная);
РезультатЗапроса = Запрос.Выполнить();
Если РезультатЗапроса.Пустой() Тогда
Сообщить("Не смогли найти документ " + стр.Накладная + " от " + стр.ДатаНакладной);
Иначе
Выборка = РезультатЗапроса.Выбрать();
Выборка.Следующий();
Если Выборка.СуммаДокумента <> стр.СуммаОпт Тогда
Сообщить("Сумма по документу " + стр.Накладная + " расходится! В 1С: " + Выборка.СуммаДокумента + ", в файле: " + стр.СуммаОпт);
КонецЕсли;
КонецЕсли;
КонецЦикла;
Почему так делать категорически нельзя:
Каждое обращение к базе данных в цикле порождает отдельный сетевой запрос к СУБД. При объеме таблицы в 3 000 записей мы выполним 3 000 отдельных SQL-запросов. Это приведет к колоссальным накладным расходам на сетевой трафик, компиляцию планов запросов на уровне СУБД и выделение памяти. На реальной базе такой код может выполняться несколько минут, парализуя работу сервера при высокой конкурентной нагрузке. Запрос в цикле — это грубейшее нарушение стандартов разработки 1С.
В Сообщении 2 предложен более разумный подход: прочитать один раз все документы за определенный период (например, за текущий год) из базы данных в оперативную память, а затем осуществлять по ним локальный поиск. Проанализируем этот алгоритм:
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| ПоступлениеТоваровУслуг.Ссылка КАК Ссылка,
| ПоступлениеТоваровУслуг.НомерВходящегоДокумента КАК НомерВходящегоДокумента,
| ПоступлениеТоваровУслуг.ДатаВходящегоДокумента КАК ДатаВходящегоДокумента,
| ПоступлениеТоваровУслуг.СуммаДокумента КАК СуммаДокумента
|ИЗ
| Документ.ПоступлениеТоваровУслуг КАК ПоступлениеТоваровУслуг
|ГДЕ
| ПоступлениеТоваровУслуг.Дата МЕЖДУ &ДатаНачала И &ДатаКонец
| И НЕ ПоступлениеТоваровУслуг.ПометкаУдаления";
Запрос.УстановитьПараметр("ДатаНачала", НачалоГода(ТекущаяДата()));
Запрос.УстановитьПараметр("ДатаКонец", КонецГода(ТекущаяДата()));
ТаблицаПТиУЗаГод = Запрос.Выполнить().Выгрузить();
// ВАЖНО: Строим составной индекс для ускорения поиска!
ТаблицаПТиУЗаГод.Индексы.Добавить("ДатаВходящегоДокумента, НомерВходящегоДокумента");
Для каждого стр Из Объект.ТаблицаНоменклатуры Цикл
ПараметрыПоиска = Новый Структура;
ПараметрыПоиска.Вставить("ДатаВходящегоДокумента", стр.ДатаНакладной);
ПараметрыПоиска.Вставить("НомерВходящегоДокумента", стр.Накладная);
НайденныеСтроки = ТаблицаПТиУЗаГод.НайтиСтроки(ПараметрыПоиска);
Если НайденныеСтроки.Количество() = 0 Тогда
Сообщить("Не смогли найти документ " + стр.Накладная + " от " + стр.ДатаНакладной);
Иначе
Если НайденныеСтроки[0].СуммаДокумента <> стр.СуммаОпт Тогда
Сообщить("Сумма по документу " + стр.Накладная + " расходится! В 1С: " + НайденныеСтроки[0].СуммаДокумента + ", в файле: " + стр.СуммаОпт);
КонецЕсли;
КонецЕсли;
КонецЦикла;
Важные нюансы оптимизации этого метода:
НайтиСтроки() на каждой итерации цикла будет выполнять полный линейный перебор всех записей. Для 3 000 итераций и, например, 50 000 документов в локальной ТЗ это даст до 150 миллионов операций сравнения!Индексы.Добавить("ДатаВходящегоДокумента, НомерВходящегоДокумента"). Если добавить их по отдельности (в два вызова метода), платформа не сможет задействовать индекс для многокритериального поиска.
МинимальнаяДата = Объект.ТаблицаНоменклатуры.Минимум("ДатаНакладной");
МаксимальнаяДата = Объект.ТаблицаНоменклатуры.Максимум("ДатаНакладной");
Если нам необходимо сопоставить данные в оперативной памяти без сложных запросов к БД, объект Соответствие является великолепной альтернативой таблице значений. На уровне платформы 1С поиск по ключу в объекте Соответствие реализован через хэш-таблицы и работает на порядок быстрее, чем метод НайтиСтроки по индексированной ТЗ, при этом потребляя меньше памяти.
Давайте разберем, как реализовать этот подход на практике:
// Шаг 1. Получаем документы из базы за динамически определенный период
МинимальнаяДата = Объект.ТаблицаНоменклатуры.Минимум("ДатаНакладной");
МаксимальнаяДата = Объект.ТаблицаНоменклатуры.Максимум("ДатаНакладной");
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| ПоступлениеТоваровУслуг.Ссылка КАК Ссылка,
| ПоступлениеТоваровУслуг.НомерВходящегоДокумента КАК НомерВходящегоДокумента,
| ПоступлениеТоваровУслуг.ДатаВходящегоДокумента КАК ДатаВходящегоДокумента,
| ПоступлениеТоваровУслуг.СуммаДокумента КАК СуммаДокумента
|ИЗ
| Документ.ПоступлениеТоваровУслуг КАК ПоступлениеТоваровУслуг
|ГДЕ
| ПоступлениеТоваровУслуг.Дата МЕЖДУ &ДатаНачала И &ДатаКонец
| И НЕ ПоступлениеТоваровУслуг.ПометкаУдаления";
Запрос.УстановитьПараметр("ДатаНачала", НачалоДня(МинимальнаяДата));
Запрос.УстановитьПараметр("ДатаКонец", КонецДня(МаксимальнаяДата));
Выборка = Запрос.Выполнить().Выбрать();
// Шаг 2. Заполняем хэш-таблицу (Соответствие).
// В качестве ключа используем уникальную строку, собранную из даты и номера.
КэшДокументов = Новый Соответствие;
Пока Выборка.Следующий() Цикл
КлючПоиска = Формат(Выборка.ДатаВходящегоДокумента, "ДФ=yyyyMMdd") + "_" + СокрЛП(Выборка.НомерВходящегоДокумента);
КэшДокументов.Вставить(КлючПоиска, Выборка.СуммаДокумента);
КонецЦикла;
// Шаг 3. Быстрый обход исходной таблицы значений
Для каждого стр Из Объект.ТаблицаНоменклатуры Цикл
КлючПоиска = Формат(стр.ДатаНакладной, "ДФ=yyyyMMdd") + "_" + СокрЛП(стр.Накладная);
СуммаВ1С = КэшДокументов.Получить(КлючПоиска);
Если СуммаВ1С = Неопределено Тогда
Сообщить("Не смогли найти документ " + стр.Накладная + " от " + стр.ДатаНакладной);
ИначеЕсли СуммаВ1С <> стр.СуммаОпт Тогда
Сообщить("Сумма по документу " + стр.Накладная + " расходится! В 1С: " + СуммаВ1С + ", в файле: " + стр.СуммаОпт);
КонецЕсли;
КонецЦикла;
Этот метод чрезвычайно прост в отладке, работает мгновенно в памяти и идеально подходит, когда исходная ТЗ не очень велика, а период документов локализован (например, в рамках одного-двух месяцев).
В Сообщениях 3, 5, 7 и 8 участники форума пришли к абсолютно верному выводу: для СУБД самым естественным и быстрым способом сопоставления двух наборов данных является операция соединения (JOIN). Для реализации этого подхода нам нужно передать локальную ТЗ во временную таблицу (ВТ) внутри СУБД и выполнить одно пакетное левое соединение.
Но здесь кроется важнейшая деталь, которую часто упускают и из-за чего этот метод у разработчиков может тормозить.
Если мы передаем таблицу значений в качестве параметра в запрос (чтобы поместить её во временную таблицу), колонки этой ТЗ должны иметь строго определенные типы данных с ограничениями по длине и точности. Если этого не сделать:
NVARCHAR(MAX) вместо NVARCHAR(50).Давайте по шагам разберем, как правильно реализовать сопоставление через СУБД с учетом всех стандартов оптимизации 1С.
// Шаг 1. Создаем строго типизированную таблицу значений для передачи в СУБД
ТЗДляЗапроса = Новый ТаблицаЗначений;
// Настройка типа для колонки НомерВходящегоДокумента (строка длиной 50 знаков)
МассивСтрока = Новый Массив;
МассивСтрока.Добавить(Тип("Строка"));
КвалификаторСтроки = Новый КвалификаторыСтроки(50);
ОписаниеСтроки = Новый ОписаниеТипов(МассивСтрока, , КвалификаторСтроки);
ТЗДляЗапроса.Колонки.Добавить("Накладная", ОписаниеСтроки);
// Настройка типа для колонки ДатаНакладной
ТЗДляЗапроса.Колонки.Добавить("ДатаНакладной", Новый ОписаниеТипов("Дата"));
// Настройка типа для колонки СуммаОпт (число 15 знаков, 2 знака после запятой)
МассивЧисло = Новый Массив;
МассивЧисло.Добавить(Тип("Число"));
КвалификаторЧисла = Новый КвалификаторыЧисла(15, 2);
ОписаниеЧисла = Новый ОписаниеТипов(МассивЧисло, , КвалификаторЧисла);
ТЗДляЗапроса.Колонки.Добавить("СуммаОпт", ОписаниеЧисла);
// Переносим данные из исходной таблицы номенклатуры в типизированную ТЗ
Для Каждого Стр Из Объект.TablicaNomenklatury Цикл
ЗаполнитьЗначенияСвойств(ТЗДляЗапроса.Добавить(), Стр);
КонецЦикла;
// Шаг 2. Вычисляем динамические границы периода для ограничения выборки из базы данных
МинимальнаяДата = ТЗДляЗапроса.Минимум("ДатаНакладной");
МаксимальнаяДата = ТЗДляЗапроса.Максимум("ДатаНакладной");
// Шаг 3. Составляем пакетный запрос
Запрос = Новый Запрос;
Запрос.УстановитьПараметр("ВнешняяТЗ", ТЗДляЗапроса);
Запрос.УстановитьПараметр("ДатаНачала", НачалоДня(МинимальнаяДата));
Запрос.УстановитьПараметр("ДатаКонец", КонецДня(МаксимальнаяДата));
Запрос.Текст =
"ВЫБРАТЬ
| ВнешняяТЗ.Накладная КАК Накладная,
| ВнешняяТЗ.ДатаНакладной КАК ДатаНакладной,
| ВнешняяТЗ.СуммаОпт КАК СуммаОпт
|ПОМЕСТИТЬ ВТ_ИсходныеДанные
|ИЗ
| &ВнешняяТЗ КАК ВнешняяТЗ
|
|ИНДЕКСИРОВАТЬ ПО
| Накладная,
| ДатаНакладной
|;
|
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ
| ВТ_ИсходныеДанные.Накладная КАК Накладная,
| ВТ_ИсходныеДанные.ДатаНакладной КАК ДатаНакладной,
| ВТ_ИсходныеДанные.СуммаОпт КАК СуммаОпт,
| ПоступлениеТоваровУслуг.Ссылка КАК СсылкаДокумента,
| ПоступлениеТоваровУслуг.СуммаДокумента КАК СуммаВ1С,
| ВЫБОР
| КОГДА ПоступлениеТоваровУслуг.Ссылка ЕСТЬ NULL
| ТОГДА ""НеНайден""
| КОГДА ЕСТЬNULL(ПоступлениеТоваровУслуг.СуммаДокумента, 0) <> ВТ_ИсходныеДанные.СуммаОпт
| ТОГДА ""РасходятсяСуммы""
| ИНАЧЕ ""ВсеОК""
| КОНЕЦ КАК СтатусПроверки
|ИЗ
| ВТ_ИсходныеДанные КАК ВТ_ИсходныеДанные
| ЛЕВОЕ СОЕДИНЕНИЕ Документ.ПоступлениеТоваровУслуг КАК ПоступлениеТоваровУслуг
| ПО ВТ_ИсходныеДанные.Накладная = ПоступлениеТоваровУслуг.НомерВходящегоДокумента
| И ВТ_ИсходныеДанные.ДатаНакладной = ПоступлениеТоваровУслуг.ДатаВходящегоДокумента
| И (ПоступлениеТоваровУслуг.Дата МЕЖДУ &ДатаНачала И &ДатаКонец)
| И (НЕ ПоступлениеТоваровУслуг.ПометкаУдаления)
|ГДЕ
| ПоступлениеТоваровУслуг.Ссылка ЕСТЬ NULL
| ИЛИ ЕСТЬNULL(ПоступлениеТоваровУслуг.СуммаДокумента, 0) <> ВТ_ИсходныеДанные.СуммаОпт";
// Шаг 4. Обрабатываем только ошибочные строки
Результат = Запрос.Выполнить().Выбрать();
Пока Результат.Следующий() Цикл
Если Результат.СтатусПроверки = "НеНайден" Тогда
Сообщить("Не смогли найти документ " + Результат.Накладная + " от " + Результат.ДатаНакладной);
ИначеЕсли Результат.СтатусПроверки = "РасходятсяСуммы" Тогда
Сообщить("Сумма по документу " + Результат.Накладная + " расходится! В 1С: " + Результат.СуммаВ1С + ", в файле: " + Результат.СуммаОпт);
КонецЕсли;
КонецЦикла;
Преимущества данного решения:
ГДЕ.НомерВходящегоДокумента и ДатаВходящегоДокумента, которые обычно индексируются в типовых конфигурациях) позволяет СУБД выполнить слияние миллионов строк за миллисекунды.В Сообщении 11 предложена универсальная функция НайтиПоУсловию, которая использует функцию Вычислить() (динамическая компиляция кода во время выполнения).
Разберем этот пример как опасный антипаттерн:
Вычислить() заставляет движок платформы заново компилировать выражение на каждой итерации цикла. Для 3 000 строк это приведет к резкому падению производительности процессора. Код будет выполняться в десятки раз медленнее, чем обычное прямое сравнение.Вычислить() для динамического кода крайне не рекомендуется стандартами безопасной разработки 1С, поскольку может привести к уязвимостям (инъекция произвольного кода через передаваемые параметры ТЗ).Для закрепления материала проанализируем сводные характеристики рассмотренных вариантов:
Вычислить() (Решение №11).