Как оптимизировать запрос 1с

Обновлено: 07.07.2024

Программа очень медленно работает, невозможно дождаться проведения документа или открытия записи? Счета-фактуры и акты формируются по часу? Непорядок! Не хватает руки мастера 1С. Хорошо если ваш супер программист просто в отпуске, скоро вернётся и исправит ситуацию, но что делать тем компаниях, штатное расписание которых не предполагает наличия программиста? Тут варианта два: либо продолжать мучиться в ожидании проведения каждого документа (честно говоря, совсем не вариант), либо пригласить программиста по 1С со стороны. Чем он может помочь? Прежде всего специалист проведёт анализ программы на предмет правильности написанного кода и запросов, именно оптимизация 1С кода позволяет увеличить быстродействие системы.

Общие правила оптимизации 1С кода

Что требует оптимизации в обязательном порядке?

Существует небольшой перечень проблем, которые требуют выявления и исправления в первую очередь, если программа медленно работает.

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

Но тут не учтён тот факт, что оптимизатор сервера, какую бы СУБД вы не применяли, не во всех случаях имеет возможность грамотно оптимизировать указанный вариант запроса. Результатом будет очень низкое быстродействие при его обработке системой. Обратите внимание, загрузка сервера при обработке в пределах нормы. Вывод: коды 1С требуют оптимизации.

Как оптимизировать большую вложенность подзапросов и их соединения?

Алгоритм оптимизации:

Ищем самый проблемный запрос ? Получаем текст запроса на встроенном языке 1С ? Дублируем ситуации через функционал Консоли запросов ?Анализируем текст запроса

Вот такие конструкции нужно переписать?

Нужно использовать эффективность временных таблиц, получим следующее:

Созданную временную таблицу помещаем в основной запрос:

Если нужно выполнить объединение запросов в единый пакетный запрос.

Как оценить результат оптимизации кода 1С?

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

Выявленные несоответствия между индексами и значимыми условиями запросов.

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

Каждому условию соответствует свой индекс. Идеальный индекс тот, для которого в обязательном порядке выполняются следующие значимые условия:

Пример неэффективного использования:

Неиспользование «ВЫРАЗИТЬ» при введении полей, имеющих составной тип.

Введение «ВЫРАЗИТЬ» исключает соединение таблиц, это попросту больше не нужно.

Пример: если сделать запрос так, то при его исполнении создастся лишнее левое соединение с таблицей «Контрагенты».

Нужно переписать вот так:

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

На данной странице задавайте вопросы по материалам и практическим заданиям девятого модуля курса «Разработка и оптимизация запросов в 1С:Предприятие 8.3».

Практические задания

К сожалению, у Вас недостаточно прав для дальнейшего просмотра.

Если Вы приобрели курс, но еще не активировали токен — пожалуйста, активируйте доступ по инструкциям, высланным на Ваш email после покупки.

Если Вы не залогинены на сайте — залогиньтесь, вернитесь на эту страницу и обновите ее.

Если Вы залогинены, у Вас активирован токен доступа, но Вы все равно видите эту запись — напишите нам на e-mail поддержки.

Комментарии / обсуждение (494):

В конструкторе запроса есть закладка Построитель, а в ней ещё 5 закладок. О ней ничего не говорилось, что это такое?

Вопрос по заданию 34.
Не могу разобраться в предложенных пояснениях.
В обычном приложении создана управляемая форма. На форме Таблица с типом ДеревоЗначений.
1. Прошу дописать код для выгрузки значений в табличную часть.

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

1. Некорректно дерево значений назвала таблицей, т.к. меняла тип с ТаблицыЗначений на ДеревоЗначений. Сканы приложила.
2. Спасибо, обработка КарточкаКонтрагентов.epf подходит, просто не глянула в неё.

Часто при внедрении программ «1С: Предприятие 8» возникают ситуации, в которых простые запросы работают достаточно медленно.

Покажем варианты оптимизации таких запросов.

Для примера рассмотрим запрос из реального проекта (в базе клиента он выполнялся более 6 секунд)

МЕСЯЦ(ДенежныеСредстваКПоступлениюБезналичныеОбороты.Период) КАК Месяц,

ГОД(ДенежныеСредстваКПоступлениюБезналичныеОбороты.Период) КАК Год,

СУММА(ДенежныеСредстваКПоступлениюБезналичныеОбороты.СуммаПриход) КАК СуммаПриход

РегистрНакопления.ДенежныеСредстваКПоступлениюБезналичные.Обороты(, , Месяц, Документ.Контрагент.Партнер = &Партнер)

C первого взгляда все хорошо, но опытный программист увидит неоптимальный код в запросе.

Источником проблем выступает параметр виртуальной таблицы, а точнее – обращение через «две точки» в фильтре .

В общем случае такой подход допустим, но есть одна проблема: поле «Документ» имеет составной тип, и при получении реквизитов данного поля выполняется соединение с каждой таблицей, входящей в составной тип этого поля .

Самым первым вариантом решения в голову приходит использовать конструкцию языка запросов «ВЫРАЗИТЬ», чтобы привести поле «Документ» к некоторому определенному типу. Это позволит избежать соединений с лишними таблицами. Но по ряду ограничений данный вариант не подходит:

Нам нужны все документы, содержащиеся в составном типе. Таковы условия постановки задачи. Получается, что необходимо фильтровать все типы документов, входящие в составной тип.
Даже если бы не было предыдущего ограничения, то обращение через «две точки» никуда не делось.
Если бы можно было использовать «ВЫРАЗИТЬ», то это не спасало бы ситуацию: в параметрах виртуальной таблицы «ВЫРАЗИТЬ» не дает прироста производительности.

Оптимизация

Исходя из вышесказанного, прежде всего необходимо избавиться от обращения через «две точки» и при этом не испортить саму логику нашего запроса.

Из нескольких способов решения задачи предлагаем два следующих варианта:

Вариант 1

В регистр «ДенежныеСредстваКПоступлениюБезналичные» добавить новое измерение «Партнер», заполняя его при записи движений документов. Ввиду использования условия по данному измерению его необходимо проиндексировать.

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

РегистрНакопления.ДенежныеСредстваКПоступлениюБезналичные.Обороты(, , Месяц, Партнер = &Партнер) КАК

Что мы видим? Этот запрос начинает работать моментально. И это, к сожалению, единственный положительный момент, минусов наблюдается существенно больше. Главный минус – изменение структуры конфигурации, возникают проблемы при последующих обновлениях, использовании типовых обменов и т.д. К тому же у нас хранится дублируемая информация, что приводит к увеличению размера таблицы, а установка признака индексирования повышает скорость чтения, но при этом замедляет запись в регистр. Поэтому рассмотрим второй вариант.

Вариант 2

Можно попробовать изменить запрос так, чтобы фильтр по полю «Документ» накладывался примерно следующим образом:

РегистрНакопления.ДенежныеСредстваКПоступлениюБезналичные.Обороты(, , Месяц, Документ В (ВЫБРАТЬ Ссылка ИЗ

ВТ_ДокументыСПартнером)) КАК ДенежныеСредстваКПоступлениюБезналичныеОбороты

Что необходимо сделать, чтобы наш запрос пришел к подобному виду? Вначале соберем все документы, входящие в составной тип поля «Документы». Для них должно соблюдаться условие:

В нашем составном типе определены 5 документов, причем искомый реквизит «Контрагент» присутствует только в документах:

ПоступлениеБезналичныхДенежныхСредств
СписаниеБезналичныхДенежныхСредств
РасходныйКассовыйОрдер
ОперацияПоПлатежнойКарте

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

ИЗ Документ.ОперацияПоПлатежнойКарте КАК ОперацияПоПлатежнойКарте

ГДЕ ОперацияПоПлатежнойКарте.Контрагент.Партнер = &Партнер

ИЗ Документ.ПоступлениеБезналичныхДенежныхСредств КАК ПоступлениеБезналичныхДенежныхСредств

ГДЕ ПоступлениеБезналичныхДенежныхСредств.Контрагент.Партнер = &Партнер

ИЗ Документ.РасходныйКассовыйОрдер КАК РасходныйКассовыйОрдер

ГДЕ РасходныйКассовыйОрдер.Контрагент.Партнер = &Партнер

ИЗ Документ.СписаниеБезналичныхДенежныхСредств КАК СписаниеБезналичныхДенежныхСредств

ГДЕ СписаниеБезналичныхДенежныхСредств.Контрагент.Партнер = &Партнер

МЕСЯЦ(ДенежныеСредстваКПоступлениюБезналичныеОбороты.Период) КАК Месяц,

ГОД(ДенежныеСредстваКПоступлениюБезналичныеОбороты.Период) КАК Год,

ДенежныеСредстваКПоступлениюБезналичныеОбороты.СуммаПриход КАК СуммаПриход

РегистрНакопления.ДенежныеСредстваКПоступлениюБезналичные.Обороты(,, Месяц, Документ В

ВТ_ДокументыСПартнером)) КАК ДенежныеСредстваКПоступлениюБезналичныеОбороты

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

После проведенной оптимизации запрос стал выполняться менее одной секунды! Да, при этом он стал сложнее, но нет необходимости в изменении структуры метаданных, как в первом варианте.

Резюме

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

Рекомендуем оптимизировать запросы посредством изменения текста самого запроса.

Анализ плана выполнения запроса с помощью консоли запросов

В этом видео показан наиболее простой способ получения плана выполнения запроса на СУБД – для этого используется официальная консоль запросов от фирмы «1С».

С помощью этого инструмента можно быстро оценить эффективность выполнения запроса и необходимость его оптимизации.

В SQL Server топ запросов по потреблению ресурсов можно получить из кэша планов запросов или с помощью Query Store.

Первый способ не требует никаких предварительных настроек. DMV sys.dm_exec_query_stats, sys.dm_exec_sql_text и sys.dm_exec_query_plan предоставляют доступ, соответственно, к статистике выполнения, текстам и планам запросов из кэша. К недостаткам этого метода относится то, что в кэше сохраняются не все версии планов запросов, и то, что кэш не предназначен для постоянного хранения и может быть очищен.

Чтобы использовать Query Store его нужно включить, настроить и подождать пока в хранилище накопятся нужные данные.

Коротко рассмотрим оба способа.

Для получения информации о ресурсоемких запросах из кэша планов запросов можно использовать следующий запрос:

Здесь DMV соединяются по идентификатору плана запроса в кэше – plan_handle. Результатом выполнения станет топ запросов по среднему процессорному времени:

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

Второй способ – Query Store, который впервые появился в SQL Server 2016. Включить его можно в окне свойств базы данных или с помощью запроса, например:

Помимо прочих возможностей Query Store позволяет просматривать отчет «Основные запросы, потребляющие ресурсы» («Top Resource Consuming Queries»). Откроем отчет и выберем метрику «Время ЦП (мс)»:

Первым в топе мы видим тот же самый запрос, который ранее мы получили из кэша планов запросов с помощью DMV.

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

В нижней части окна отчета выводится текст запроса и план его исполнения.

Теперь, когда запрос для оптимизации выбран, выясним, что это за запрос. Для того чтобы определить место вызова запроса в конфигурации, настроим технологический журнал 1С следующим образом:

После того, как запрос в очередной раз выполнится, в технологическом журнале мы найдём следующую запись:

В журнале фиксируется контекст исполнения оператора. В данном случае мы имеем дело с одной из процедур регламентного задания отражения документов в регламентированном учете. Этим и объясняется высокая частота выполнения запроса:

Мы видим, что текст запроса перед выполнением модифицируется. Если посмотреть процедуру ДобавитьВЗапросФильтрОтраженияВРеглУчете(), то можно сказать, что это делается с целью повторного использования кода.

Получим окончательный текст запроса с помощью отладчика:

Теперь выясним, как SQL Server выполняет этот запрос, и почему он входит в топ запросов по потреблению ресурсов.

План исполнения запроса, который мы видим в нижней части отчета Query Store – это предполагаемый план (Estimated Plan), сформированный до выполнения запроса. Чтобы дополнить его информацией времени выполнения, т.е. получить фактический план (Actual Plan), воспользуемся Extended Events.

Сеанс Extended Events можно создать с помощью графических средств, но быстрее и проще это сделать с помощью запроса:

Для события query_post_execution_showplan мы установили отбор по имени базы данных и шаблону текста запроса. В параметрах события мы включили дополнительное поле – sqlserver.sql_text (текст запроса).

Из контекстного меню созданного сеанса запустим сеанс и откроем окно событий – «Наблюдать за данными, передаваемыми в режиме реального времени» («Watch Live Data»).

Мы видим, что наш запрос выполняется за 5,7 секунд (поле duration). Оптимизатор запросов оценил его предполагаемую стоимость как 180 (поле estimated_cost) и, в соответствии с настройками параллелизма, для запроса выбрана параллельная версия плана исполнения (поле dop). Для сравнения, порог стоимости плана запроса для использования параллелизма (cost threshold for parallelism) по умолчанию – 5.

Поле sql_text содержит текст запроса. Скопировав его в редактор запросов SSMS можно получить запрос в отформатированном виде:

Обратите внимание на условие отбора по полю Fld1490 для каждой таблицы. Это поле разделителя с типом "ОбщийРеквизит.ОбластьДанныхОсновныеДанные". Платформа 1С неявно подставляет такие условия в запросы для конфигураций, в которых заложена возможность разделения данных, независимо от того, включено разделение или нет. Это относится ко всем конфигурациям, основанным на современных версиях БСП.

Для того, чтобы узнать каким объектам конфигурации соответствуют таблицы и поля базы данных, нужно воспользоваться функцией глобального контекста ПолучитьСтруктуруХраненияБазыДанных(). Результат её выполнения можно посмотреть в отладчике:

Таким же способом можно получить структуру индексов таблиц базы данных:

Первое, что бросается в глаза в плане исполнения нашего запроса – это толщина стрелок, изображающих потоки данных. Мы видим, что вся таблица регистра сведений ОтражениеДокументовВРеглУчете (InfoRg30933), в которой более 1,3 миллионов записей, попарно соединяется с каждой из основных таблиц трех документов, участвующих в запросе. Если быть точным, соединяются не таблицы, а результаты поиска по некластеризованным индексам, где предикатом является значение поля разделителя Fld1490, которое уже было упомянуто выше. Т.к. разделение в базе отключено, селективность этого предиката равна единице, т.е. возвращаются все строки таблиц.

Во всех трех случаях для соединения используется оператор Hash Match. Оптимизатор обычно использует этот оператор для соединения больших входных данных, если они не отсортированы по полям соединения.

При соединении оператором Hash Match вычисляются хеши полей соединения верхних входных данных, называемых «Build». Таблица со значениями полей соединения и их хешами помещается в оперативную память. После этого выполняется построчное чтение нижних входных данных, называемых «Probe». Для каждой строки вычисляется хеш значений полей соединения и сравнивается с хешами в таблице из оперативной памяти. Для эффективного выполнения в качестве «Build» (верхних входных данных) выбирается меньший из двух потоков.

Поля, по которым выполняется соединение, можно увидеть в свойстве «Hash Keys Probe» оператора Hash Match.

В нашем случае стоимость операторам Hash Match увеличивает ещё и дополнительный предикат, соответствие которому должно быть проверено в ходе соединений. Его можно увидеть в свойстве «Probe Residual» операторов:

Из-за объема данных в нижнем потоке эти три оператора Hash Match наиболее затратны по CPU.

Наш запрос входит в топ по процессорному времени и по той причине, что для его исполнения используется параллелизм. Параллелизм уменьшает время выполнения запроса, но увеличивает потребление ресурсов. Если есть необходимость, параллелизм можно отключить не на уровне экземпляра или базы данных, а для конкретного запроса, добавив к запросу «Хинт» (Hint): OPTION (MAXDOP 1). Т.к с помощью средств платформы 1С мы не можем непосредственно изменять sql-запросы, хинт можно добавить в SSMS с помощью т.н. «Структуры планов» (Plan Guide):

Рассмотрим теперь этот фрагмент плана исполнения:

На плане мы видим, что верхний поток, в котором 1,3 млн. записей (результат соединения регистра сведений с документами), соединяется с таблицей константы «ДатаНачалаВеденияРеглУчета» с помощью оператора Nested Loops. В современных версиях платформы 1С каждой константе соответствует своя отдельная таблица базы данных.

Перед соединением на таблицу константы, как и на все другие таблицы, накладывается отбор по значению разделителя. Чтобы не производить поиск по этому значению на каждой итерации Nested Loops, результат поиска в индексе записывается в tempdb c помощью оператора Table Spool.

При выполнении Nested Loops операторы нижней ветки, в данном случае Table Spool, выполняются столько раз, сколько строк содержит верхний входящий поток, в нашем случае более 1,3 млн. раз. Это неоптимальное использование оператора Nested Loops, который эффективен, только если верхний поток содержит сравнительно малое количество строк.

Оператор Nested Loops проверяет условия соединения одним из двух способов. В нашем случае условия соединения находятся в свойстве «Predicate» оператора. Это значит, что оператор Nested Loops отбирает строки, соответствующие условиям, после того как получит значения из внутреннего цикла (нижняя ветка). Значения из верхнего набора строк в нижний не передаются. Нижние входящие данные в нашем случае статичны и возвращают одни и те же значения при каждом выполнении.

Условия соединения могут также проверятся в нижней ветке (во внутренних циклах). В этом случае мы бы увидели условия не в свойстве «Predicate», а в свойстве «Outer References» оператора Nested Loops.

Если мы посмотрим значение свойства «Predicate» оператора Nested Loops в плане исполнения нашего запроса, то мы увидим там все условия из секции «ГДЕ» запроса.

Способ оптимизации этого запроса достаточно прост. Нужно применить отборы как можно раньше, чтобы уменьшить объем соединяемых данных. При этом поиск данных, соответствующих условиям отбора, по возможности должен выполняться с помощью индексов.

Перепишем запрос следующим образом:

Выполним запрос и посмотрим метрики его производительности в Extended Events:

Мы видим, что на этот раз исполнитель запросов счел запрос достаточно простым, чтобы выполнить его на одном логическом процессоре (estimated_cost=0, dop = 1). При этом измененный запрос выполнился за 0.13 секунд, т.е. в 43 раза быстрее оригинального запроса. Процессорное время, затраченное на выполнение (cpu_time), теперь равно 123870 микросекунд, что в 88 раз меньше, чем у оригинального запроса.

Посмотрим теперь на текст sql-запроса и на план его исполнения:

Для реализации логической операции объединения (Union) оптимизитор использовал физический оператор Merge Join, который требует, чтобы входные данные были упорядочены по полям объединения и не содержали дубликатов. Сам оператор Merge Join (Union) исключает из объединения дубликаты строк, которые есть в обоих наборах, но не учитывает дубликаты, которые могут быть в каждом из наборов по отдельности. Поэтому оптимизатор явным образом удаляет из наборов дубликаты либо с помощью оператора Sort (Distinct Sort), либо с помощью оператора Stream Aggregare (если данные в наборе уже упорядочены по нужным полям):

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

В секции «ГДЕ» верхнего запроса мы видим условия отбора по ресурсу «Статус» и по измерению «ДатаОтражения» регистра «ОтражениеДокументовВРеглУчете». Значения параметра &СтатусыОтражения передаются в запрос и объединяются с помощью оператора Concatenation. Ресурс «Статус» регистра проиндексирован, и поиск значений статусов выполняется в этом индексе.

Условие по Статусу высокоселективное: из 1.3 млн. строк выбирается 4 тысячи. Далее эти 4 тысячи строк соединяются с таблицей константы с отбором по Дате отражения, уже без использования индексов:

Используя терминологию из

Наконец, для получения результата последнего из пяти запросов, достаточно выполнить поиск по индексу, т.к. все элементы в секции «ГДЕ», несмотря на то, что они объединены оператором «ИЛИ», накладывают отбор по одному и тому же полю – Регистратору.

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

Оригинальный запрос заменим оптимизированным в расширении конфигурации. В данном случае безопасней всего это сделать, расширив метод «ДобавитьВЗапросФильтрОтраженияВРеглУчете()» следующим образом:

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

В большинстве случаев, как только специалисты сталкиваются с проблемой производительности, они начинают действовать наугад. Путём перебора различных вариантов надеются найти правильный в данной ситуации. Естественно это не самый лучший способ и очень часто задаются вопросом с чего начать и что лучше применить? Какова должна быть последовательность действий при возникновении проблем?

Статьи по оптимизации производительности 1С для программистов

Методика повышения производительности 1С

Итак, с чего начать и как действовать правильно.

Если есть проблема производительности, первое, что необходимо сделать, нужно зафиксировать с помощью технологии APDEX текущую производительность. Узнать на сколько плохо система стала работать. Особенно это важно, если выполняется оптимизация не для своей компании, где вы являетесь штатным сотрудником, а выступаете в качестве внешнего эксперта.

Далее необходимо посмотреть, выполняется ли сервере СУБД регламентные операции, обновляется ли статистика, есть ли дефрагментация индексов? Если регламентные операции не выполняются, то их выполнение нужно настроить, запустить систему и проанализировать, как изменился APDEX. Оцениваем, изменилась ли производительность.

Следующий этап. Если регламентные операции включены и это не помогло, исследуем, выполняется ли эта операция в однопользовательском режиме, редко применимом на практике, или только в многопользовательском режиме. Если операция выполняется медленно и в однопользовательском режиме, то её довольно легко оптимизировать с помощью замера производительности в конфигураторе 1С. А вот если операция выполняется медленно только в многопользовательском режиме, тогда это очевидно проблемы связанные с параллельной работой. Здесь без посторонних инструментом используя один конфигуратор обойтись гораздо сложнее.

После сбора всех необходимый данных смотрим, можно ли ускорить систему без «апгрейда» оборудования. Как это узнать? Если видно, что есть не оптимальные запросы, ожидания на блокировках, то про «апгрейд» пока можно забыть. Есть ещё запас по оптимизации на уровне программного кода.

Если вы только начинаете программировать в 1С или просто хотите систематизировать свои знания - попробуйте Школу программирования 1С нашего друга Владимира Милькина. Пошаговые и понятные уроки даже для новичка с поддержкой учителя.
Попробуйте бесплатно по ссылке >>

Заключительным этапом является регулярный мониторинг производительности. Обязательно должны быть установлены счётчики, которые будут показывать, насколько слажено работает система.

И как можно заметить у этого процесса есть начало, но нет логического конца. Производительность нужно отслеживать постоянно. Не важно, имеются ли в данный момент очевидные проблемы или нет.

Читайте также: