Пакетные запросы. Пакетные запросы для чайников 1с пакетное выполнение запроса
В статье рассказывается о механизме пакетных запросов, реализованных в платформе «1С:Предприятие». Прочитав статью, вы узнаете:
- Что такое пакетные запросы и для чего они нужны?
- Как создать пакет запросов при помощи конструктора запросов?
- Как вернуть массив результатов для каждого запроса из пакета?
Применимость
Материал актуален для текущих версий платформы «1С:Предприятие» редакции 8.3
Назначение пакета запросов
Платформа позволяет работать с пакетами запросов. Получаем возможность выполнить несколько запросов «за раз». В пакетном запросе тексты запросов разделяются символом «;» (точка с запятой).
Запросы исполняются последовательно, при этом временные таблицы, которые были созданы во время исполнения какого-либо запроса, будут существовать до окончания исполнения всего пакета запроса или до исполнения в пакете запроса, уничтожающего данную временную таблицу. Важное отличие от вложенного запроса заключается в том, что доступны результаты каждого запроса пакета отдельно.
Пакеты запросов позволяют достичь поэтапного выполнения запроса. Для этого в пакетном запросе сначала происходит создание временных таблиц, далее – их совместное использование (соединение, объединение, фильтры) для получения итогового результата запроса. Также важно отметить, что использование временных таблиц в пакетных запросах позволяет улучшить читаемость текста запроса.
Объемные запросы с завернутыми друг в друга вложенными запросами зачастую бывают достаточно сложными для восприятия. Но если переписать такой запрос с использованием временных таблиц, наглядность запроса может повыситься достаточно сильно. Применение пакета запросов с временными таблицами также может повысить производительность запроса.
Существуют методики оптимизации производительности запросов, основанные на замене вложенных запросов на временные таблицы.
Временная таблица может быть полезна, когда необходимо использовать одни и те же данные в большом запросе несколько раз, например, соединяя или объединяя с другими таблицами. При использовании вложенных запросов такие данные пришлось бы получать несколько раз при помощи одинаковых вложенных запросов, что, безусловно, сказалось бы и на читаемости текста, и на производительности.
Создание пакета запросов при помощи конструктора
Отдельные запросы, входящие в пакет, отделяются в тексте символом «;» (точка с запятой). Чтобы не разделять текст запроса вручную, можно использовать для этого конструктор запросов.
В конструкторе запросов есть отдельная закладка для пакетов запросов. Запросы в пакет можно добавлять при помощи соответствующей кнопки на командной панели, а также передвигать вверх или вниз.
Визуальное отображение отдельных запросов – закладки в правой части конструктора, при помощи которых можно перейти к редактированию текста отдельного запроса. На этих закладках для временных таблиц выводятся имена, для запросов на выборку данных – «Запрос пакета 2» и т.п., на уничтожение – «– ИмяВТ».
Также в списке таблиц базы данных появляются временные таблицы, созданные в рамках данного пакета. Однако это не означает, что временные таблицы хранятся в базе данных вместе со всеми остальными таблицами информационной базы.
Выполнение запросов пакета
Если объекту Запрос , исполняющему пакетный запрос, установлен менеджер временных таблиц, временные таблицы, которые не были уничтожены в рамках пакетного запроса, сохранятся в установленном менеджере.
В тексте пакетного запроса возможно использование и уничтожение временных таблиц, которые существовали в установленном менеджере временных таблиц на момент запуска пакета на исполнение.
Кроме метода Выполнить() , последовательно выполняющего все запросы пакета и возвращающего результат последнего запроса в пакете, в платформе существует еще один метод – ВыполнитьПакет() .
Этот метод последовательно выполняет все запросы и возвращает массив результатов для каждого запроса из пакета в последовательности расположения запросов в тексте пакета.
Результатом выполнения запроса на уничтожение временной таблицы является значение Неопределено , которое также помещается в массив результатов.
Когда мой запрос стал таким сложным, что превысил пределы моего понимания, я решил использовать пакетные запросы.
Но столкнулся с фактом, что ничего о них не знаю. Оказалось, все очень просто. Через 5 минут вы будете уметь пользоваться пакетными запросами. Начинайте читать.
Как оказалось все очень просто. Нужно просто написать несколько запросов, разделенных точкой с запятой. Результат вернется в последнем запросе.
Пакетные запросы появились только в версии 8.1.11.67.4.
Вот текст запроса:
ВЫБРАТЬ Т1.Зн ПОМЕСТИТЬ ВТБуквы ИЗ (ВЫБРАТЬ "А" КАК Зн ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ "Б") КАК Т1;
ВЫБРАТЬ Т1.Зн ПОМЕСТИТЬ ВТЦифры ИЗ (ВЫБРАТЬ "1" КАК Зн ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ "2") КАК Т1;
ВЫБРАТЬ ТБ.Зн, ТЦ.Зн, ТБ.Зн+ТЦ.Зн ИЗ ВТБуквы КАК ТБ, ВТЦифры КАК ТЦ
Пакетные запросы поддерживаются в любой обычной консоли запросов.
На рисунке представлен образец выполнения запроса:
А теперь немного из опыта. Зачем нужны пакетные запросы.
Дело в том, что во временную таблицу можно вложить какой-то промежуточный результат, который потом может понадобиться в нескольких последующих запросах.
Раньше, когда не было временных таблиц, пришлось бы дублировать текст запроса.
Можно конечно, обойтись без пакетного запроса, последовательно выполняя несколько запросов и манипулируя вложенными таблицами. Но с пакетными запросами удобнее. Просто пишешь запрос, и не думаешь о размещении временных таблиц. Все происходит само.
Кроме того, если используется система компоновки данных (СКД), она грамотно отбирает нужные поля и минимизирует весь пакет запросов.
Если у запросов был метод Запрос.Выполнить() то теперь появился метод Запрос.ВыполнитьПакет() , который возвращает все таблицы из пакета, в виде массива.
Анонс пакетных запросов на сайте 1с находится здесь: http://v8.1c.ru/overview/release_8_1_11/#Functional
История из жизни
Объясню, что меня подвигло на пакетные запросы.
Значит, представьте есть документ, у него табличная часть. В колонке «Ошибка » признак, есть ли ошибка при заполнении документа. В колонке «ТекстОшибки » может быть одно или несколько предложений с текстами ошибок. Виды ошибок, содержащиеся в предложениях известны заранее.
Так вот, мы заносим список всех ошибок в таблицу КодыОшибок - там содержится код ошибки и подстрока поиска.
Получаем для каждой строки одну, две или больше ошибок. Т.к. в одной строке может быть несколько ошибок.
Но ошибка может быть и не распознана, т.е. флаг «Ошибка » стоит, а текст ошибки не выдал нам код ошибки.
Делаем левое соединение, там где код ошибки есть NULL, даем код ошибки «Прочие ошибки » .
Но проблема была в том, что кодов ошибок было около 200, поэтому левое соединение работало очень долго. Пришлось заменить его на внутреннее соединение, которое летало. Но при этом терялись строки, для которых ошибка была не найдена. Я так и не смог понять, как вытащить эти строки в результат.
Запрос писался для системы компоновки, т.е. никаких таблиц значений или временных таблиц применять нельзя в принципе. Тут и пригодились пакетные запросы.
Я просто еще раз соединил все строки с ошибками со всеми строками, для которых были найдены ошибки, и добавил все-таки вид ошибки «Прочие ошибки».
Пакетный запрос необходим когда запрос слишком сложен, чтобы его сформулировать и заставить работать эффективно. Для того чтобы понять, что такое пакетный запрос можно использовать схемку:
Кстати, пакетные варианты запросов стали доступны только в решении 8.1.11.67.4 – так что вы, возможно, ещё не знаете, как это работает на практике. Если рассказать примитивно, то достаточно создать ряд запросов и соединить их через символ «;». Например:
Результат возвращается через последний из них. Кстати, пакетные запросы имеют поддержку в каждой обычной консоли. Пример его выполнения через консоль запросов:
Почему нельзя обойтись без пакетных запросов?
Вкладываем во временную таблицу промежуточный результат, предполагаем использовать его в каких-то следующих запросах. Без наличия функционала временной таблицы, такой промежуточный запрос нужно было бы повторять ещё раз. Если нет знаний о том, как работает пакетный запрос и как им воспользоваться, то можно выполнять один за другим несколько запросов, используя данные вложенных таблиц – так было раньше, теперь не стоит тратить на это время, ведь у нас есть эффективный и простой пакетный запрос в 1С . С ним удобно, уже не надо помнить о том, где должна быть временная таблица. Для минимизации и быстродействия используйте возможности СКД. Система отберёт, отсортирует, разместит. Вот как позиционируют данное новшество сами разработчики популярного и надежного решения.
Анонс пакетных запросов от разработчиков 1С
- Существенная оптимизация работы программы в целом;
- Увеличение масштабируемости известного решения;
- Эффективное быстродействие за счёт внедрения возможностей пакетного размещения;
- Простое и понятное администрирование системы;
- Лучшие возможности для интеграции с другими решениями.
Каковы же функциональные возможности запросов в 8.1.11?
Именно запросы существенно улучшают работу с временными таблицами. Функционал пакетных запросов подразумевает, что любая таблица, сформированная для конкретного запроса из пакета, работает пока пакетный запрос полностью не закончил своё действие или до достижения команды на его уничтожение. Сейчас можно получать как результат самого последнего запроса – это будет команда Выполнить(), так и целый массив результатов по методу ВыполнитьПакет().
Алгоритм грамотного создания пакетного запроса
Итак, мы теперь знаем, что пакетный запрос – это несколько запросов, которые описаны нами как один общий запрос. Выполняться они должны друг за другом. Любой промежуточный результат в ходе выполнения всей цепочки включённых запросов может быть помещён во временную таблицу. При такой структуре любой запрос может обратиться к этим промежуточным данным. Важно, результаты любого запроса, помещённые во временную таблицу, могут быть использованы неоднократно. При этом больше не нужно прописывать одно и тоже несколько раз.
Минусы метода
Говоря о преимуществах использования пакетных запросов, стоит упомянуть и о явном минусе. Дело в том, что при таком алгоритме действий система постоянно вынуждена создавать таблицы, в которых она и хранит всю базу данных. Уходит время как на создание каждой таблицы, так и на её последующее удаление. Вот такой пример:
В результате имеем таблицу:
Теперь используем пакетный запрос, помним, что нам для этого нужен символ точка с запятой, получаем:
Обратите внимание, 3-ий запрос объединил результаты работы первых двух, смотрим, что получилось:
Теперь постараемся добавить красоты и немного изменим текст для 3-его запроса, вот так:
Теперь у нас вот что получилось:
Осталось объединить столбцы с Номенклатурой, и проставить нули в пустых клетках.
Думаю все вы согласитесь с тем, что тема запросов в системе 1С:Предприятие 8 сегодня может похвастаться своей актуальностью. Именно поэтому нашу сегодняшнюю статью мы решили посвятить теме, связанной с объединением запросов, получении последних по дате документов в 1С запросе и соединением запросов. Давайте все таки начнем.
Итак, для того, чтобы создать в системе 1С:Предприятие пакетный запрос следует использовать предложение ПОМЕСТИТЬ. Пакетный запрос подразумевают под собой сразу же несколько запросов, описанных как один большой запрос. Все они выполняются последовательно один за другим. В результате использования любого из этих запросов, их можно поместить во внутрь временной таблицы и потом обратится к ней используя для этого уже какой то другой запрос. Наверное большинство из вас спросят: в чем же плюсы этого метода?
У нас есть ответ на этот вопрос. Лично я считаю, что самым большим преимуществом этого метода является возможность помещать его результаты во внутрь так называемой временной таблицы. В ней они могут использоваться несколько раз. Более того, вам при всем этом не нужно будет несколько раз подряд выполнять один и тот же вложенный запрос, все что вам будет нужно это просто напросто выбирать данные из уже готовой таблицы. Некоторые из вас спросят: есть ли в этой методике какие то минусы. Безусловно, как и во всем в нашем мире в этой методике есть некоторые, определенные недостатки.
К самым существенным можно отнести то, что во время использования временных таблиц в пакетных запросах система может формировать в базе данных вполне реальные таблицы. Возможно некоторые спросят: так в что же здесь такого плохого? С одной стороны это хорошо. Но, с другой вы будете тратить значительную часть своего времени для того, чтобы создавать запросы, а после того, как вы их используете, вам придется уделить некоторое время еще и для того, чтобы удалить их.
Вот, например:
ВЫБРАТЬ
Номенклатура,
Сумма(Сумма) КАК Сумма,
ПОМЕСТИТЬ ДокПриход
ИЗ
В результате, после того, как вы выполните этот запрос выведется только то количество записей, которое было помещено в таблицу. Результаты при этом будут находится в той же самой таблице. Давайте сделаем подобный запрос для расходного документа. Для этого нам потребуется разделить эти два запроса с помощью символа ";". Именно он способен рассказать пользователю о том, что закончился один пакетный запрос и начинается другой.
ВЫБРАТЬ
Номенклатура,
Сумма(Количество) КАК Количество,
Сумма(Сумма) КАК Сумма,
ПОМЕСТИТЬ ДокПриход
ИЗ
Документ.ПоступлениеТоваровУслуг.Товары
СГРУППИРОВАТЬ ПО Номенклатура
;
ВЫБРАТЬ
Номенклатура,
Сумма(Количество) КАК Количество,
Сумма(Сумма) КАК Сумма,
ПОМЕСТИТЬ ДокРасход
ИЗ
Документ.РеализацияТоваровУслуг.Товары
СГРУППИРОВАТЬ ПО Номенклатура
;
ВЫБРАТЬ
*
ИЗ
ДокПриход КАК ДокПриход
ПОЛНОЕ СОЕДИНЕНИЕ
ДокРасход КАК ДокРасход
ПО
Из этого можно увидеть, что третий запрос соединяет данные из этих двух запросов.
Давайте теперь попытаемся привести запрос к еще более красивому виду. Для этого в результате запроса попытаемся понять какие суммы и количество у нас были приходными, а какие расходными. Для того, чтобы сделать это следует немного изменить текст третьего запроса.
ВЫБРАТЬ
ДокПриход.Номенклатура,
ДокРасход.Номенклатура,
ИЗ
ДокПриход КАК ДокПриход
ПОЛНОЕ СОЕДИНЕНИЕ
ДокРасход КАК ДокРасход
ПО
ДокПриход.Номенклатура = ДокРасход.Номенклатура
В результате мы получим данные по приходу и расходу. Но, у нас все же осталась проблема, связанная с тем, каким образом можно было бы объединить два столбца, «Номенклатура» и «Номенклатура1»? В том случае, если брать значение из столбца «Номенклатура», то мы в итоге так и не получим значение из последней строки, ну а если же брать значение из столбца «Номенклатура1», то не получим в итоге значение из третьей строки. Для этого следует воспользоватся функцией «ЕСТЬNULL».
В итоге запрос будет иметь такой вид:
ВЫБРАТЬ
КАК Номенклатура,
ДокПриход.Количество КАК КолПриход,
ДокПриход.Сумма КАК СумПриход,
ДокРасход.Количество КАК КолРасход,
ДокРасход.Сумма КАК СумРасход
ИЗ
ДокПриход КАК ДокПриход
ПОЛНОЕ СОЕДИНЕНИЕ
ДокРасход КАК ДокРасход
ПО
ДокПриход.Номенклатура = ДокРасход.Номенклатура
При этом может возникнуть еще одна проблема, связанная с тем, каким образом можно было бы заменить значения на числовое ноль? Для этого вам опять придется воспользоватся функцией «ЕСТЬNULL» и написать запрос:
ВЫБРАТЬ
ЕСТЬNULL(ДокПриход.Номенклатура,ДокРасход.Номенклатура)
КАК Номенклатура,
ЕСТЬNULL(ДокПриход.Количество,0) КАК КолПриход,
ЕСТЬNULL(ДокПриход.Сумма,0) КАК СумПриход,
ЕСТЬNULL(ДокРасход.Количество,0) КАК КолРасход,
ЕСТЬNULL(ДокРасход.Сумма,0) КАК СумРасход
ИЗ
ДокПриход КАК ДокПриход
ПОЛНОЕ СОЕДИНЕНИЕ
ДокРасход КАК ДокРасход
ПО
ДокПриход.Номенклатура = ДокРасход.Номенклатура