При работе с внешними компонентами, такими как 1С++, разработчики часто сталкиваются с необходимостью передачи больших списков объектов (например, списков номенклатуры или счетов) из встроенного языка 1С в SQL-запрос. Стандартный метод УложитьСписокОбъектов объекта ODBCRecordSet является универсальным инструментом, однако при работе с объемами данных от 1000 элементов и выше его производительность резко падает. В некоторых случаях простая операция может занимать несколько секунд, что критично для интерактивной работы системы.
В этой статье мы подробно разберем, почему штатный механизм работает медленно, проанализируем результаты замеров производительности и реализуем альтернативный, гораздо более быстрый способ загрузки данных с сохранением возможности работы с иерархией справочников.
Давайте разберем причину медленной работы УложитьСписокОбъектов. Основная проблема заключается в том, как компонента взаимодействует с объектом СписокЗначений. Внутренние механизмы компоненты вынуждены последовательно итерировать (перебирать) каждый элемент списка. Поскольку каждый элемент в 1С 7.7 — это тяжеловесный COM-объект, накладные расходы на обращение к каждой строке суммируются. Если в списке 3000-5000 элементов, время ожидания становится неприемлемым.
Посмотрим на результаты замеров, предоставленные участниками сообщества. При обработке 3000 элементов справочника "Счета" получаются следующие цифры:
Как мы видим, использование специализированного метода ВыполнитьSQL_ИзТЗ дает десятикратный прирост скорости. Наша задача — использовать это преимущество, не потеряв при этом возможность автоматического разворота групп справочника.
Первым делом рассмотрим ситуацию, когда нам не нужно учитывать иерархию (только конкретные элементы). Вместо медленного УложитьСписокОбъектов мы подготовим временную таблицу в SQL вручную и заполним её данными из ТаблицыЗначений.
Проанализируем следующий пример кода, который демонстрирует этот подход:
// Создаем объект запроса
Запрос = СоздатьОбъект("ODBCRecordSet");
// Подготавливаем структуру временной таблицы в TempDB
ТекстЗапроса = "
|IF OBJECT_ID(N'tempdb..#Фильтр3','U') IS NOT NULL DROP TABLE #Фильтр3;
|CREATE TABLE #Фильтр3 (Счет Char(9), UNIQUE CLUSTERED (Счет))";
Запрос.ВыполнитьИнструкцию(ТекстЗапроса);
// Подготавливаем параметризованный запрос на вставку
Запрос.Подготовить("INSERT INTO #Фильтр3 VALUES(?)");
// Заполняем таблицу данными напрямую из ТаблицыЗначений (ВремТЗ)
// Этот метод работает значительно быстрее итератора списка
Запрос.ВыполнитьSQL_ИзТЗ(ВремТЗ);
В данном случае метод ВыполнитьSQL_ИзТЗ использует механизм подготовленных запросов SQL Server, что минимизирует время на передачу данных. Однако здесь кроется нюанс: если нам нужно, чтобы при выборе группы в список попадали все её дочерние элементы, этот код нам не поможет. Нам нужно реализовать разворот иерархии самостоятельно на стороне SQL.
Для того чтобы получить действительно быстрый аналог УложитьСписокОбъектов с поддержкой иерархии, мы создадим собственную процедуру. Мы будем использовать ВыполнитьSQL_ИзТЗ для быстрой вставки исходных данных, а затем запустим цикл на стороне SQL Server, который "раскроет" все группы справочника.
Рассмотрим реализацию этой логики. Мы создадим временную таблицу с полем val для идентификатора объекта и полем isfolder для понимания типа объекта (группа или элемент).
Процедура УложитьСписокОбъектов_Быстро(пСЗ, пИмяТаблицы, пВидСправочника = "")
// Преобразуем список значений в таблицу значений для метода ВыполнитьSQL_ИзТЗ
ТЗ = СоздатьОбъект("ТаблицаЗначений");
пСЗ.Выгрузить(ТЗ);
рс = СоздатьОбъект("ODBCRecordset");
// 1. Создаем временную таблицу
// Используем collate database_default для исключения конфликтов кодировок
рс.ВыполнитьИнструкцию("
|if exists(select * from tempdb..sysobjects where id = object_id('tempdb.." + пИмяТаблицы + "'))
| drop table " + пИмяТаблицы + "
|create table " + пИмяТаблицы + " (val char(9) collate database_default, isfolder tinyint, primary key clustered (val))
|");
// 2. Быстро вставляем исходные данные (по умолчанию помечаем как элементы - 2)
рс.Подготовить("insert into " + пИмяТаблицы + " values (?, 2)");
рс.ДобПараметр(1, 14, 9, 0); // Тип: строка, длина 9
рс.ВыполнитьSQL_ИзТЗ(ТЗ);
// 3. Если указан вид справочника, выполняем разворот иерархии
Если ПустаяСтрока(пВидСправочника) = 0 Тогда
ТекстРазворота = "
|set nocount on
|
|-- Обновляем признак группы для тех ID, которые реально являются группами
|update " + пИмяТаблицы + "
|set isfolder = 1
|from " + пИмяТаблицы + " tab
|inner join $Справочник." + пИвидСправочника + " as ref (nolock) on ref.id = tab.val
|where ref.isfolder = 1
|
|-- Таблица для итерации групп
|declare @folders table(val char(9) collate database_default, primary key clustered (val))
|
|while 1=1 begin
| delete from @folders
|
| -- Ищем группы, которые еще не были обработаны (те, что есть в нашей таблице)
| insert into @folders
| select val from " + пИмяТаблицы + " where isfolder = 1
|
| if @@rowcount = 0 break
|
| -- Добавляем в основную таблицу всех детей найденных групп
| insert into " + пИмяТаблицы + "
| select id, isfolder
| from $Справочник." + пВидСправочника + " (nolock)
| where parentid in (select val from @folders)
| and id not in (select val from " + пИмяТаблицы + ")
|
| -- Удаляем отработанные группы из признака 'isfolder = 1', чтобы цикл не зациклился
| -- (или помечаем их как 'обработанные')
| update " + пИмяТаблицы + " set isfolder = 0 where val in (select val from @folders)
|end
|set nocount off";
рс.ВыполнитьИнструкцию(ТекстРазворота);
КонецЕсли;
КонецПроцедуры
Проанализируем преимущества данного подхода:
ВыполнитьSQL_ИзТЗ.ParentID, чем это делает компонента через объектную модель 1С.char(9), что является стандартом для внутренних идентификаторов 1С 7.7, и используем кластерный индекс для мгновенного поиска.При использовании данного метода обратим внимание на важный аспект: Collation (кодировка). В коде выше мы использовали collate database_default. Это критически важно, так как временные таблицы создаются в базе tempdb, кодировка которой может отличаться от кодировки вашей рабочей базы. Если кодировки не совпадут, SQL Server будет тратить огромные ресурсы на неявное преобразование типов при каждом JOIN, и скорость запроса упадет.
Если вы используете SQL Server 2005 или выше, цикл WHILE можно заменить на рекурсивное обобщенное табличное выражение (Recursive CTE). Это позволит выполнить разворот иерархии еще элегантнее и быстрее, так как движок SQL оптимизирует такой запрос целиком.
Выясним итоговый результат: применение данной методики позволяет сократить время "укладывания" списка из нескольких секунд до 100-200 миллисекунд. Это делает отчеты и обработки гораздо более "отзывчивыми" для конечного пользователя, особенно в крупных информационных базах.