Как посмотреть и проанализировать план запроса SQL в терминах метаданных 1С?

Каждый разработчик 1С рано или поздно сталкивается с проблемой производительности запросов. Мы пишем лаконичный запрос на декларативном языке 1С, но СУБД (будь то MS SQL Server или PostgreSQL) выполняет его физический аналог. Когда мы пытаемся проанализировать план выполнения этого запроса в стандартных средствах СУБД, перед нами предстает полотно из физических имен таблиц вида _InfoReg12345 и полей вроде _Fld12345_TYPE. Читать такой план крайне затруднительно, а сопоставление физических имен с объектами метаданных 1С вручную занимает огромное количество времени.

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

Почему чтение «сырого» плана SQL вызывает сложности?

Платформа 1С:Предприятие транслирует объекты метаданных в физические таблицы базы данных по определенным правилам. Например:

1. Справочники превращаются в таблицы _ReferenceXX.
2. Документы — в _DocumentXX.
3. Регистры сведений — в _InfoRegXX.
4. Табличные части — в подчиненные таблицы с суффиксом _VTXX.

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

Решение 1: Подсистема «Статистика по запросам MSSQL» в составе «Инструментов разработчика»

Один из самых мощных и проверенных временем инструментов для решения этой задачи — проект «Инструменты разработчика» (ИР). Для глубокого анализа планов запросов в нем предусмотрен специализированный инструмент.

Выясним, как правильно им воспользоваться для получения плана запроса в терминах 1С:

1. Запустим инструмент «Статистика по запросам MSSQL» непосредственно или откроем его через встроенную Консоль запросов ИР.
2. Этот инструмент позволяет перехватывать и собирать статистику выполнения запросов на уровне СУБД MS SQL Server.
3. После выполнения интересующего нас запроса мы получаем детальный план выполнения в формате XML (Showplan XML), который СУБД отдает для анализа.
4. Инструмент автоматически выполняет парсинг этого XML-плана. Он сопоставляет идентификаторы таблиц и полей с метаданными текущей базы данных 1С.
5. В результате мы получаем интерактивное дерево плана запроса, где вместо системных имен СУБД выведены реальные синонимы и имена объектов 1С (например, вместо _Document150 мы увидим Документ.РеализацияТоваровУслуг, а вместо _Fld151RRef — реквизит Контрагент).

Также в составе «Инструментов разработчика» есть подсистема «Анализ технологического журнала». Она позволяет импортировать файлы технологического журнала 1С, содержащие планы запросов СУБД, и автоматически декодировать их в терминах метаданных 1С.

Решение 2: Внешняя обработка «Управляемая консоль отчетов» (УКО)

Если вы ищете удобное решение для управляемого приложения, обратите внимание на подсистему анализа планов запросов в составе «Управляемой консоли отчетов» (УКО). Этот инструмент работает на основе парсинга планов, полученных из технологического журнала (ТЖ) 1С. Для интерактивной работы с планами выполнения и структурой запросов подойдёт управляемая консоль запросов и отчетов с анализом плана запроса.

Рассмотрим подробнее ключевые возможности этого решения:

• Интерактивный маппинг в один клик: Реализована кнопка быстрого переключения между физическими именами таблиц/колонок СУБД и терминами метаданных конфигурации 1С. Вы можете мгновенно переключать представление плана, чтобы сопоставить физическую картину выполнения с кодом на языке запросов 1С.
• Автоматическая подсветка «узких мест»: Инструмент анализирует стоимость операций в плане и автоматически выделяет жирным шрифтом самые тяжелые узлы (25% наиболее ресурсоемких операций). Это позволяет сразу сфокусировать внимание на проблемных местах (например, сканировании таблицы вместо поиска по индексу).
• Сравнение план/факт: Если фактическое количество обработанных СУБД строк отличается от прогнозного значения оптимизатора более чем в два раза, УКО подсвечивает эти строки красным цветом. Это верный признак того, что статистика СУБД по данным таблицам устарела и ее необходимо обновить.
• Кроссплатформенность СУБД: Инструмент отлично справляется с планами как для MS SQL Server, так и для PostgreSQL, получая их из ТЖ.

Решение 3: Встроенные возможности современной «Консоли запросов» от фирмы «1С»

Не все знают, что современная стандартная обработка «Консоль запросов» для управляемого приложения (доступная на ИТС) уже имеет базовый встроенный функционал для работы с планами выполнения:

• При работе с MS SQL Server консоль умеет запрашивать план выполнения и выводить его в виде интерактивного дерева внутри интерфейса 1С.
• В настройках отображения плана можно переключать режим представления текста запроса: вы можете видеть оригинальный SQL-текст или транслированный текст, где физические таблицы заменены на имена объектов метаданных 1С.
• Для СУБД PostgreSQL консоль выводит текстовый план выполнения команды EXPLAIN, полученный через технологический журнал платформы.

Как настроить технологический журнал для получения планов запросов?

Чтобы сторонние инструменты (такие как УКО или Анализ ТЖ в ИР) могли визуализировать планы, нам необходимо предварительно собрать их с помощью технологического журнала 1С. Разберем по шагам, как настроить файл конфигурации logcfg.xml для сбора планов выполнения.

Создадим файл конфигурации со следующим содержимым:

<config xmlns="http://v8.1c.ru/v8/tech-log">
    <log location="C:\v8_logs\query_plans" history="12">
        <event filter="dbmssql">
            <eq property="Name" value="DBMSSQL"/>
            <nonempty property="plansql"/>
        </event>
        <event filter="dbpostgrs">
            <eq property="Name" value="DBPOSTGRS"/>
            <nonempty property="plansql"/>
        </event>
        <property name="all"/>
    </log>
</config>

Проанализируем эту настройку:

1. Мы указали сбор событий DBMSSQL (для MS SQL Server) и DBPOSTGRS (для PostgreSQL).
2. Фильтр nonempty property="plansql" указывает платформе, что в журнал нужно записывать только те события СУБД, для которых был успешно построен и зафиксирован план выполнения запроса.
3. Файлы логов будут складываться в каталог C:\v8_logs\query_plans и храниться там 12 часов.

После того как файлы ТЖ будут сформированы, вы сможете загрузить их в «Управляемую консоль отчетов» или «Инструменты разработчика», где они автоматически преобразуются в термины метаданных 1С.

Особенности анализа планов для СУБД PostgreSQL

С переходом многих систем 1С на СУБД PostgreSQL классические методы трассировки MS SQL уступили место анализу планов PostgreSQL. Здесь есть своя специфика:

• План выполнения PostgreSQL получается с помощью команды EXPLAIN (ANALYZE, BUFFERS, FORMAT XML) или в текстовом виде.
• Поскольку СУБД PostgreSQL чувствительна к неоптимальным соединениям и устаревшей статистике, крайне важно отслеживать операции типа Seq Scan (последовательное чтение всей таблицы) на крупных таблицах метаданных 1С.
• Для визуализации планов PostgreSQL разработчики часто используют внешние бесплатные визуализаторы (например, PG Plan Insight или Explain Dalibo). Чтобы эффективно работать с ними, сначала прогоните текст SQL-запроса через транслятор метаданных в УКО или ИР, чтобы заменить абстрактные имена таблиц на понятные вам объекты 1С, а затем загружайте план в визуализатор.

Программный маппинг метаданных для собственных инструментов

Если вы хотите написать собственную обработку или скрипт для автоматического декодирования сырых SQL-планов, вам потребуется механизм сопоставления имен. На сегодняшний день в сообществе 1С-разработчиков активно используются специализированные библиотеки с открытым исходным кодом, например DaJet.Metadata.

Посмотрим, как устроен этот процесс изнутри:

• Библиотека напрямую считывает структуру метаданных из системных таблиц СУБД (таблица Params, файл DBNames и таблица Config).
• Это позволяет за доли секунды без запуска сеанса 1С и использования COM-соединения сопоставить любые физические таблицы (например, _InfoReg120) и их поля (_Fld121RRef) с реальными объектами конфигурации и их реквизитами.
• Вы можете использовать этот подход для создания корпоративного парсера планов запросов, который будет автоматически присылать отчеты об оптимизации в чат разработчиков. Для автоматизации рассылки таких уведомлений настраивается платформа для роботизации процессов OneRPA.

Резюме и рекомендации

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

1. Для быстрого интерактивного анализа при разработке новых запросов в управляемом приложении используйте «Управляемую консоль отчетов». Она наглядно подсветит самые тяжелые операции и покажет расхождения в статистике строк. Для очистки базы от лишних данных в проблемных узлах применяется выборочное удаление организации из 1С.
2. Для глубокого расследования проблем на продуктивной базе под управлением MS SQL Server незаменимым инструментом будет подсистема «Статистика по запросам MSSQL» в «Инструментах разработчика». Для контроля корректности данных после доработки кода и оптимизации запросов применяется автоматическая сверка бухгалтерских проводок.
3. При оптимизации запросов на PostgreSQL настройте сбор технологического журнала свойством plansql и используйте парсеры метаданных для удобной трансляции физических имен перед отправкой планов во внешние визуализаторы.