Ускорение работы формы выгрузки платежей в банк
Ускорение работы формы выгрузки платежей в банк (обработка Клиент банка) с помощью оптимизации запроса в 1С:ERP 2.4

Станислав Песков
Руководитель проектов, эксперт по технологическим вопросам

Добрый день! Мы снова на связи.
Делимся результатами своей работы по ускорению 1С. Нам удалось увеличить скорость заполнения списка документов в 1С:ERP более, чем в 3 раза!
Интересно? Сейчас расскажем.

Сокращения
ТЖ – Технологический журнал
1С:ЦУП – 1С:Центр управления производительностью
ИБ – информационная база
БСП – библиотека стандартных подсистем
КО – ключевая операция
RLS – Row-Level Security, ограничение прав пользователей на уровне записей

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

В предыдущей статье мы рассматривали, как можно собрать информацию по медленным операциям с помощью ТЖ. Например, по собранным данным в ЦУП можно увидеть проблемы производительности в разрезе пользователей. Затем можно связаться с пользователями и уточнить, есть ли жалобы на скорость работы и какие конкретно операции работают медленно.
Определение ключевых операций и выполнение замеров
Итак, на предыдущем шаге мы уточнили ключевых пользователей. Связались с пользователем и подтвердили информацию о существовании жалоб по скорости работы, запланировали удаленное подключение и демонстрацию операций, которые работают медленно.

Рекомендуем произвести запись демонстрации, чтобы при необходимости можно было более подробно разобрать видео, выполнить замер операции секундомером, если он не выполняется подсистемой БСП Оценка производительности.

Перед демонстрацией включим сбор ТЖ. Пример конфигурационного файла logcfg.xml представлен ниже. Более подробно о настройках сбора ТЖ рассказано в предыдущей статье.
<?xml version="1.0"?>
<ns:config xmlns:ns="http://v8.1c.ru/v8/tech-log">
    <ns:log location="F:\LOGS\P_ERPtest_ForCup\all" history="4">
        <ns:event>
            <ns:eq property="Name" value="CALL"/>
            <ns:eq property="p:processName" value="P_ERP_TEST"/>
            <ns:ge property="Duration" value="50000"/>
        </ns:event>
        <ns:event>
            <ns:eq property="Name" value="TLOCK"/>
            <ns:eq property="p:processName" value="P_ERP_TEST"/>
        </ns:event>
        <ns:event>
            <ns:eq property="Name" value="TDEADLOCK"/>
            <ns:eq property="p:processName" value="P_ERP_TEST"/>
        </ns:event>
        <ns:event>
            <ns:eq property="Name" value="DBMSSQL"/>
            <ns:eq property="p:processName" value="P_ERP_TEST"/>
        </ns:event>
        <ns:event>
            <ns:eq property="Name" value="SDBL"/>
            <ns:eq property="p:processName" value="P_ERP_TEST"/>
        </ns:event>
		<ns:property name="all"/>
    </ns:log>
    <ns:plansql/>
    <ns:dbmslocks/>
</ns:config>
От пользователя получили замечания по скорости работы обработки выгрузки платежей в банк. В ERP открывается через меню Казначейство > Безналичные платежи > Выгрузка и загрузка платежей > Выгрузка в банк.

Повисания происходят при выборе банковских счетов, при выборе периода платежей. То есть при заполнении списка платежных документов для выгрузки.

Произведем моделирование ключевой операции на тестовой базе и выполним замеры производительности конфигуратором под пользователем с полными правами и ограниченными.
Замеры конфигуратором показали, что наибольшее время уходит на выполнение запроса:
     Таблица = Запрос.Выполнить().Выгрузить();

При этом у пользователя с ограниченными правами на это тратится 95% времени от всего выполнения операции.

Время выполнения по секундомеру: у полных прав 1-2 сек, у ограниченных 16 сек.

Оказалось, что данная ключевая операция не записывается в регистр «Замеры времени». Добавим необходимые строки кода для записи времени. Это необходимо для сбора статистики до и после оптимизации КО.

После применения убедимся, что замеры записываются в регистр. Обратите внимание на большую разницу во времени выполнения операции под полными и ограниченными правами.


Попробуем разобраться, почему запрос под пользователем с ограниченными правами выполняется в 10 и более раз медленнее.

Анализ запроса и плана выполнения запроса

Анализ с помощью ЦУП
Загрузим ТЖ, собранный во время демонстрации, в ЦУП и выполним анализ производительности. в предыдущей статье подробнее описано, как это делать. Остановимся на результатах. В форме анализа проблем производительности видим на первом месте строку, знакомую нам по результатам замера конфигуратором:

Таблица = Запрос.Выполнить().Выгрузить();

Кликнем два раза на строке «Выполнение запросов» для открытия формы анализа выполнения запросов. Далее кликаем дважды на сам запрос.

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

На второй закладке отображается запрос SQL, а также запрос SQL в терминах метаданных 1С. Можно увидеть, как преобразовался исходный запрос 1С в запрос SQL. В нашем случае добавилось огромное количество соединений к метаданным, отвечающих за RLS (ограничение прав на уровне записей).

Например, справочник ГруппыДоступа, регистры сведений.

На третьей закладке можно увидеть план запроса. План выполнения запроса – это последовательность операций СУБД, необходимых для получения результата запроса SQL. Подробности здесь:

https://ru.wikipedia.org/wiki/План_выполнения_запроса

https://docs.microsoft.com/ru-ru/sql/relational-databases/performance/execution-plans?view=sql-server-ver15

В нашем случае видим огромное количество Nested Loops (или «вложенные циклы» - оператор соединения, который проходит все записи в первой и второй таблице и проверяет условие соединения). То есть выполняется большое количество вложенных циклов внутри других вложенных циклов и так далее.
При разборе плана запроса и преобразования имен таблиц SQL в названия метаданных 1С можно выяснить, что все это огромное число соединений осуществляется со справочниками и регистрами сведений ГруппыДоступа* (список представлен на скриншоте выше).

Таким образом ограничения прав на уровне записей оказали существенное влияние на выполнение этого запроса. Поэтому у пользователя с ограниченными правами запрос выполняется значительно дольше. Попробуем выяснить причины огромного количества соединений из-за RLS и оптимизировать запрос.

Получение плана запроса в SQL Profiler
Чтобы было удобнее собирать трассировку скопируем текст запроса из конфигуратора в консоль запросов. Для этого можно установить точку останова в уже известной нам строке:

Таблица = Запрос.Выполнить().Выгрузить();

Затем открыть форму выгрузки платежей в банк в режиме 1С:Предприятия и когда точка сработает вызвать окно вычисления выражения (Shift+F9). В окне вывести значение выражения «Запрос.Текст» и скопировать в консоль запросов.
В консоли запросов необходимо заполнить параметры (их не так много) и выполнить запрос для проверки. Список документов выводится и совпадает с формой выгрузки платежей в банк. Оставим консоль открытой.

Для получения графического представления плана выполнения запроса выполним трассировку запроса средствами SQL Server Profiler. После подключения к SQL серверу запустим новую трассировку. В общих свойствах необходимо установить флаг «Сохранить в файл» и «Сервер обрабатывает данные трассировки». Это необходимо для сохранения объемного плана запроса, на сбор которого требуются значительные ресурсы.

На второй закладке добавим события Showplan Statistics Profile и Showplan XML Statistics Profile для сбора текстового и графического представления плана запроса. Для этого необходимо установить флаг «Показать все события».

Также необходимо добавить столбец DatabaseName, чтобы была возможность установить фильтр на имя базы данных.

После заполнения настроек необходимо нажать Запустить. Трассировка запущена, необходимо перейти в консоль запросов 1С и выполнить запрос. После выполнения вернуться в SQL Profiler и остановить трассировку.

На экране мы видим собранные данные, но по самому тяжелому запросу вместо плана выполнения запроса выводится Trace skipped Records. Это происходит, когда на сбор данных требуются значительные ресурсы SQL Server. Поэтому мы сохраняли трассировку в файл.

Открыв сохраненные данные можно просмотреть собранные планы выполнения запроса в текстовом и графическом виде. Текстовое представление аналогично тому, что мы видели в ЦУП.

В графическом варианте более наглядно видно большое количество соединений.

Но, как мы помним, под полными правами запрос выполняется достаточно быстро. Значительное замедление получается под ограниченными правами.

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

Судя по значительной деградации производительности выполнения запроса данный план построен неоптимально и требуется переработка текста запроса в 1С.

Промежуточные итоги

Мы выяснили, что при заполнении списка документов в форме выгрузки платежей в банк большая часть времени тратится на выполнение запроса. Произвели анализ запроса и сделали выводы, что план выполнения запроса, формируемый под ограниченными правами, построен неоптимально.

При работе механизмов RLS платформа добавляет в запросы конструкции, прописанные в полях ограничений доступа ролей. В нашем случае в исходном осуществляется большое количество явных и неявных соединений к справочникам и документам, на которые наложены ограничения доступа в ролях. Платформа при каждом таком соединении добавляет дополнительные соединения. Запрос значительно усложняется.
Исходя из сделанных выводом предлагаем следующие варианты решения:

1. Оптимизировать текст исходного запроса таким образом, чтобы соединений с ограниченными метаданными было меньше, выделить общие структуры в запросе во временные таблицы.

2. Создать для пользователей новые роли с более простыми условиями ограничения доступа.

3. Выполнять запрос в привилегированном режиме, но доработать установку необходимых отборов, чтобы он возвращал только допустимые данные.
Оптимизация запроса

У каждого из вариантов решения есть свои преимущества и недостатки. Не будем останавливаться на них, в рамках данной статьи рассмотрим первый вариант решения – оптимизация запроса.

В предыдущих разделах мы выяснили, что в сложный запрос под ограниченными правами добавляется большое количество соединений, формируется тяжелый план запроса, время выполнения вырастает более, чем в 10 раз (по сравнению с полными правами).

Заметим, что в последнем пакете осуществляется объединение результатов 6 запросов.
Выделим данные, к которым производится обращение в каждом из 6 запросов, во временные таблицы. Таким образом, сложные конструкции RLS будут добавляться меньшее количество раз.
Проверим это на практике, выполнив запросы в консоли запросов

Исходный запрос (фрагмент)
ВЫБРАТЬ РАЗРЕШЕННЫЕ
	БезналичныеДенежныеСредства.Регистратор КАК Ссылка
ПОМЕСТИТЬ ПлатежныеПоручения
ИЗ
	РегистрНакопления.ДенежныеСредстваБезналичные КАК БезналичныеДенежныеСредства
		ВНУТРЕННЕЕ СОЕДИНЕНИЕ Документ.СписаниеБезналичныхДенежныхСредств КАК ДанныеДокумента
		ПО (ДанныеДокумента.Ссылка = БезналичныеДенежныеСредства.Регистратор)
ГДЕ
	БезналичныеДенежныеСредства.КСписанию <> 0
	И (БезналичныеДенежныеСредства.Период МЕЖДУ &ДатаНачала И &ДатаКонца
			ИЛИ &ДатаКонца = ДАТАВРЕМЯ(1, 1, 1)
				И БезналичныеДенежныеСредства.Период >= &ДатаНачала)
	И ДанныеДокумента.Проведен
	И НЕ ДанныеДокумента.ПроведеноБанком
	И ДанныеДокумента.ТипПлатежногоДокумента В (ЗНАЧЕНИЕ(Перечисление.ТипыПлатежныхДокументов.ПлатежноеПоручение), ЗНАЧЕНИЕ(Перечисление.ТипыПлатежныхДокументов.РаспоряжениеОбОбязательнойПродаже))
	И ДанныеДокумента.БанковскийСчет В(&МассивСчетов)
	И (ДанныеДокумента.ДатаВыгрузки = ДАТАВРЕМЯ(1, 1, 1)
			ИЛИ НЕ &ТолькоНевыгруженные)

СГРУППИРОВАТЬ ПО
	БезналичныеДенежныеСредства.Регистратор
;

////////////////////////////////////////////////////////////////////////////////
Новый запрос (фрагмент)
…………………
	КонтактнаяИнформацияКонтрагента.Страна КАК КонтактнаяИнформацияКонтрагентаСтрана,
	КонтактнаяИнформацияКонтрагента.Регион КАК КонтактнаяИнформацияКонтрагентаРегион,
	КонтактнаяИнформацияКонтрагента.Город КАК КонтактнаяИнформацияКонтрагентаГород
ПОМЕСТИТЬ БанковскиеСчетаКонтрагентов
ИЗ
	Справочник.БанковскиеСчетаКонтрагентов КАК БанковскиеСчетаКонтрагентов
		ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Контрагенты.КонтактнаяИнформация КАК КонтактнаяИнформацияКонтрагента
		ПО (КонтактнаяИнформацияКонтрагента.Ссылка = БанковскиеСчетаКонтрагентов.Владелец)
			И (КонтактнаяИнформацияКонтрагента.Вид = ЗНАЧЕНИЕ(Справочник.ВидыКонтактнойИнформации.МеждународныйАдресКонтрагента))

ИНДЕКСИРОВАТЬ ПО
	Ссылка
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ РАЗРЕШЕННЫЕ
	БанковскийСчетОрганизации.Ссылка КАК Ссылка,
	БанковскийСчетОрганизации.Владелец КАК Владелец,
	БанковскийСчетОрганизации.Владелец.Наименование КАК ВладелецНаименование,
..................................
Хотите получить полный текст запроса?
Оставьте контакты, и мы свяжемся с вами в ближайшее время
Нажимая на кнопку, вы даете согласие на обработку персональных данных и соглашаетесь c политикой конфиденциальности

План выполнения запроса упростился, выполним замеры времени под пользователями с ограниченными правами.

Под пользователем с ограниченными правами в тестовой базе запрос до изменений выполнялся 35 секунд, после изменений 5 секунд.

Таким образом, в результате оптимизации скорость выполнения запроса ускорилась в 7 раз.
После внедрения изменений в продуктивную базу и сбора статистики скорость заполнения таблицы документов увеличилась более чем в 3 раза!

До оптимизации таблица документов заполнялась в среднем - 15 секунд,

после оптимизации запроса - 4 секунды.

Остались вопросы? Свяжитесь с нами!
Даже если вы просто спросите, как у нас дела, нам будет приятно :)
Телефон: 8 (495) 505 63 78
Почта: cloud@1cniki.ru
Москва, ул. Большая Новодмитровская, д. 36, стр. 12, вход 6 Дизайн-завод FLACON


Политика конфиденциальности
Нажимая на кнопку, вы даете согласие на обработку персональных данных и соглашаетесь c политикой конфиденциальности.