Первая IT лаборатория!
8-800-100-13-05
b2b@silverbulleters.org
b2b@silverbulleters.org
Бог в деталях (128 значений)
Проводил я тут небольшое исследование, ну как исследование, надо было понять почему ведет себя странно запрос в 1С. Точнее даже не запрос – а определённая парадигма при написании кода в одной из команд. В команде сложился определенный стереотип по написанию кода – фильтры на выборки старательно накладывались
в стиле WHERE @SomeOne IN ()
в стиле 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# псевдокод в случае типизации расширится следующими псевдоконструкциями
Ну то есть всем понятно, что в случае если мы хотим сделать установку такого фильтра универсальной – то должны продумать все возможные ситуации с данным фильтром.
Я оставлю за кадром историю с автоматической типизацией ;-).
Но вы должны помнить, что в случае бизнес-приложений с использованием 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 отработают штатно при построении плана запроса
То есть поведение будет ожидаемым, которое вы написали в 1С - соответственно такой запрос будет быстрым в силу "параметризированности" фильтров и оптимизаторы MSSQL, PostgreSQL, OracleDB, IBMDB2 отработают штатно при построении плана запроса
Если в коллекции фильтра больше или равно 128 значений
Перед выполнением запроса платформа 1С создаст временную таблицу методом
CREATE tempTable ##ttNNN
затем в цикле наполнит эту таблицу значениями из коллекции методом
INSERT INTO ##ttNNN VALUES (@p1)
то есть чем больше значений, тем дольше будет наполнятся таблица
и уже потом сделает Вашу выборку создав запрос в стиле
IN(SELECT val FROM ##ttNNN)
причем как вы понимаете индексов на такой временной таблице не будет и здесь всё будет зависеть от конкретной версии движка СУБД.
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 поисковики. Для желающих на досуге почитать интересное, могу сказать что основное время мною было потрачено на вот такую поисковую строку