При проектировании сложных учетных систем на платформе 1С разработчики часто сталкиваются с иерархическими связями между документами. Типичный пример такой задачи — цепочка документов в торговле или снабжении. Представим документ СчетНаОплатуПоставщика, в табличной части которого под названием Поступления хранится перечень ссылок на фактические документы поступления — Документ.ПоступлениеТоваровУслуг. Нам необходимо написать запрос, который обратится к табличной части Товары каждого из этих документов поступления, выберет оттуда номенклатуру, рассчитает общее количество с учетом коэффициентов упаковок и сгруппирует результат.
В этой статье мы подробно разберем, как решить эту задачу с помощью языка запросов 1С, проанализируем базовый рабочий вариант, выявим его критические проблемы с производительностью и построим идеальный, полностью оптимизированный пакетный запрос, готовый к работе на высоконагруженных базах данных.
Для начала проанализируем самое простое и интуитивное решение, которое часто выбирают начинающие разработчики. Чтобы получить данные из подчиненной табличной части, нам нужно связать две физические таблицы СУБД:
Документ.СчетНаОплатуПоставщика.Поступления.Документ.ПоступлениеТоваровУслуг.Товары.Связующим звеном выступает реквизит Документ в первой табличной части и реквизит Ссылка во второй табличной части. Посмотрим на базовый вариант запроса, который решает эту задачу напрямую:
ВЫБРАТЬ
ПоступлениеТовары.Номенклатура КАК Номенклатура,
СУММА(ПоступлениеТовары.Количество * ПоступлениеТовары.Коэффициент) КАК Количество
ИЗ
Документ.ПоступлениеТоваровУслуг.Товары КАК ПоступлениеТовары
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Документ.СчетНаОплатуПоставщика.Поступления КАК СчетПоставщикаПоступления
ПО ПоступлениеТовары.Ссылка = СчетПоставщикаПоступления.Документ
ГДЕ
СчетПоставщикаПоступления.Ссылка.Проведен
И СчетПоставщикаПоступления.Ссылка.Дата >= &НачалоПериода
И ПоступлениеТовары.Номенклатура.Родитель В(&Группы)
И СчетПоставщикаПоступления.Ссылка.СтатусПоступления <> ЗНАЧЕНИЕ(Перечисление.СтатусыСчетовНаОплату.Получен)
СГРУППИРОВАТЬ ПО
ПоступлениеТовары.Номенклатура
Этот запрос будет работать и вернет правильные данные на небольших тестовых базах. Однако при переносе его в реальную рабочую базу (особенно на СУБД MS SQL Server или PostgreSQL с объемом данных в сотни гигабайт) система начнет испытывать колоссальные перегрузки. Выясним основные причины неоптимальности этой конструкции.
Проанализируем детально, почему прямое соединение табличных частей «в лоб» является грубой ошибкой с точки зрения стандартов разработки фирмы «1С».
Во-первых, это прямое соединение двух физических таблиц большого объема. Табличные части документов — это одни из самых нагруженных таблиц в базе данных. Когда СУБД выполняет внутреннее соединение (INNER JOIN) таблицы товаров всех поступлений с таблицей строк всех счетов, ей приходится сопоставлять миллионы записей. Без предварительной фильтрации СУБД вынуждена использовать неоптимальные алгоритмы соединения (например, Nested Loops или Hash Match большого объема), что приводит к высокой нагрузке на процессор сервера и дисковую подсистему.
Во-вторых, в запросе присутствуют неявные левые соединения (обращения через точку). В секции ГДЕ мы видим конструкции:
СчетПоставщикаПоступления.Ссылка.ПроведенСчетПоставщикаПоступления.Ссылка.ДатаСчетПоставщикаПоступления.Ссылка.СтатусПоступленияКаждое такое обращение заставляет платформу 1С неявно генерировать дополнительное левое соединение (LEFT JOIN) с таблицей шапки документа СчетНаОплатуПоставщика на уровне SQL-запроса. СУБД вынуждена «подтягивать» шапку для каждой строки табличной части, что критически замедляет выполнение операции.
В-третьих, это проблема составного типа данных. Если реквизит Документ в табличной части Поступления имеет составной тип (например, помимо поступления товаров туда можно выбрать акт выполненных работ или поступление доп. расходов), СУБД сгенерирует сложнейший SQL-код с объединением (UNION) всех возможных таблиц документов. Это приводит к полному сканированию таблиц (Table Scan) и делает невозможным использование индексов.
В-четвертых, некорректная работа с иерархией справочников. Условие ПоступлениеТовары.Номенклатура.Родитель В(&Группы) проверяет только непосредственного родителя элемента. Если в справочнике Номенклатура глубина вложенности папок больше двух, товары из глубоких подпапок просто не попадут в отчет. К тому же, обращение к реквизиту Родитель через точку создает еще одно неявное соединение таблиц.
Чтобы обойти все вышеописанные узкие места, перепишем запрос в соответствии со стандартами высокой производительности 1С. Мы применим технологию пакетных запросов и временных таблиц.
Разберем наш план оптимизации по шагам:
СчетНаОплатуПоставщика, соответствующие условиям по дате, проведению и статусу.ВЫРАЗИТЬ к полю Документ, чтобы СУБД точно знала, с какой именно физической таблицей нужно будет делать соединение во втором запросе пакета. Это полностью нивелирует проблему составных типов данных.ВТ_ОтобранныеДокументы.В ИЕРАРХИИ.Рассмотрим структуру готового оптимизированного пакета запросов:
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| ВЫРАЗИТЬ(СчетПоступления.Документ КАК Документ.ПоступлениеТоваровУслуг) КАК ДокументПоступления
|ПОМЕСТИТЬ ВТ_ОтобранныеДокументы
|ИЗ
| Документ.СчетНаОплатуПоставщика.Поступления КАК СчетПоступления
|ГДЕ
| СчетПоступления.Ссылка.Проведен
| И СчетПоступления.Ссылка.Дата >= &НачалоПериода
| И СчетПоступления.Ссылка.СтатусПоступления <> ЗНАЧЕНИЕ(Перечисление.СтатусыСчетовНаОплату.Получен)
|
|ИНДЕКСИРОВАТЬ ПО
| ДокументПоступления
|;
|
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ
| ПоступлениеТовары.Номенклатура КАК Номенклатура,
| СУММА(ПоступлениеТовары.Количество * ПоступлениеТовары.Коэффициент) КАК Количество
|ИЗ
| ВТ_ОтобранныеДокументы КАК ВТ_Документы
| ВНУТРЕННЕЕ СОЕДИНЕНИЕ Документ.ПоступлениеТоваровУслуг.Товары КАК ПоступлениеТовары
| ПО ВТ_Документы.ДокументПоступления = ПоступлениеТовары.Ссылка
|ГДЕ
| ПоступлениеТовары.Номенклатура В ИЕРАРХИИ(&Группы)
|
|СГРУППИРОВАТЬ ПО
| ПоступлениеТовары.Номенклатура";
Проанализируем поведение СУБД при выполнении нашего оптимизированного пакетного запроса. На первом этапе сервер СУБД обращается к сравнительно небольшой таблице табличной части счетов на оплату, накладывая жесткие фильтры по дате и статусу. Благодаря тому, что во временную таблицу помещается лишь небольшой отфильтрованный объем ссылок, мы экономим оперативную память сервера.
Оператор ВЫРАЗИТЬ(... КАК Документ.ПоступлениеТоваровУслуг) указывает транслятору запросов платформы 1С, что поле нужно обрабатывать строго как уникальный идентификатор конкретной таблицы поступлений товаров. В результате СУБД строит чистый план запроса без избыточных проверок типов.
Создание индекса по полю ДокументПоступления во временной таблице гарантирует, что во втором запросе пакета СУБД не будет сканировать всю физическую таблицу товаров поступлений. Вместо этого будет задействован поиск по индексу (Index Seek), что выполняется за миллисекунды даже на миллионных массивах строк.
Использование оператора В ИЕРАРХИИ позволяет корректно отбирать номенклатуру независимо от того, насколько глубоко внутри выбранных папок находятся карточки товаров. Платформа 1С самостоятельно транслирует это условие в эффективную SQL-конструкцию выбора по иерархическому дереву.
Для обеспечения максимального быстродействия данного запроса важно не только правильно написать код, но и настроить метаданные в конфигураторе 1С. По умолчанию реквизиты табличных частей документов не индексируются базой данных.
Откройте конфигуратор, найдите документ СчетНаОплатуПоставщика, перейдите на вкладку Данные, разверните табличную часть Поступления и найдите реквизит Документ. В свойствах этого реквизита найдите параметр Индексировать и установите для него значение Индексировать (или Индексировать с доп. упорядочиванием). Это создаст физический индекс в SQL-таблице табличной части, что ускорит выполнение первого запроса в пакете в десятки раз.