При подготовке к тестированию «1С:Профессионал» по платформе начинающие разработчики часто сталкиваются с вопросами, формулировки которых кажутся излишне теоретическими или запутанными. Один из таких примеров — вопрос под номером 07.06, посвященный использованию вложенных запросов. В частности, недоумение вызывает термин «таблица-источник данных».
В этой статье мы подробно разберем, что скрывается под этим понятием, детально проанализируем варианты использования вложенных запросов, рассмотрим особенности работы с ними в интерактивном Конструкторе запросов 1С, а также затронем важнейшие аспекты оптимизации производительности СУБД.
Прежде чем перейти к практике, давайте разберемся с терминологией. В языке запросов 1С (который во многом основан на стандартах SQL) любая выборка данных происходит из какого-либо источника. Источником данных могут выступать:
ПОМЕСТИТЬ.Таким образом, когда мы пишем конструкцию ВЫБРАТЬ ... ИЗ (...) КАК ИмяИсточника, мы используем вложенный запрос именно в качестве таблицы-источника.
Давайте подробно проанализируем структуру экзаменационного вопроса и выясним, почему правильным является ответ, объединяющий все три предложенных варианта.
Формулировка вопроса: «Вложенный запрос может использоваться...»
В данном сценарии вложенный запрос выступает полноценной «виртуальной» таблицей в секции ИЗ основного запроса. Для СУБД эта конструкция выглядит как подзапрос (subquery). Главное правило синтаксиса 1С в данном случае — вложенному запросу обязательно должен быть присвоен уникальный псевдоним (имя источника), к которому мы сможем обращаться во внешнем запросе.
Рассмотрим практический пример. Предположим, нам необходимо выбрать номенклатуру и сумму оборота из регистра накопления, предварительно рассчитав эти данные во вложенной выборке:
ВЫБРАТЬ
ВложенныйИсточник.Номенклатура КАК Номенклатура,
ВложенныйИсточник.СуммаОборот КАК СуммаОборот
ИЗ
(ВЫБРАТЬ
ПродажиОбороты.Номенклатура КАК Номенклатура,
ПродажиОбороты.СуммаОборот КАК СуммаОборот
ИЗ
РегистрНакопления.Продажи.Обороты КАК ПродажиОбороты) КАК ВложенныйИсточник
В этом коде вся конструкция внутри скобок является вложенным запросом, а имя ВложенныйИсточник определяет его как таблицу-источник для внешнего запроса.
Виртуальные таблицы в 1С (такие как остатки, обороты, срезы) очень чувствительны к параметрам отбора. Если наложить фильтр внутри параметров виртуальной таблицы, СУБД применит его на самом раннем этапе сборки данных, что существенно увеличит скорость выполнения. Вложенный запрос здесь выступает как поставщик списка значений для оператора В.
Посмотрим на пример, где мы выбираем остатки товаров на складах, но только по той номенклатуре, которая не помечена как услуга в справочнике:
ВЫБРАТЬ
ТоварыОстатки.Номенклатура,
ТоварыОстатки.Склад,
ТоварыОстатки.ВНаличииОстаток
ИЗ
РегистрНакопления.ТоварыНаСкладах.Остатки(
&Период,
Номенклатура В (ВЫБРАТЬ Ссылка ИЗ Справочник.Номенклатура ГДЕ Услуга = ЛОЖЬ)
) КАК ТоварыОстатки
Обратите внимание, что результат вложенного запроса возвращает список ссылок, который сразу же используется для жесткой фильтрации остатков СУБД.
Это классический способ фильтрации итоговой выборки. В отличие от предыдущего варианта, фильтрация через секцию ГДЕ происходит уже после того, как СУБД объединила и подготовила основные таблицы. Тем не менее, этот синтаксис абсолютно корректен и регулярно применяется на практике.
Рассмотрим ситуацию, когда нам нужно выбрать документы продажи только по тем контрагентам, которые являются юридическими лицами:
ВЫБРАТЬ
Реализация.Ссылка,
Реализация.Дата,
Реализация.Контрагент
ИЗ
Документ.РеализацияТоваровУслуг КАК Реализация
ГДЕ
Реализация.Контрагент В (ВЫБРАТЬ Ссылка ИЗ Справочник.Контрагенты ГДЕ ЮрЛицо = ИСТИНА)
Поскольку все три варианта использования вложенного запроса синтаксически верны и активно применяются в платформе, правильным ответом в тестировании является интегральный вариант: «Верны ответы 1, 2 и 3».
Давайте разберем по шагам, как визуально выглядит создание вложенного запроса в стандартном Конструкторе запросов в режиме Конфигуратора. Это поможет лучше сопоставить сухой текст теории с реальным интерфейсом платформы.
Важное примечание разработчику: Если вы работаете в режиме управляемого приложения непосредственно в режиме «1С:Предприятие» (например, редактируете схему компоновки данных в отчете пользователя), интерактивный конструктор может иметь ограничения. В таких случаях вложенные конструкции в условиях связи или сложных выражениях зачастую приходится дописывать вручную с клавиатуры.
Несмотря на то, что использование вложенных запросов является абсолютно легитимным с точки зрения синтаксиса, стандарты разработки фирмы «1С» накладывают серьезные ограничения на их применение. В реальной практике высоконагруженных систем разработчики стараются избегать глубоких вложенных запросов, заменяя их временными таблицами.
Выясним причину такого подхода и проанализируем различия.
Когда мы соединяем физическую таблицу с вложенным запросом (например, используя ЛЕВОЕ СОЕДИНЕНИЕ), современные СУБД (MS SQL Server, PostgreSQL, IBM DB2) не всегда способны корректно оценить, сколько именно строк вернет этот вложенный запрос. Это называется проблемой оценки кардинальности (Cardinality Estimation).
Не имея точной статистики по объему данных внутри вложенного запроса, оптимизатор СУБД может построить крайне неэффективный план запроса — например, использовать медленный алгоритм Nested Loops (вложенные циклы) вместо быстрого Hash Match (соединение хешированием). На больших базах данных это приводит к драматическому падению производительности, блокировкам таблиц и зависанию системы.
Вместо вложенного запроса рекомендуется использовать пакетные запросы с помещением промежуточных результатов во временные таблицы с помощью конструкции ПОМЕСТИТЬ.
Рассмотрим, как можно переписать неоптимальный вложенный запрос в производительный пакетный вариант.
Плохо (использование вложенного запроса в соединении):
ВЫБРАТЬ
Товары.Ссылка КАК Товар,
Продажи.СуммаОборот КАК Сумма
ИЗ
Справочник.Номенклатура КАК Товары
ЛЕВОЕ СОЕДИНЕНИЕ (
ВЫБРАТЬ
Обороты.Номенклатура КАК Номенклатура,
СУММА(Обороты.СуммаОборот) КАК СуммаОборот
ИЗ
РегистрНакопления.Продажи.Обороты КАК Обороты
ГРУППИРОВАТЬ ПО
Обороты.Номенклатура
) КАК Продажи
ПО Товары.Ссылка = Продажи.Номенклатура
Хорошо (перенос данных во временную таблицу):
ВЫБРАТЬ
Обороты.Номенклатура КАК Номенклатура,
СУММА(Обороты.СуммаОборот) КАК СуммаОборот
ПОМЕСТИТЬ Вт_Продажи
ИЗ
РегистрНакопления.Продажи.Обороты КАК Обороты
ГРУППИРОВАТЬ ПО
Обороты.Номенклатура
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
Товары.Ссылка КАК Товар,
ЕСТЬNULL(Продажи.СуммаОборот, 0) КАК Сумма
ИЗ
Справочник.Номенклатура КАК Товары
ЛЕВОЕ СОЕДИНЕНИЕ Вт_Продажи КАК Продажи
ПО Товары.Ссылка = Продажи.Номенклатура
Разберем по шагам преимущества второго варианта:
Вт_Продажи, так как она уже физически создана и заполнена в базе данных tempdb. Оптимизатор строит идеальный план выполнения.ИНДЕКСИРОВАТЬ ПО. Индексирование по полю связи (в нашем случае по Номенклатура) ускоряет операцию соединения в разы.;), читать и анализировать в консоли запросов гораздо проще, чем огромные вложенные конструкции «вложенный запрос во вложенном запросе».Не стоит полностью отказываться от вложенных запросов. Они вполне применимы и полезны в следующих сценариях:
ГДЕ Поле В (ВЫБРАТЬ...)), когда накладные расходы СУБД на создание, запись структуры и последующее удаление временной таблицы могут превысить пользу от её использования.Понимание этих нюансов поможет вам не только успешно сдать экзамен «1С:Профессионал», но и писать по-настоящему быстрый, отказоустойчивый код для реальных высоконагруженных систем автоматизации бизнеса.