Бог в деталях (128 значений)
Проводил я тут небольшое исследование, ну как исследование, надо было понять почему ведет себя странно запрос в 1С. Точнее даже не запрос – а определённая парадигма при написании кода в одной из команд. В команде сложился определенный стереотип по написанию кода – фильтры на выборки старательно накладывались в стиле WHERE @SomeOne IN ()
Для не знакомых с миром 1С, поясню – в нём доступно следующее поведение алгоритма:

«Прочитать данные из СУБД в переменную на сервере приложений»
«Наложить данные в качестве фильтра на следующую выборку из СУБД»
«Сохранить результат последней выборки»
для тех кто помнит 1С++ и ODBC Recordset
Установить параметр запроса
Если анализировать этот алгоритм и его поведение, то на псевдоязыке эта конструкция может быть записана следующим образом
recordSetOfParams = Querry.Invoke("SELECT val FROM table1");

listOfParams = recordSetOfParams.GetList("val");

Querry.setNewQuerryText("SELECT * FROM t2 WHERE col1 IN (&filter)");

Querry.SetParams("filter", listOfParams);
То есть, если описать человеческим языком - мы можем сделать на языке 1С такое поведение, когда с сервера приложений программист 1С сможет передавать коллекции в СУБД для того чтобы использовать эти коллекции в качестве фильтров
На языке 1С это выглядит так
реальный пример кода из системы проектирования прикладных решений



ЗапросВыборка = Новый Запрос(

"ВЫБРАТЬ РАЗРЕШЕННЫЕ
|	ОбъектыМетаданных.Ссылка
|ИЗ
|	Справочник.ОбъектыМетаданных КАК ОбъектыМетаданных
|ГДЕ
|         ОбъектыМетаданных.Владелец В (&МассивПроектов)"

);
	
ЗапросВыборка.УстановитьПараметр("МассивПроектов", МассивПроектов);
Нас интересует код SDBL (языка запросов 1С)
И (ОбъектыМетаданных.Владелец В (&МассивПроектов)

и метод установки параметра &МассивПроектов

ЗапросВыборка.УстановитьПараметр(
"МассивПроектов", МассивПроектов
);


Вот этот вот метод УстановитьПараметр и имеет для нас ключевую проблему. Дело в том что массив проектов заполняется выше по стеку на основе справочника Технические проекты, размер которого в общем случае будет бесконечным, то есть расширяемым пользователем.
Каким образом передать фильтр в запрос СУБД, если в списке фильтра очень много значений ?
Ну то есть всем понятно, что в случае если мы хотим сделать установку такого фильтра универсальной – то должны продумать все возможные ситуации с данным фильтром.

Я оставлю за кадром историю с автоматической типизацией ;-).

Но вы должны помнить, что в случае бизнес-приложений с использованием ORM, вам в таком случае необходимо добавлять определение типов как в момент выборки данных из СУБД, так и в момент отправки фильтра. Для знатоков Java,C# псевдокод в случае типизации расширится следующими псевдоконструкциями
Добавляем типизации фильтра
код на псевдоязыке в Java стиле
recordSetOfParams = Querry.Invoke(SELECT val FROM table1);

listOfParams <org.example.model.Value> 
= recordSetOfParams.GetList('val') <org.example.model.Value>;

Querry.setNewQuerryText("SELECT * FROM t2 WHERE col1 IN(&filter)")

Querry.SetParams('filter', listOfParams, org.example.model.Value.class);
Но вернемся к 1С. Итак, исходная проблема – в определённых случаях при наложении фильтра в виде Списка значений (Коллекции), возникают необъяснимые задержки при выполнении запросов. Как вы думаете почему ?
списки значений это коллекции
в SQL в зависимости от конкретного вендора СУБД, различается максимальное значение параметров передаваемое в конструкцию IN (1,2,3 ...)
Чтобы скрыть это от Вас, платформа 1С поддержвает 2 разных поведения
Если в фильтре меньше 128 значений
на SQL будет отправлен запрос с конструкцией IN(@p1, @p2..., @p127)

То есть поведение будет ожидаемым, которое вы написали в 1С - соответственно такой запрос будет быстрым в силу "параметризированности" фильтров и оптимизаторы MSSQL, PostgreSQL, OracleDB, IBMDB2 отработают штатно при построении плана запроса
Если в коллекции фильтра больше или равно 128 значений
Перед выполнением запроса платформа 1С создаст временную таблицу методом

CREATE tempTable ##ttNNN

затем в цикле наполнит эту таблицу значениями из коллекции методом

INSERT INTO ##ttNNN VALUES (@p1)

то есть чем больше значений, тем дольше будет наполнятся таблица

и уже потом сделает Вашу выборку создав запрос в стиле

IN(SELECT val FROM ##ttNNN)

причем как вы понимаете индексов на такой временной таблице не будет и здесь всё будет зависеть от конкретной версии движка СУБД.
— А почему 128 ?
— Это история родом из 1999 года, когда авторы OracleDB заявили что будут поддерживать только 128 значений в конструкции IN, так как для такой конструкции им пришлось написать большой CASE и для хранения этого операнда они выделили 256 байт по 2 байта на каждый CASE. Для обоснования этого было заявлено, что наличии в фильтре больше 128 значений означает неверную логику в зоне приложения, поэтому "исправляйте приложение". И хотя сегодня уже не 1999 год, и у самого же Oracle предел уже повышен до "тысяч" параметров, 1С в целях обеспечения обратной совместимости, пока остается в этом ограничении.
как исправлять и как писать правильно
Что же делать ?
1
Ну во первых учитывайте это ограничение при написании кода, например на SonarQube плагин проверяет такие конструкции и считает их недочетом проектирования - то есть техническим долгом.
2
Во вторых - в случаях необъяснимых задержек запросов в которых используется такой фильтр, посмотрите нет ли у Вас на сервере СУБД высоких нагрузок в зоне временных таблиц (tempDB для MSSQL) и (./tmp директорию для PostgreSQL)
3
Ну и в третьих - рефакторьте код ;-). Например так
Пример итогового запроса с разделением массивов на 128 значений
один из способов рефакторинга, когда не хочется менять бизнес-логику, как разделить коллекции на несколько параметров-массивов с 128 значениями кодом 1С - это Вам домашнее задание
ВЫБРАТЬ РАЗРЕШЕННЫЕ
      ОбъектыМетаданных.Ссылка
ИЗ
      Справочник.ОбъектыМетаданных КАК ОбъектыМетаданных
ГДЕ

ОбъектыМетаданных.Владелец В (&МассивПроектов1Со128Значениями)
ИЛИ
ОбъектыМетаданных.Владелец В (&МассивПроектов2Со128Значениями)
ИЛИ
ОбъектыМетаданных.Владелец В (&МассивПроектов3Со128Значениями)
Если кому-нибудь кажется, что данное R&D заняло очень много времени – то могу вам сказать, что это не так. Обычно для таких целей я использую консоль кода с трассировкой SQL и Google&Yandex поисковики. Для желающих на досуге почитать интересное, могу сказать что основное время мною было потрачено на вот такую поисковую строку
Поделиться