Временная таблица в запросе 1С — именованный промежуточный результат: запрос сохраняет выборку через ПОМЕСТИТЬ, а следующие запросы пакета читают её как обычную таблицу — соединяют, фильтруют, ставят в условие. Разберём синтаксис, условия по временной таблице (дальше — ВТ), менеджер временных таблиц, УНИЧТОЖИТЬ, конструктор и то, почему временные таблицы в запросе 1С 8.3 — первое средство против медленного сложного запроса. Имена объектов в примерах сверены по УТ 11.4 и КА 2.5, сам приём работает в любой конфигурации.
Временные таблицы появляются, как только запрос перестаёт быть одной выборкой: отчёт сводит остатки, продажи и цены, обработка подбирает товары по нескольким условиям, проведение сверяет строки документа с остатками. Типовые конфигурации устроены так же: в УТ 11.4 в модуле менеджера документа ЗаказКлиента есть экспортная процедура ВременныеТаблицыТоваровЗаказа(МенеджерВременныхТаблиц, Заказ, Склад) — она кладёт товары заказа во временные таблицы переданного менеджера, а общий модуль ОбеспечениеСервер читает их своими запросами.
ПОМЕСТИТЬ ИмяТаблицы пишут после списка полей, перед ИЗ. Колонки получают имена из псевдонимов КАК, поэтому псевдоним нужен каждому полю. Индекс задают в конце запроса: ИНДЕКСИРОВАТЬ ПО и список колонок. В запросе с объединением ПОМЕСТИТЬ ставят в первую часть, ИНДЕКСИРОВАТЬ ПО — после последней. Запросы пакета разделяет точка с запятой, строка из косых черт между ними — комментарий. Источником может быть и таблица значений из параметра — см. статью о таблице значений в запросе.
| Как выполняется запрос | Сколько живёт временная таблица |
|---|---|
| Пакет без менеджера временных таблиц | До конца выполнения пакета |
Запрос с заданным свойством МенеджерВременныхТаблиц | Пока её не уничтожат через УНИЧТОЖИТЬ, не вызовут Закрыть() у менеджера или пока сам менеджер не будет освобождён |
Таблицу видят только запросы с тем же менеджером, другие менеджеры и сеансы — нет, поэтому одинаковые имена у разных пользователей не конфликтуют. В клиент-серверной базе на MS SQL Server это временная таблица в tempdb: чем она больше, тем больше работы серверу СУБД.
Задача: найти неликвиды — товары, которые лежат на складах, но не продавались за период. Первый запрос пакета кладёт остатки во временную таблицу, второй выбирает продажи только по этим товарам, третий оставляет остатки без продаж.
// Неликвиды: товары с остатком на складах, которых не было в реализациях за период.
// Серверный код: модуль объекта обработки, модуль менеджера или общий модуль.
// Имена объектов - УТ 11.4 и КА 2.5. Если товар уходит и другими документами,
// добавьте их в ВТ_Продажи через ОБЪЕДИНИТЬ ВСЕ.
Функция НеликвидныеТовары(НачалоПериода, КонецПериода) Экспорт
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| Остатки.Номенклатура КАК Номенклатура,
| Остатки.Характеристика КАК Характеристика,
| Остатки.Склад КАК Склад,
| СУММА(Остатки.ВНаличииОстаток) КАК Остаток
|ПОМЕСТИТЬ ВТ_Остатки
|ИЗ
| РегистрНакопления.ТоварыНаСкладах.Остатки КАК Остатки
|
|СГРУППИРОВАТЬ ПО
| Остатки.Номенклатура,
| Остатки.Характеристика,
| Остатки.Склад
|
|ИМЕЮЩИЕ
| СУММА(Остатки.ВНаличииОстаток) > 0
|
|ИНДЕКСИРОВАТЬ ПО
| Номенклатура,
| Характеристика
|;
|
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ РАЗЛИЧНЫЕ
| Товары.Номенклатура КАК Номенклатура,
| Товары.Характеристика КАК Характеристика
|ПОМЕСТИТЬ ВТ_Продажи
|ИЗ
| Документ.РеализацияТоваровУслуг.Товары КАК Товары
|ГДЕ
| Товары.Ссылка.Проведен
| И Товары.Ссылка.Дата МЕЖДУ &НачалоПериода И &КонецПериода
| И (Товары.Номенклатура, Товары.Характеристика) В
| (ВЫБРАТЬ
| Ост.Номенклатура,
| Ост.Характеристика
| ИЗ
| ВТ_Остатки КАК Ост)
|
|ИНДЕКСИРОВАТЬ ПО
| Номенклатура,
| Характеристика
|;
|
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ
| Остатки.Склад КАК Склад,
| Остатки.Номенклатура КАК Номенклатура,
| Остатки.Характеристика КАК Характеристика,
| Остатки.Остаток КАК Остаток
|ИЗ
| ВТ_Остатки КАК Остатки
| ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Продажи КАК Продажи
| ПО Остатки.Номенклатура = Продажи.Номенклатура
| И Остатки.Характеристика = Продажи.Характеристика
|ГДЕ
| Продажи.Номенклатура ЕСТЬ NULL
|
|УПОРЯДОЧИТЬ ПО
| Остаток УБЫВ";
Запрос.УстановитьПараметр("НачалоПериода", НачалоДня(НачалоПериода));
Запрос.УстановитьПараметр("КонецПериода", КонецДня(КонецПериода));
// [0] - ВТ_Остатки, [1] - ВТ_Продажи, [2] - итоговая выборка.
// Без менеджера временные таблицы исчезнут сами, когда пакет выполнится.
Результаты = Запрос.ВыполнитьПакет();
Возврат Результаты[2].Выгрузить();
КонецФункцииСГРУППИРОВАТЬ ПО в ВТ_Остатки: у регистра есть ещё измерения Назначение, Помещение и Серия, а нужна одна строка на товар, характеристику и склад. Условие на ресурс — в ИМЕЮЩИЕ: в параметр «Условие» виртуальной таблицы остатков пишут только измерения.(Товары.Номенклатура, Товары.Характеристика) В (ВЫБРАТЬ … ИЗ ВТ_Остатки КАК Ост) — условие по временной таблице сразу по двум полям: в ВТ_Продажи попадут только товары, которые лежат на складе.ЛЕВОЕ СОЕДИНЕНИЕ … ГДЕ Продажи.Номенклатура ЕСТЬ NULL — «есть в первой таблице, нет во второй». По одному полю то же пишут так: ГДЕ НЕ Остатки.Номенклатура В (ВЫБРАТЬ П.Номенклатура ИЗ ВТ_Продажи КАК П).ВыполнитьПакет() возвращает массив результатов по числу запросов пакета, с нуля; у запроса с ПОМЕСТИТЬ там не строки, а количество помещённых записей. УНИЧТОЖИТЬ не нужен: без менеджера таблицы исчезнут вместе с пакетом.Третий частый случай — сравнить поле с одним заранее посчитанным значением. Скалярных подзапросов вида Дата > (ВЫБРАТЬ …) в языке запросов нет: вложенный запрос бывает только источником или правой частью В. Поэтому значение временной таблицы из одной строки подставляют соединением:
// Реализации со склада после последнего поступления на этот склад.
ВЫБРАТЬ
МАКСИМУМ(Поступление.Дата) КАК Дата
ПОМЕСТИТЬ ВТ_Граница
ИЗ
Документ.ПриобретениеТоваровУслуг КАК Поступление
ГДЕ
Поступление.Проведен
И Поступление.Склад = &Склад
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
Реализация.Ссылка КАК Документ,
Реализация.Дата КАК Дата
ИЗ
Документ.РеализацияТоваровУслуг КАК Реализация
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_Граница КАК Граница
ПО Реализация.Дата > Граница.Дата
ГДЕ
Реализация.Проведен
И Реализация.Склад = &СкладЕсли поступлений на склад не было, МАКСИМУМ вернёт NULL, условие соединения не выполнится, и выборка будет пустой.
Когда временную таблицу создаёт одна процедура, а читает другая, нужен МенеджерВременныхТаблиц: один объект присваивают свойству МенеджерВременныхТаблиц каждого запроса. Пример проверяет, хватает ли остатка под заказ клиента; запрос к временной таблице заказа может выполнить любая функция, которой передали менеджер.
// Кладёт строки заказа клиента во временную таблицу ВТ_ТоварыЗаказа
// переданного менеджера. Отменённые строки пропускает, одинаковые товары складывает.
// Склад берётся из строки заказа: в УТ 11.4 и КА 2.5 он есть в табличной части Товары.
Процедура ПоместитьТоварыЗаказа(МенеджерВТ, Заказ) Экспорт
Запрос = Новый Запрос;
Запрос.МенеджерВременныхТаблиц = МенеджерВТ;
Запрос.Текст =
"ВЫБРАТЬ
| Товары.Номенклатура КАК Номенклатура,
| Товары.Характеристика КАК Характеристика,
| Товары.Склад КАК Склад,
| СУММА(Товары.Количество) КАК Количество
|ПОМЕСТИТЬ ВТ_ТоварыЗаказа
|ИЗ
| Документ.ЗаказКлиента.Товары КАК Товары
|ГДЕ
| Товары.Ссылка = &Заказ
| И НЕ Товары.Отменено
|
|СГРУППИРОВАТЬ ПО
| Товары.Номенклатура,
| Товары.Характеристика,
| Товары.Склад";
Запрос.УстановитьПараметр("Заказ", Заказ);
Запрос.Выполнить();
КонецПроцедуры
// Строки из ВТ_ТоварыЗаказа, под которые не хватает остатка на складе.
// ВТ_ТоварыЗаказа уже должна быть в менеджере и после вызова в нём остаётся.
// Сравниваем с физическим остатком, резервы не учитываются.
Функция НехваткаТоваров(МенеджерВТ) Экспорт
Запрос = Новый Запрос;
Запрос.МенеджерВременныхТаблиц = МенеджерВТ;
Запрос.Текст =
"ВЫБРАТЬ
| Остатки.Номенклатура КАК Номенклатура,
| Остатки.Характеристика КАК Характеристика,
| Остатки.Склад КАК Склад,
| СУММА(Остатки.ВНаличииОстаток) КАК Остаток
|ПОМЕСТИТЬ ВТ_ОстаткиПоЗаказу
|ИЗ
| РегистрНакопления.ТоварыНаСкладах.Остатки(
| ,
| (Номенклатура, Характеристика, Склад) В
| (ВЫБРАТЬ
| Т.Номенклатура,
| Т.Характеристика,
| Т.Склад
| ИЗ
| ВТ_ТоварыЗаказа КАК Т)) КАК Остатки
|
|СГРУППИРОВАТЬ ПО
| Остатки.Номенклатура,
| Остатки.Характеристика,
| Остатки.Склад
|;
|
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ
| ТоварыЗаказа.Номенклатура КАК Номенклатура,
| ТоварыЗаказа.Характеристика КАК Характеристика,
| ТоварыЗаказа.Склад КАК Склад,
| ТоварыЗаказа.Количество КАК Заказано,
| ЕСТЬNULL(Остатки.Остаток, 0) КАК Остаток,
| ТоварыЗаказа.Количество - ЕСТЬNULL(Остатки.Остаток, 0) КАК Нехватка
|ИЗ
| ВТ_ТоварыЗаказа КАК ТоварыЗаказа
| ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ОстаткиПоЗаказу КАК Остатки
| ПО ТоварыЗаказа.Номенклатура = Остатки.Номенклатура
| И ТоварыЗаказа.Характеристика = Остатки.Характеристика
| И ТоварыЗаказа.Склад = Остатки.Склад
|ГДЕ
| ТоварыЗаказа.Количество > ЕСТЬNULL(Остатки.Остаток, 0)
|;
|
|////////////////////////////////////////////////////////////////////////////////
|УНИЧТОЖИТЬ ВТ_ОстаткиПоЗаказу";
// [0] - ВТ_ОстаткиПоЗаказу, [1] - нехватка, [2] - УНИЧТОЖИТЬ.
// Выполнить() вернул бы результат последнего запроса пакета, то есть УНИЧТОЖИТЬ.
Возврат Запрос.ВыполнитьПакет()[1].Выгрузить();
КонецФункции
// Один менеджер на всю проверку заказа, в конце он закрывается.
Функция ПроверитьОбеспеченностьЗаказа(Заказ) Экспорт
МенеджерВТ = Новый МенеджерВременныхТаблиц;
ПоместитьТоварыЗаказа(МенеджерВТ, Заказ);
Нехватка = НехваткаТоваров(МенеджерВТ);
// Здесь можно выполнить и другие запросы к ВТ_ТоварыЗаказа с тем же менеджером.
МенеджерВТ.Закрыть(); // удаляет все временные таблицы менеджера
Возврат Нехватка;
КонецФункцииОтбор в параметре виртуальной таблицы Остатки(, (Номенклатура, Характеристика, Склад) В (ВЫБРАТЬ … ИЗ ВТ_ТоварыЗаказа КАК Т)) — главный приём экономии: регистр считается только по строкам заказа. НехваткаТоваров работает с чужим менеджером и уничтожает свою вспомогательную таблицу, иначе второй вызов упадёт на ПОМЕСТИТЬ. Индексов нет намеренно: строк столько, сколько в заказе.
Удалить временную таблицу можно отдельным запросом пакета УНИЧТОЖИТЬ ИмяТаблицы — по одной таблице на запрос. Удалить или изменить отдельные строки нельзя, только таблицу целиком. Уничтожать стоит, когда менеджер живёт дальше (передаётся в другие процедуры, используется в цикле), когда таблицу с тем же именем нужно пересоздать и когда в длинном пакете большая промежуточная таблица уже не нужна — место в tempdb освободится раньше.
Все таблицы менеджера сразу удаляет Закрыть(). В УТ 11 и КА 2 для выборочной очистки есть ОбщегоНазначенияУТ.УничтожитьВременныеТаблицы(МенеджерВременныхТаблиц, СписокТаблиц, ИсключаяТаблицы), а имена таблиц менеджера возвращает ОбщегоНазначенияУТ.СписокВременныхТаблиц(). Учтите, что Выполнить() возвращает результат последнего запроса пакета: если это УНИЧТОЖИТЬ, данных вы не получите — берите результат по индексу из ВыполнитьПакет().
Большой запрос с вложенными запросами и соединениями виртуальных таблиц СУБД оптимизирует целиком и часто ошибается в оценке, сколько строк вернёт вложенная часть, — отсюда вложенные циклы по миллионам записей. Временная таблица режет задачу на шаги:
ИНДЕКСИРОВАТЬ ПО;Классический случай — соединение с вложенным запросом. «Последняя реализация каждого партнёра» в лоб:
// Было: соединение с вложенным запросом.
ВЫБРАТЬ
Последние.Партнер КАК Партнер,
Последние.Дата КАК Дата,
Реализация.Ссылка КАК Документ,
Реализация.СуммаДокумента КАК Сумма
ИЗ
(ВЫБРАТЬ
Р.Партнер КАК Партнер,
МАКСИМУМ(Р.Дата) КАК Дата
ИЗ
Документ.РеализацияТоваровУслуг КАК Р
ГДЕ
Р.Проведен
И Р.Дата >= &ДатаНачала
СГРУППИРОВАТЬ ПО
Р.Партнер) КАК Последние
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Документ.РеализацияТоваровУслуг КАК Реализация
ПО Последние.Партнер = Реализация.Партнер
И Последние.Дата = Реализация.Дата
ГДЕ
Реализация.ПроведенТо же через временную таблицу с индексом:
// Стало: последняя проведённая реализация каждого партнёра начиная с даты.
// Шаг 1: дата последней реализации по партнёру - во временную таблицу с индексом.
// Шаг 2: соединяем документы с этой таблицей по партнёру и дате.
Функция ПоследниеРеализацииПартнеров(ДатаНачала) Экспорт
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| Реализация.Партнер КАК Партнер,
| МАКСИМУМ(Реализация.Дата) КАК Дата
|ПОМЕСТИТЬ ВТ_ПоследниеДаты
|ИЗ
| Документ.РеализацияТоваровУслуг КАК Реализация
|ГДЕ
| Реализация.Проведен
| И Реализация.Дата >= &ДатаНачала
|
|СГРУППИРОВАТЬ ПО
| Реализация.Партнер
|
|ИНДЕКСИРОВАТЬ ПО
| Партнер,
| Дата
|;
|
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ
| Последние.Партнер КАК Партнер,
| Последние.Дата КАК Дата,
| Реализация.Ссылка КАК Документ,
| Реализация.СуммаДокумента КАК Сумма
|ИЗ
| ВТ_ПоследниеДаты КАК Последние
| ВНУТРЕННЕЕ СОЕДИНЕНИЕ Документ.РеализацияТоваровУслуг КАК Реализация
| ПО Последние.Партнер = Реализация.Партнер
| И Последние.Дата = Реализация.Дата
|ГДЕ
| Реализация.Проведен
|
|УПОРЯДОЧИТЬ ПО
| Дата";
Запрос.УстановитьПараметр("ДатаНачала", НачалоДня(ДатаНачала));
// Два документа партнёра с одинаковыми датой и временем дадут две строки.
// Первыми идут партнёры, которые дольше всех не покупали.
Возврат Запрос.Выполнить().Выгрузить();
КонецФункцииИндексируйте по полям соединения или условия В и только большие таблицы: для пары десятков строк построение индекса дороже выигрыша. В методических материалах 1С по оптимизации запросов на ИТС соединения с вложенными запросами и виртуальными таблицами названы типичными причинами медленной работы, и решение там то же — временная таблица.
ИНДЕКСИРОВАТЬ ПО задайте на вкладке «Индекс».УНИЧТОЖИТЬ проще дописать текстом.В конструкторе 8.3.27 в «Типе запроса» есть ещё «Добавление во временную таблицу» — о нём в вопросах ниже.
Пакет удобнее проверять в консоли запросов, которая показывает временные таблицы, например в бесплатной консоли 1c-only.ru. В коде то же даёт ВыполнитьПакетСПромежуточнымиДанными(): для запросов с ПОМЕСТИТЬ он возвращает содержимое таблицы, а не число строк. Функцию ниже удобно вызвать в отладчике через «Вычислить выражение».
// Отладка пакета: результат каждого его запроса таблицей значений.
// Для запросов с ПОМЕСТИТЬ это содержимое временной таблицы, а не число строк.
Функция ПромежуточныеДанныеПакета(Запрос) Экспорт
СвойМенеджер = (Запрос.МенеджерВременныхТаблиц = Неопределено);
Если СвойМенеджер Тогда
Запрос.МенеджерВременныхТаблиц = Новый МенеджерВременныхТаблиц;
КонецЕсли;
Данные = Новый Массив;
Для Каждого Результат Из Запрос.ВыполнитьПакетСПромежуточнымиДанными() Цикл
// Запрос УНИЧТОЖИТЬ данных не возвращает.
Если Результат = Неопределено Тогда
Данные.Добавить(Неопределено);
Иначе
Данные.Добавить(Результат.Выгрузить());
КонецЕсли;
КонецЦикла;
// Свой менеджер закрываем, чтобы пакет можно было выполнить ещё раз.
// Чужой не трогаем: его таблицы нужны вызывающему коду.
Если СвойМенеджер Тогда
Запрос.МенеджерВременныхТаблиц.Закрыть();
Запрос.МенеджерВременныхТаблиц = Неопределено;
КонецЕсли;
Возврат Данные;
КонецФункцииЗапрос без общего менеджера, стоит в пакете раньше запроса с ПОМЕСТИТЬ или таблицу уже уничтожили.ПОМЕСТИТЬ с тем же именем в том же менеджере: повторный вызов, цикл, совпадение с таблицей типового кода при проведении. Уничтожайте вспомогательные таблицы, своим давайте префикс.ПОМЕСТИТЬ нет КАК, и платформа назвала колонку сама.ИНДЕКСИРОВАТЬ ПО указано поле, которого нет среди колонок.Закрыть().Выполнить() — последним в пакете стоит УНИЧТОЖИТЬ.ПОМЕСТИТЬ.Другие ошибки выполнения запросов разобраны в статье об ошибке при выполнении запроса.
Вложенный запрос СУБД разворачивает внутри общего плана и может ошибиться с оценкой его размера. Временная таблица считается один раз, хранит реальное число строк, может иметь индекс и читается в нескольких запросах. Для короткого отбора через В вложенный запрос нормален, для соединений лучше временная таблица.
Отдельные строки не меняются и не удаляются. Классический путь — новая таблица из старой с нужным отбором или через ОБЪЕДИНИТЬ ВСЕ. Добавление строк в существующую таблицу разработчики платформы анонсировали для 8.3.25 вместе с несколькими индексами на одну таблицу, а в конструкторе 8.3.27 есть тип запроса «Добавление во временную таблицу». Синтаксис сверяйте с документацией своей версии.
Да. В наборе данных «Запрос» можно написать пакет с ПОМЕСТИТЬ, полями набора станут поля последнего запроса пакета.
Нет. В тонком и веб-клиенте МенеджерВременныхТаблиц недоступен, и он не сериализуется: в реквизит формы или параметр клиентского метода он не попадёт. Нужны данные между вызовами — выгрузите результат в таблицу значений и положите во временное хранилище.
Если отчёт на временных таблицах всё равно работает минутами или нужно переписать чужой запрос на сотни строк, проще отдать это тому, кто оптимизирует запросы каждый день. Профильные услуги — отчёты и обработки 1С и «1С тормозит»; разместите задачу — оценка бесплатная, ответ в течение рабочего дня.
Станьте частью сообщества!
Войдите или зарегистрируйтесь, и вы сможете участвовать в обсуждениях.