Имея некоторый опыт разработки, не раз приходилось видеть, насколько нерационально и громоздко порой строятся запросы начинающими разработчиками к информационным базам 1С. Существует достаточно простой и удобный механизм временных таблиц, при использовании которого можно разбить большой и плохо читаемый запрос на несколько небольших. Можно так же по некоторому алгоритму сформировать таблицу значений, поместить ее во временную таблицу и выполнить по ней запрос.
В данной публикации на примере процедуры проведения документа Реализация, не вникая в детали алгоритма проведения, описываются некоторые приемы использования временных таблиц в запросах к информационной базе на платформе 8.2. Автор публикации не претендует на полноту освещения вопроса или методическую безупречность.
В данной публикации на примере процедуры проведения документа Реализация, не вникая в детали алгоритма проведения, описываются некоторые приемы использования временных таблиц в запросах к информационной базе на платформе 8.2. Автор публикации не претендует на полноту освещения вопроса или методическую безупречность.
Рассмотрим пример работы с временными таблицами в конкретном запросе.
1. Создается таблица значений с указанием типов колонок. Типы колонок для таблицы значений, которая будет использоваться в запросе, обязательно должны быть определены (в тексте программного модуля номера и краткое описание разделов указаны в комментарии).
2. Таблица значений заполняется по некоторому произвольному алгоритму. В данном примере она дублирует движения документа.
3. Копируются отдельные таблицы значений для использования в условиях запроса.
4. Создается объект менеджер временных таблиц. Менеджер временных таблиц предназначен для хранения информации о временных таблицах, которые участвуют в запросе. При уничтожении объекта временные таблицы, находящиеся в нем, удаляются. Для временных таблиц, участвующих в одном запросе, должен использоваться единый менеджер временных таблиц.
5. Свойствам МенеджерВременныхТаблиц всех запросов, где участвуют временные таблицы, присваивается значение созданного ранее менеджера временных таблиц.
6. Формируются временные таблицы. При необходимости можно сформировать требуемое количество временных таблиц путем выполнения запросов к заполненной ранее таблице значений. Важной особенностью является то, что таблица значений непосредственно не может участвовать в запросах, где используются соединения с другими таблицами или агрегатные функции (Сумма, Среднее, Максимум, Минимум и др.). Для этого предварительно следует простым запросом, где в качестве параметра используется ссылка на таблицу значений, поместить ее во временную таблицу.
7. Временные таблицы используются в запросе. В приведенном запросе они используются в условии виртуальной таблицы регистра накопления в виде небольших вложенных запросов, а так же временная таблица связана внутренним соединением с виртуальной таблицей регистра.
Процедура ПроведениеПоУУ(Отказ, Режим)
Перем ЗапрОтрОст, РезЗапрОтрОст, ВыбЗапрОтрОст, Гран;
Перем КонтрольОстатков, ТекстСообщ, НовСтрокаТзНомен, ТзНом, СуммаУсл;
Перем МенВрТаб, ЗапрНом, ЗапрКач, ИмяНабора, ИмяПоля, ТзКач, ТзНмСтр;
// 1. создается таблица значений с указанием типов колонок
ТзНомен = Новый ТаблицаЗначений;
ТзНомен.Колонки.Добавить("Набор", Новый ОписаниеТипов("СправочникСсылка.Номенклатура"));
ТзНомен.Колонки.Добавить("Номенклатура", Новый ОписаниеТипов("СправочникСсылка.Номенклатура"));
ТзНомен.Колонки.Добавить("Качество", Новый ОписаниеТипов("СправочникСсылка.КачествоТовара"));
ТзНомен.Колонки.Добавить("Количество", Новый ОписаниеТипов("Число", Новый КвалификаторыЧисла(12,3)));
ТзНомен.Колонки.Добавить("НомерСтроки", Новый ОписаниеТипов("Число", Новый КвалификаторыЧисла(5,0)));
// 2. таблица значений заполняется по некоторому алгоритму
КонтрольОстатков = Истина;
Для Каждого ТекСтрокаТовары Из Товары Цикл
// регистр ОстаткиТоваров Расход
Если ТекСтрокаТовары.Номенклатура.ВидНоменклатуры=Перечисления.ВидНоменклатуры.Товар Тогда
Движение = Движения.ОстаткиТоваров.ДобавитьРасход();
Движение.Период = Дата;
Движение.Подразделение = Подразделение;
Движение.Номенклатура = ТекСтрокаТовары.Номенклатура;
Движение.Качество = ТекСтрокаТовары.Качество;
Движение.Количество = ТекСтрокаТовары.Количество * ТекСтрокаТовары.К;
НовСтрокаТзНомен = ТзНомен.Добавить();
НовСтрокаТзНомен.Набор = Справочники.Номенклатура.ПустаяСсылка();
НовСтрокаТзНомен.Номенклатура = ТекСтрокаТовары.Номенклатура;
НовСтрокаТзНомен.Качество = ТекСтрокаТовары.Качество;
НовСтрокаТзНомен.Количество = ТекСтрокаТовары.Количество * ТекСтрокаТовары.К;
НовСтрокаТзНомен.НомерСтроки = ТекСтрокаТовары.НомерСтроки;
ИначеЕсли ТекСтрокаТовары.Номенклатура.ВидНоменклатуры=Перечисления.ВидНоменклатуры.Набор Тогда
ТзНабор = РаботаСНаборамиСервер.ПолучитьСоставНабора(ТекСтрокаТовары.Номенклатура);
Для Каждого ТекСтрокаНабор Из ТзНабор Цикл
Движение = Движения.ОстаткиТоваров.ДобавитьРасход();
Движение.Период = Дата;
Движение.Подразделение = Подразделение;
Движение.Номенклатура = ТекСтрокаНабор.Номенклатура;
Движение.Качество = ТекСтрокаТовары.Качество;
Движение.Количество = ТекСтрокаНабор.Количество * ТекСтрокаТовары.Количество * ТекСтрокаТовары.К;
НовСтрокаТзНомен = ТзНомен.Добавить();
НовСтрокаТзНомен.Набор = ТекСтрокаТовары.Номенклатура;
НовСтрокаТзНомен.Номенклатура = ТекСтрокаНабор.Номенклатура;
НовСтрокаТзНомен.Качество = ТекСтрокаТовары.Качество;
НовСтрокаТзНомен.Количество = ТекСтрокаНабор.Количество * ТекСтрокаТовары.Количество * ТекСтрокаТовары.К;
НовСтрокаТзНомен.НомерСтроки = ТекСтрокаТовары.НомерСтроки;
КонецЦикла;
КонецЕсли;
КонецЦикла;
Движения.ОстаткиТоваров.Записать();
ТзНом = Новый ТаблицаЗначений;
ТзКач = Новый ТаблицаЗначений;
ТзНмСтр = Новый ТаблицаЗначений;
// 3. копируются отдельные таблицы значений для использования в условиях запроса
ТзНом = ТзНомен.Скопировать();
ТзНмСтр = ТзНомен.Скопировать();
ТзКач = ТзНомен.Скопировать();
ТзНом.Свернуть("Номенклатура");
ТзКач.Свернуть("Качество");
// 4. создается менеджер временных таблиц
МенВрТаб = Новый МенеджерВременныхТаблиц;
// временная таблица номенклатуры
ЗапрНом = Новый Запрос;
// 5. свойству МенеджерВременныхТаблиц запроса присваивается значение
// созданного ранее менеджера временных таблиц
ЗапрНом.МенеджерВременныхТаблиц = МенВрТаб;
// 6. формируются временные таблицы
ЗапрНом.Текст = "ВЫБРАТЬ
| Номен.Номенклатура
|ПОМЕСТИТЬ ВыбНомен // результат запроса помещается во временную таблицу ВыбНомен
|ИЗ
| &ТзНомен КАК Номен";
ЗапрНом.УстановитьПараметр("ТзНомен", ТзНом);
ЗапрНом.Выполнить();
// временная таблица качества
ЗапрКач = Новый Запрос;
ЗапрКач.МенеджерВременныхТаблиц = МенВрТаб;
ЗапрКач.Текст = "ВЫБРАТЬ
| Кач.Качество
|ПОМЕСТИТЬ ВыбКач // результат запроса помещается во временную таблицу ВыбКач
|ИЗ
| &ТзНомен КАК Кач";
ЗапрКач.УстановитьПараметр("ТзНомен", ТзКач);
ЗапрКач.Выполнить();
// временная таблица номеров строк
ЗапрНмСтр = Новый Запрос;
ЗапрНмСтр.МенеджерВременныхТаблиц = МенВрТаб;
ЗапрНмСтр.Текст = "ВЫБРАТЬ
| НмСтр.Номенклатура,
| НмСтр.Качество,
| НмСтр.НомерСтроки,
| НмСтр.Набор
|ПОМЕСТИТЬ ВыбНмСтр // результат запроса помещается во временную таблицу ВыбНмСтр
|ИЗ
| &ТзНомен КАК НмСтр";
ЗапрНмСтр.УстановитьПараметр("ТзНомен", ТзНмСтр);
ЗапрНмСтр.Выполнить();
// 7. временные таблицы используются в запросе
ЗапрОтрОст = Новый Запрос;
ЗапрОтрОст.МенеджерВременныхТаблиц = МенВрТаб;
ЗапрОтрОст.Текст = "ВЫБРАТЬ
| ОстТов.Номенклатура,
| ОстТов.Качество,
| ОстТов.КоличествоОстаток КАК Нехватка,
| ДокРеал.НомерСтроки,
| ДокРеал.Набор
|ИЗ
| РегистрНакопления.ОстаткиТоваров.Остатки(
| &Момент,
| Номенклатура В
| (ВЫБРАТЬ
| ВыбНомен.Номенклатура
| ИЗ
| ВыбНомен КАК ВыбНомен) // временная таблица в условии
| И Качество В // виртуальной таблицы регистра
| (ВЫБРАТЬ
| ВыбКач.Качество
| ИЗ
| ВыбКач КАК ВыбКач) // временная таблица в условии
| И Подразделение = &Подразд) КАК ОстТов // виртуальной таблицы регистра
| ВНУТРЕННЕЕ СОЕДИНЕНИЕ (ВЫБРАТЬ
| ДокРеалТовары.Набор КАК Набор,
| ДокРеалТовары.Номенклатура КАК Номенклатура,
| ДокРеалТовары.Качество КАК Качество,
| МАКСИМУМ(ДокРеалТовары.НомерСтроки) КАК НомерСтроки
| ИЗ
| ВыбНмСтр КАК ДокРеалТовары // временная таблица связана внутренним соединением
| // с виртуальной таблицей регистра
| СГРУППИРОВАТЬ ПО
| ДокРеалТовары.Набор,
| ДокРеалТовары.Номенклатура,
| ДокРеалТовары.Качество) КАК ДокРеал
| ПО ОстТов.Номенклатура = ДокРеал.Номенклатура
| И ОстТов.Качество = ДокРеал.Качество
|ГДЕ
| ОстТов.КоличествоОстаток < 0";
Гран = Новый Граница(МоментВремени(), ВидГраницы.Включая);
ЗапрОтрОст.УстановитьПараметр("Момент", Гран);
ЗапрОтрОст.УстановитьПараметр("Подразд", Подразделение);
РезЗапрОтрОст = ЗапрОтрОст.Выполнить();
Если Не РезЗапрОтрОст.Пустой() Тогда
Если КонтрольОстатков И Режим=РежимПроведенияДокумента.Оперативный Тогда
Отказ = Истина;
КонецЕсли;
ВыбЗапрОтрОст = РезЗапрОтрОст.Выбрать();
Пока ВыбЗапрОтрОст.Следующий() Цикл
Если Не ВыбЗапрОтрОст.Набор.Пустая() Тогда
ИмяНабора = " в составе набора - "+СокрЛП(ВыбЗапрОтрОст.Набор.Наименование);
ИмяПоля = "].Номенклатура"
Иначе
ИмяНабора = "";
ИмяПоля = "].Количество"
КонецЕсли;
Сообщение = Новый СообщениеПользователю;
ТекстСообщ = "Упр.учет: нехватка товара - "+СокрЛП(ВыбЗапрОтрОст.Номенклатура.Наименование)+ИмяНабора
+", качество - "+ВыбЗапрОтрОст.Качество.Наименование
+" в количестве "+(-ВыбЗапрОтрОст.Нехватка);
Сообщение.Текст = ТекстСообщ;
Сообщение.Поле = "Товары["+(ВыбЗапрОтрОст.НомерСтроки-1)+ИмяПоля;
Сообщение.УстановитьДанные(ЭтотОбъект);
Сообщение.Сообщить();
КонецЦикла
КонецЕсли;
КонецПроцедуры // ПроведениеПоУУ
Related Posts
Получение логина и пароля техподдержки 1С из базы
Класс для вывода отчета в Excel
Счет-фактура для УПП
Библиотека классов для создания внешней компоненты 1С на C#- Акт об оказании услуг (со скидками) — внешняя печатная форма для Управление торговлей 11.1.10.86
Прайс-лист с артикулом в отдельной колонке
(0) Старо. Пример в статье можно реализовать пакетным запросом с большей эффективностью и наглядностью.
Стоит сказать, что огульное использование временных таблиц тоже может быть нерациональным. Все таки они занимают место в оперативной памяти и если перестараться, то СУБД может скинуть промежуточные результаты запроса на жесткий диск и производительность резко просядет.
Нельзя такие статьи пропускать, ну просто снос крыши. У Вас подход к решению задачи не верный изначально. Верно сказали в (1) — все можно решить пакетным запросом, тем более в вашем случае — Вы в цикле получаете состав комплекта (набора).
Могу вам привести для примера хороший и очень быстрый контроль остатков на ВТ и пакетном запросе, если хотите
(0) (3) Тем более, что в примере не используются какие-то временные расчеты, которые можно передать в другие процедуры или запросы. Всё решается в одном блоке. Автор запоздал с менеджерами временных таблиц года на четыре, когда ещё не было пакетных запросов.
(3) Статья Чистова, ставшая классикой. Именно с неё я и начал использование пакетов.
(4) согласен, ВТ нужны только для передачи данных запроса между процедурами или временно сохранить результат, когда это действительно необходимо и пакетный запрос использовать невозможно, например сравнить данные движений по регистрам до и после проведения — ВТ можно передать через ДополнительныеСвойства документа.
+ .. за старание .. работа то какая-товсе равно проделана
(0) (6) Не, автору следовало бы направить затраченную энергию на изучение матчасти.
Обоснование минуса в постах (2) (3) (4) (5).
(6) DoctorRoza, ну видимо вам никогда не приходилось такие старания переписывать за кем-то
(2) khaoos,
Временные таблицы не хранятся в оперативной памяти. Они создаются и сохраняться в файле tempdb.
(0), минус ставить не буду. Но статье не место здесь, так как она учит как делать не стоит..
(7) полностью согласен
(4)
ну уж не на 4 года, а на все 8… когда там 8.1 появилась?
И потом, применение МВТ или пакетого запроса… суть разве в этом?
Применение таких запросов с ВТ и что можно получать — вот где сама суть.
Да только никто не может так глубоко раскопать… проще из типовой взять пример, желательно на 5 разворотов, и расписать, что вот, в примере такие вот инструкции применены — а зачем, почему именно они, какие альтернативы и когда — это уже не важно….
(9) kilokilo, спасибо за просвещение. Теперь буду еще осторожнее использовать временные таблицы :). Вроде в MSSQL есть еще переменные table, которые в оперативке хранятся. 1С всегда использует именно те временные таблицы, которые в tempdb создаются? Как в остальных используемых СУБД дело обстоит, не знаете?
(11) Платформу 8.1 в промышленную эксплуатацию ввели во второй половине 2007 года, менеджеры временных таблиц появились году в 2008, пакетные запросы примерно в начале 2009, то есть не так уж давно.
А в чем фишка то? Этими примерами «кишат» все типовые конфигурации.
(9) kilokilo, (12) khaoos, в SQL временные таблицы (ВТ) хранятся в отдельной, нетранзакционной базе данных (tempDb). В зависимости от версии sql сами таблицы базы данных либо висят в памяти сервера, либо лежат на диске (в случае использования МВТ на сервере) или на клиенте (в случае МВТ на клиенте). В файловом варианте все валяется внутри файлов БД
Даешь пример использования ВТ на примере расчета переходящего больничного из типовой ЗУП!
(16) mr zafod, ЗУП — это рай для любителей пододбных запросов 🙂
(15) mr zafod, если знаете подробности насчет версий, в каких что используется, прошу поделиться
(12) khaoos,
а название «Временные таблицы» ни о чем вам даже не намекнуло? 🙂
(15) mr zafod,
откуда столь точные и противоречивые данные?
(13)
ну значит, я слишком много работал с 8.0, и сразу перешел к использованию МВТ и пакетных запросов.
Вот бету 8.2 осенью 2009 году помню… Почему так хорошо — два года потом плевался от неё, и до сих пор готово ведро помоев на это поделие…
(18) khaoos, (19) AlexO, По поводу места хранения данных ВТ — я об этом узнал от самих 1С, методично задавая вопросы в поддержку. У меня есть коммерческий продукт «Загрузка данных из StoreHouse», работает он на ура с кешированием, отборами и кучей всяких интересных вещей — для этого активно используются ВТ (и МВТ), поэтому в свое время пришлось окунуться в эту область.
SQL Server 2008 R2 держит в памяти — в этом можно убедиться (и даже посмотреть содержимое таблиц) с помощью штатного монитора ресурсов (вроде так называется). В PG SQL 9.х — тоже в памяти, там даже на БД с ВТ висят какие-то интересные триггеры. За остальные не знаю, в указанных видел сам. Я так думаю, что эти области хранения ограниченны в объеме и работают как стек fifo.
(19) AlexO, вы намекаете на слово «временная» или на слово «таблица»? 🙂 Ни то ни то мне не намекает, что этот объект должен обязательно физически записываться на диск. Я думал оптимизатор СУБД решает, где хранить эту таблицу.
(21) mr zafod, спасибо. Это хорошо согласовывается с моими скромными теоретическими познаниями в области БД :). Уж слишком накладно было бы при каждом случае производить физическую запись на диск. При желании конечно можно сделать небольшие эксперименты. Правда дома у меня только Postgre стоит.
Совсем забыл — в случае файлового варианта ВТ храниться в памяти клиента в кеше (копии 1Cv8.1CD) и не вытесняются. В этом виде использование ВТ наиболее опасно!
Я думаю что главный смысл ВТ — это чтение данных из предварительно подготовленной ограниченной таблицы ВТ вместо чтения из возможно очень огромной таблицы скажем регистра. А все остальные фокусы с ВТ — просто для удобста разработки.
(25) mr zafod, несомненный плюс ВТ — в удобочитаемости запроса и повторного использования тяжело извлекаемых данных. Ну или когда считаешь себя умнее СУБД, и отказываешься от Loop Join’ов по индексному доступу в пользу некоторого подобия Hash Join’ов 🙂
(26) khaoos, в PG SQL кстати сейчас глянул для интереса на tempDb висит что-то типа autotruncate on checkpoint — видимо когда ВТ умирает постгре не убивает ее структуру как в обычных таблицах БД. А может 1С чего-то не допилили.
(27) mr zafod, когда kilokilo мне ответил, то я немного покопал, вроде на форумах писали, что в MSSQL тоже делается truncate. А таблицы умирают, когда умирают все сеансы 1С. Ну это мож в более ранних версиях платформы, я не в курсе.
(28) khaoos, ну триггеры и команды для 1Совских БД создаются платформой — в MSSQL такого платформа не делает, может там какой-то встроенных механизм есть?
(29) mr zafod, могу предположить (но обманывать не буду, надо б в профайлер или тех.журнал смотреть), что при трансляции запросов УНИЧТОЖИТЬ переведется в truncate (не уверен что так может оператор называться) и при закрытии МВТ аналогично
Устаревшая статья. За последние два года менеджер временных таблиц практически не использую.
К вышесказанному добавлю еще одну ложку дёгтя. Ни в одной из ВТ не использована индексация.
Тут приведен пример работы с ВТ. А использовать его или нет каждый сам решает.
(0) Только бы код еще раскрасить…
(32) ok0504,
Индексация имеет смысл, если временная таблица используется как источник данных несколько раз. В приведенном примере она не нужна.
(33) marku,
Пакетный запрос — это тоже пример с использованием ВТ. В данном случае приведен пример использования не ВТ, а МВТ, причем далеко не в самом выгодном контексте. После появления пакетных запросов, полезность МВТ свелась единственно к переносу временных таблиц между запросами, расположенными в разных частях кода.
Попал сюда с голосования от «Spec8.RU». Сам смотрел некоторые курсы, очень полезные. Удивлен прочитанным.
Комментарий только один — ни когда так не делайте!
Не буду даже ввязываться в полемику об использовании внешних таблиц в запросах, так как просто не хотелось читать код, написанный автором. Если автор не в курсе, что есть определённая стилистика при написании любого программного кода, могу подсказать, что имена переменных должны нести определённую смысловую нагрузку. Например, переменная, именованная в примере как «ТзНмСтр» при прокрутке страницы просто «теряется»… Точнее, теряется смысл её наименования и становится не понятен тому, кто читает этот код. Приходится возвращаться к тому месту, где эта переменная была определена, чтобы понять её назначение. Однозначный минус за неуважение к коллегам, для которых была предназначена эта статья!
(9) kilokilo,
Ага, временные таблицы МВТ хранятся во временном файле, что повышает нагрузку на диск.
Уж лучше бы в они хранились оперативной памяти )))
Жаль, минус поставить не могу.
Спасибо за старания
Я так понимаю, что автор предназначал свою работу неопытным программистам. И статья им может быть полезна. Начнут изучать ВТ и прочитают про пакеты.
И не стал бы критиковать за ВТ, а вот про написание кода (я промолчу про имена переменных, уже было) скажу.
Зачем копировать и сжимать ТЗНомен
// 3. копируются отдельные таблицы значений для использования в условиях запроса
ТзНом = ТзНомен.Скопировать();
ТзНмСтр = ТзНомен.Скопировать();
ТзКач = ТзНомен.Скопировать();
ТзНом.Свернуть(«Номенклатура»);
ТзКач.Свернуть(«Качество»);
Если вот здесь
// 6. формируются временные таблицы
ЗапрНом.Текст = «ВЫБРАТЬ
| Номен.Номенклатура
|ПОМЕСТИТЬ ВыбНомен // результат запроса помещается во временную таблицу ВыбНомен
|ИЗ
| &ТзНомен КАК Номен»;
из ТЗ ТзНомен можно сразу получить ВыбНомен используя слово «Различные».
Согласен свертка — совсем не к чему.
(1)
После использования пакетных запросов везде, где попало, оптимизатор запросов, который без изменений, фактически, живет еще с Sybase, обламывается на 16 подзапросе и давай, до свидания вся Ваша хваленая производительность, как и читабельность. Привык народ фигачить монструозные запросы. Посидели-бы Вы на старом добром аксессе, во времена windows 95. Ставлю плюс, подход понадобился при соединении по оле к хх базам и сборе данных из них в одну виртуальную таблицу.
(45)
Кудахтают петухи, сохраняйте приличия
Я видел девочек, которых научили писать SQL запросы, вот они любили рисовать полотенца на T-SQL, где пока дочитаешь до конца, уже напрочь забывал, что было вначале. Не учите всех делать некрасиво и нечитабельно, вопрос производительности — спорный, но я думаю, что архитектурные конструкции автора статьи вполне бодро работают.
(37) premier,
Поддерживаю, товарищи! На кол срочно, товарищи!
Добавлю свое мнение:
пакетные запросы хороши, но есть в менеджере свой плюс.
когда пакетов 5-10 и все выполняется на сервере то бывает иногда что запрос не выполняется и понять какой-же запрос тебя подвел не всегда ясно.
на клиенте этот такой пакетный запрос выполнится, но на сервере бывает нет. Всему виной соединения таблиц и громоздкие условия.
так что менеджер временных таблиц пока еще имеет место быть 😉
Пример нормальный +