← вся теориятеория к собесу · Язык запросов 1С

Временные таблицы и пакетные запросы 1С

разбор писал Даниил, автор Сеньорчика

Зачем это спрашивают

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

// Так спрашивают: «Зачем временные таблицы, если есть вложенные запросы?» «Как передать таблицу значений в запрос?» «Когда нужен ИНДЕКСИРОВАТЬ ПО?» «Как получить остатки по всем строкам документа?»

Пакет и ПОМЕСТИТЬ

ПОМЕСТИТЬ ВТ_Имя кладёт результат во временную таблицу, а запросы пакета разделяют точкой с запятой. Выполнить() возвращает результат последнего запроса, ВыполнитьПакет() массив результатов всех запросов:

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

Запрос.Текст =
"ВЫБРАТЬ Т.Номенклатура, СУММА(Т.Количество) КАК Количество
|ПОМЕСТИТЬ ВТ_Товары
|ИЗ &Товары КАК Т
|СГРУППИРОВАТЬ ПО Т.Номенклатура
|;
|ВЫБРАТЬ ВТ.Номенклатура, ВТ.Количество, ЕСТЬNULL(О.КоличествоОстаток, 0) КАК Остаток
|ИЗ ВТ_Товары КАК ВТ
|    ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыНаСкладах.Остатки(,
|        Склад = &Склад
|        И Номенклатура В (ВЫБРАТЬ ВТ_Товары.Номенклатура ИЗ ВТ_Товары)) КАК О
|    ПО ВТ.Номенклатура = О.Номенклатура";
Запрос.УстановитьПараметр("Товары", Товары.Выгрузить(, "Номенклатура, Количество"));

Вместо запроса в цикле

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

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

Менеджер и индексы

Менеджер временных таблиц хранит таблицы между отдельными запросами, пока жив объект менеджера. Так в проведении один запрос готовит ВТ_Товары, а следующие с ней соединяются. Создать таблицу с тем же именем на том же менеджере повторно нельзя: перед повтором её удаляют УНИЧТОЖИТЬ ВТ_Товары.

ИНДЕКСИРОВАТЬ ПО Номенклатура строит индекс по временной таблице. Он окупается, когда таблица большая и по полю идёт соединение. На паре сотен строк построение индекса стоит дороже, чем поиск без него.

Отбор делают до ПОМЕСТИТЬ: каждая лишняя строка записывается во временную базу СУБД, в MS SQL это tempdb. Для отладки есть ВыполнитьПакетСПромежуточнымиДанными, он вернёт и содержимое временных таблиц.

Как отвечать: «Как получить остатки по всем товарам документа?»

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

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

На чём валят

  • −Запрашивают остатки в цикле по строкам. Сто строк дают сто запросов. Одна временная таблица и одно соединение.
  • −Создают временную таблицу с тем же именем в цикле на одном менеджере. На втором проходе имя занято. УНИЧТОЖИТЬ перед повтором или новый менеджер.
  • −Индексируют временную таблицу на сто строк. Индекс не окупается. Индексируют большие таблицы для соединений.
  • −Передают таблицу значений с колонками без типов. Временная таблица не построится. Колонки создают с ОписаниеТипов.

Проверь себя

Пять вопросов из банка по этой подтеме. Всего их 12, остальные разбираются в тренажёре.

  1. #oc_q_temp_tables1 / 5
    Как разделяются запросы пакета в одном тексте?
    A)Каждый запрос — отдельный объект Запрос
    B)Пустой строкой
    C)Словом ПАКЕТ
    D)Точкой с запятой
    показать ответ и разбор
    +D)Точкой с запятой

    // разбор: Пакетный запрос — несколько запросов в одном тексте через точку с запятой. Они выполняются по порядку, временные таблицы предыдущих доступны следующим. Выполнить() вернёт результат последнего, ВыполнитьПакет() — массив результатов всех.

  2. #oc_q_temp_tables2 / 5
    Зачем нужен МенеджерВременныхТаблиц?
    A)Чтобы хранить таблицы между сеансами пользователя
    B)Чтобы ускорить запись в регистры
    C)Таблицы живут между отдельными запросами
    D)Чтобы передать временные таблицы на клиент
    показать ответ и разбор
    +C)Таблицы живут между отдельными запросами

    // разбор: Без менеджера временные таблицы исчезают после выполнения пакета. С менеджером они живут, пока жив объект менеджера: несколько запросов с одним Запрос.МенеджерВременныхТаблиц видят таблицы друг друга. Между сеансами и на клиенте временные таблицы не существуют.

  3. #oc_q_temp_tables3 / 5
    Что вернёт Запрос.Выполнить() для пакета из трёх запросов?
    A)Массив из трёх результатов
    B)Результат последнего запроса
    C)Результат первого запроса
    D)Ошибку: пакет выполняют ВыполнитьПакет()
    показать ответ и разбор
    +B)Результат последнего запроса

    // разбор: Выполнить() выполняет все запросы пакета и возвращает результат последнего. Если нужны результаты промежуточных, вызывают ВыполнитьПакет(), он возвращает массив результатов по порядку. У запросов с ПОМЕСТИТЬ результат — число записанных строк.

  4. #oc_q_temp_tables4 / 5
    Когда стоит писать ИНДЕКСИРОВАТЬ ПО у временной таблицы?
    A)На каждой временной таблице, без исключений
    B)Таблица большая и соединяется по полю
    C)Только для таблиц меньше 100 строк
    D)Не нужно: СУБД индексирует временные таблицы сама
    показать ответ и разбор
    +B)Таблица большая и соединяется по полю

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

  5. #oc_q_temp_tables5 / 5
    Найди проблему (запросы выполняются с одним менеджером временных таблиц в цикле):
    ВЫБРАТЬ Т.Ссылка
ПОМЕСТИТЬ ВТ_Товары
ИЗ Справочник.Товары КАК Т
ГДЕ Т.Группа = &Группа
    A)На втором проходе имя ВТ уже занято
    B)ПОМЕСТИТЬ нельзя без ИНДЕКСИРОВАТЬ ПО
    C)Временную таблицу нельзя назвать с подчёркиванием
    D)ГДЕ нельзя использовать вместе с ПОМЕСТИТЬ
    показать ответ и разбор
    +A)На втором проходе имя ВТ уже занято

    // разбор: Временная таблица живёт в менеджере, и повторное ПОМЕСТИТЬ с тем же именем даёт ошибку «таблица уже существует». В конце итерации её уничтожают: УНИЧТОЖИТЬ ВТ_Товары, или создают новый менеджер. А ещё лучше — убрать запрос из цикла и обработать все группы одним запросом.

дальше

Теорию прочитал. Навык ставится повторением

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

Начать бесплатноВся тема: Язык запросов 1С
читать дальше