gifts2017

Сведения об ОС - отчет тормозит. Неоптимальный запрос или странное поведение платформы?

Опубликовал Дмитрий Потуроев (dap) в раздел Отчеты - Бухгалтерские

Типовой отчет тормозит неподобно! В случае выполнения отчета с отбором по одному элементу справочника ОС. На больших объемах справочника ОС и при большом количестве бухгалтерских проводок.
Проведу небольшое исследование и исправлю типовой отчет.

Назначение

Функциональность была протестирована и отработана на основе конфигурации Бухгалтерия для Украины 1.2.33.2 и платформы версии 8.3.6.2363. На базе MSSQL

Но данный типовой отчет "СведенияОбОС" с идентичной функциональностью и проблемой встречается в УТП, УПП и прочих типовых конфигурациях на обычных формах.

Рассмотрена любопытная особенность поведения платформы, поэтому будет интересно всем получить небольшой опыт в оптимизации запросов.

История

История обычная,  обращается бухгалтер. Проблема с одной стороны пустяковая, но работать с основными средствами нормально не возможно.  Действительно, проблема встречается редко, т.к. запустить отчет "Сведения об ОС" из карточки справочника (или списка) многим достается редко, некоторые даже не знают о его существовании.

По делу

Есть база, большущая. На MSSQL. Огромное количество ОС в справочнике.  Заходим в справочник Основные средства, и нажимаем кн. "Перейти" - "Сведения об ОС".

Ждем.... Ждем минуту, две ...  - и вуаля, Сведения об ОДНОМ объекте сформированы!  Быстренько. ничего не скажешь.
А работать то надо!

Начинаю ковырять. Добираюсь до запроса, что видим: небольшой запрос, грамотные фильтры, выполненные как отборы построителя (фигурные скобки). Нет правда временных таблицы, ну да ладно, как оказалось это не главное в данной проблеме.

 Запрос

На первый взгляд не удалось обнаружить каких-то  проблем с запросом. Соединений масса, но содержимое виртуальных таблиц при наложении фильтров единичное, что не должно вызывать разрастание объема обрабатываемой информации.

Первое что повело по правильному пути - это понимание того, что отчет формируется одинаково медленно как при отборе по одному элементу основного средства, так и по всем без отборов. Ну почти. разница - не заметна. Получается, фильтры не работают? А уж когда фильтры не работают - вот тогда и получаются множественные соединения больших таблиц, например,  регистра "Хозрасчетный" виртуальная таблица которого огромна по определению, и регистра "СчетаБухгалтерскогоУчетаОС" - содержимое которого массивно при большом количестве ОС.

Для поиска проблемы решил последовательно отрабатывать в консоли запросов все вложенные подзапросы изнутри наружу. Проблема не показалась. Все подзапросы, все варианты соединений их от самых простых до полного запроса в консоли запросов отрабатывались быстро. С отбором по одном объекту естественно.

Вопрос: почему в консоли запросов все работает быстро, в этом отчете тормозит?

Ок, в консоли не было директив построителя - отборов в фигурных скобках. Совпадение, или нет, но в начале я писал о предположении что как раз отборы временных таблиц и не работают. И о чудо!

В тот момент, когда открыл исходный запрос в модуле отчета в конструкторе запросов и не внося изменений, подтвердил и закрыл конструктор - случайно заметил что ПРОПАЛ ФИЛЬТР в подзапросе другого ФИЛЬТРА виртуальной таблицы !!! То, что на рисунке выше выделено красным. Что это означает? То, что  Конструктор запроса отказался принимать эту директиву. Что сразу же навеяло на мысль, что данный фильтр НЕ РАБОТАЕТ!

Вывод созрел. Проблема найдена!

Фильтры виртуальной таблицы, которая использована как подзапрос в Фильтре другой виртуальной таблицы НЕ РАБОТАЮТ. Т.е. такого не бывает, т.е. использование их разработчиками было ошибочным.
Что это? баг платформы или фича?  документация не найдена. Прошу уважаемое сообщество прокомментировать данный факт.

Исправляем

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

Временная таблица:

 временная таблица

Заменяем в подзапросе виртуальную талицу временной:

 использование временной таблицы

Все, проблемы нет, на огромной базе отчет в случае отбора по одному ОС или небольшой группе работает МГНОВЕННО! Потому, что фильтры переданные построителем теперь работают нормально.
Собственно как и обязан.

с уважением.

Скачать файлы

Наименование Файл Версия Размер
Отчет "Сведения ОБ ОС"
.erf 27,55Kb
22.11.15
0
.erf типовой 27,55Kb Скачать

См. также

Подписаться Добавить вознаграждение

Комментарии

1. Sergey Andreev (starik-2005) 22.11.15 14:11
Сдается мне, что если проиндексировать ВТ по счету и вынести подзапрос во ВНУТРЕННЕЕ СОЕДИНЕНИЕ, то может получиться еще быстрее безотносительно количества выбираемых ОС.
2. Дмитрий Потуроев (dap) 22.11.15 15:07
(1) starik-2005, Да, спасибо. Там еще есть над чем поработать. Смотрите, там получается 4 раза выполняется запрос по "СчетаБухгалтерскогоУчетаОС". Там все соединения нужно переделать с использованием ВТ.
Но зачем ... если главная проблема решена ...
3. Антон Стеклов (asved.ru) 24.11.15 06:26
Не факт, что "фильтры не работают" и это ошибка кода. Для того, чтобы в этом убедиться, нужно смотреть на фактические запросы к СУБД и их планы.

Зачастую бывает так, что условие соединения, указанное в фильтре, просто-напросто не оставляет оптимизатору иного выхода, кроме как сканить всю таблицу. Кроме того, для соединения с подзапросом оптимизатор часто не может просчитать статистику, особенно на сложных условиях, и применяет алгоритм соединения nested loops, сильно тормозной на больших количествах строк. И наконец, степень оптимальности планов зависит от состояния базы данных и нагрузки на СУБД.
4. Дмитрий Потуроев (dap) 24.11.15 10:36
(3) asved.ru, Спасибо.
А почему тогда конструктор запроса удаляет именно эти фильтры? Или это его предсказуемое поведение на таких вложенностях?
Или это не имеется значение, 1С передает запрос в БД без предварительного анализа и интерпретации?
Для написания сообщения необходимо авторизоваться
Прикрепить файл
Дополнительные параметры ответа