Введение. Цель статьи
Привет всем! В настоящей статье я хочу затронуть такую злободневную тему как "запросы в цикле". Наверное, нет ни одного программиста 1с, который бы не использовал хоть раз запрос в цикле, а некоторые (и их очень много) считают это даже нормой программирования. Ну а что, многие могут подумать, раз свою задачу программа выполняет - можно и использовать - пользователи ведь довольны результатом и принимают его. И таких "отчетов" я встречал, ну наверное 70 - 80%..... как бы это было не печально или смешно.
Но, исходя из своей практики и опыта, однажды до меня "дошло", что использование запросов в цикле - это все-таки не такая уж универсальная и правильная штука и, в этой статье, я постараюсь объяснить на примерах почему, постараюсь показать на практике.
Понятно, что каждый "начинающий" программист, который умеет "собирать" колонки в таблице результата (например, для СКД) считает, что может построить любой отчет или обработку.
Отчасти это так. И вопрос как долго по времени будут "собираться данные в таблицу результата" - отнюдь не самый главный здесь. На ИТС, в разделе "оптимизация запросов" написано примерно так - что нужно не только добиваться поставленной цели, но и обеспечить комфорт и скорость работы пользователей с ним. Я с этим согласен полностью. Но это про каких-то там пользователей.... "пусть ночью запускают" - ответ "программистов", которые не могу совладать с оптимизацией.....
Я бы предложил свой аргумент, гораздо более весомый, подкрепленный моим практическим опытом, на мой взгляд - если программист привык "собирать" данные, используя запросы в цикле, то в плане развития, он никогда не научится решать более сложный класс задач, где использование запросов в цикле просто невозможно. Посмотрим какие задачи в этой статье дальше.
В статье я упомяну конфигурации Управление торговлей 10.3 и Розница 2.3. Задачи решались на платформе 8.3.15.1830.
Когда бы я использовал "запрос в цикле"
Странный раздел, да? Попробую объяснить вам почему я так назвал его. На примере. Была у меня как-то задачка лет 7 назад, где нужно было построить "своеобразный" АВС-анализ, который в корне отличался от типового. Это была достаточно большая городская розничная сеть, которая работала на УТ 10.3. Управляющая сама не знала, чего ей нужно, но "за первый заход" (конечно это был не совсем первый заход) нарисовала вот такую таблицу - "нужно мол вот так":
Рис.1. "Первоначальная таблица" АВС-анализа.
Ну как? Просто? Сейчас, возможно, да - это ведь обычные данные для "торгового" отчета - "производственные" таблицы "план/факт" в разы сложнее. С ними я тоже имел дело, работая в УПП, но не буду их касаться здесь.
Короче, в этой таблице почти половина колонок - это расчетные или получаемые на период времени величины. На тот момент как их считать им было нужно - непонятно. Все упиралось конечно в расчет "себестоимости" - то ли им нужно было по-партиям, то ли по стоимости последнего прихода, то ли по "средне-приходной". В итоге, они мне сказали, а сделай "галочку", чтобы считалась или так или так.... Пришлось сделать такую галочку:
Рис.2. Выбор алгоритма расчета себестоимости.
Дальше, исходные условия задачи менялись примерно раз в день. Если бы я начал делать данную задачу единым запросом, что считаю самым правильным методом решения подобных задач в ввиду постоянно меняющихся условий, я бы не закончил эту задачу до сих пор.
Как я подошел к решению данной задачи - воспользовался определенным правилом и вынес все "расчетные и получаемые величины" в свои собственные функции.
Изначально, я постарался оптимизировать свое личное время разработки данного отчета.
Первым шагом, я создал основной "базовый" простейший запрос. Пусть он будет вот такой:
ВЫБРАТЬ
ПродажиОбороты.Номенклатура,
СУММА(ПродажиОбороты.КоличествоОборот) КАК КоличествоОборот,
СУММА(ПродажиОбороты.СтоимостьОборот) КАК СтоимостьОборот,
ПродажиОбороты.Подразделение,
ПродажиОбороты.ДокументПродажи.Склад,
ПродажиОбороты.Организация,
ПродажиОбороты.ДокументПродажи.Дата
ИЗ
РегистрНакопления.Продажи.Обороты(&НачПериода, &КонПериода, Период, ТИПЗНАЧЕНИЯ(ДокументПродажи) = ТИП(Документ.ОтчетОРозничныхПродажах)) КАК ПродажиОбороты
СГРУППИРОВАТЬ ПО
ПродажиОбороты.Номенклатура,
ПродажиОбороты.Подразделение,
ПродажиОбороты.ДокументПродажи.Склад,
ПродажиОбороты.Организация,
ПродажиОбороты.ДокументПродажи.Дата
А затем, я создал внешние функции расчетов заданных величин:
Рис.3. Набор внешних функций для определения необходимых величин.
Функции я разместил в общем модуле ОбщийМодульАВС с параметрами Клиент, ВызовСервера, Привилегированный. Куча функций, куда я передаю параметры базового запроса - Номенклатура и Дата, но можно добавить больше.
Функция ВаловаяПрибыль(Номенклатура, Дата) Экспорт
//*** Содержимое функции ***
Возврат ВаловаяПрибыльРезультат;
КонецФункции
Функция КолвоМагазинов(Номенклатура, Дата) Экспорт
//*** Содержимое функции ***
Возврат КолвоМагазиновРезультат;
КонецФункции
Функция Поставщик(Номенклатура, Дата) Экспорт
//*** Содержимое функции ***
Возврат ПоставщикРезультат;
КонецФункции
Функция ПоследнийЗакуп(Номенклатура, Дата) Экспорт
//*** Содержимое функции ***
Возврат ПоследнийЗакупРезультат;
КонецФункции
Функция Выручка(Номенклатура, Дата) Экспорт
//*** Содержимое функции ***
Возврат ВыручкаРезультат;
КонецФункции
Функция ЧистаяПрибыль(Номенклатура, Дата) Экспорт
//*** Содержимое функции ***
Возврат ЧистаяПрибыльРезультат;
КонецФункции
Функция Списание(Номенклатура, Дата) Экспорт
//*** Содержимое функции ***
Возврат СписаниеРезультат;
КонецФункции
Функция СредняяЦенаПродажи(Номенклатура, Дата) Экспорт
//*** Содержимое функции ***
Возврат СредняяЦенаПродажиРезультат;
КонецФункции
Функция Наценка(Номенклатура, Дата) Экспорт
//*** Содержимое функции ***
Возврат НаценкаРезультат;
КонецФункции
Ну и в вынесенных функциях "я творил что угодно" : подгонял их под быстро-меняющиеся условия данной задачи. После того как результаты отчета были удовлетворительные, я немного оптимизировал данное решение ("а теперь попробуй его убыстрить" - меня так попросили) - получая некоторые величины сразу в запросе.
Небольшая по-шаговая оптимизация отчета
В этом разделе я приведу пример "оптимизации" функции получение последнего поставщика. Допустим, в вышеупомянутой функции я использовал "запрос в цикле", выбирал первого поставщика по номенклатуре и упорядочивал по дате убывания в возвращал в таблицу для СКД. В противовес этому запросу я буду использовать функцию языка запросов МАКСИМУМ. Наверное, в 80% я использую данный механизм, чтобы избежать запроса в цикле.
Запрос по определению поставщика номенклатуры выглядит вот так:
ВЫБРАТЬ
МАКСИМУМ(Закупки.Период) КАК Период,
Закупки.Номенклатура КАК Номенклатура
ПОМЕСТИТЬ ВТ_Номенклатура
ИЗ
РегистрНакопления.Закупки КАК Закупки
СГРУППИРОВАТЬ ПО
Закупки.Номенклатура
ИНДЕКСИРОВАТЬ ПО
Номенклатура
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
Закупки.Номенклатура,
МАКСИМУМ(Закупки.Контрагент) КАК Контрагент,
Закупки.Период КАК Период
ИЗ
РегистрНакопления.Закупки КАК Закупки
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Номенклатура КАК ВТ_Номенклатура
ПО Закупки.Номенклатура = ВТ_Номенклатура.Номенклатура
И Закупки.Период = ВТ_Номенклатура.Период
СГРУППИРОВАТЬ ПО
Закупки.Номенклатура,
Закупки.Период
Объясню запрос - первым шагом я выбираю Максимальную дату "Период" поступления номенклатуры на склад в регистре накопления "Закупки" по этой номенклатуре (это одна запись). А вторым шагом - я выбираю "Максимального" (в данном случае - единственного поставщика), в периоде времени по этой номенклатуре. Почему максимального - потому, что могут быть поступления по одной номенклатуре в одну дату и одно время. Это неоднозначность - исключается так же функцией МАКСИМУМ.
Вот так - Играясь с измерениями по Периоду регистра накопления "Закупки" (используя функцию МАКСИМУМ), я создал нужную мне временную таблицу, которую я соединю с "базовым" запросом.
Теперь, я могу исключить внешнюю функцию "Поставщик". Вот примерно таким способом, я стараюсь оптимизировать (ускорить) уже готовое решение.
Хочу отметить, что данный запрос я сильно упростил в статье, не использовал склады и т.п...
Когда использование исключено
После моего небольшого примера по оптимизации, постараюсь написать почему нужно избавляться от практики "запросов в цикле". Вот явный аргумент - -объект "динамический список", который очень часто используется в современных конфигурациях, в основе которого чаще всего лежит "произвольный запрос" и как бы не пытался - за один "присест" мне в динамическом списке необходимо собрать все данные одним запросом.
Вот пример. Конфигурация "Розница", справочник "Номенклатура", изначальное содержание динамического списка вот такое:
ВЫБРАТЬ
СправочникНоменклатура.Ссылка КАК Ссылка,
СправочникНоменклатура.Код КАК Код,
СправочникНоменклатура.Наименование КАК Наименование,
СправочникНоменклатура.ВидНоменклатуры КАК ВидНоменклатуры,
СправочникНоменклатура.ТоварнаяКатегория КАК ТоварнаяКатегория,
СправочникНоменклатура.Марка КАК Марка,
ВЫБОР
КОГДА СправочникНоменклатура.ЭтоГруппа
ТОГДА ВЫБОР
КОГДА СправочникНоменклатура.ПометкаУдаления
ТОГДА 7
ИНАЧЕ 6
КОНЕЦ
ИНАЧЕ ВЫБОР
КОГДА СправочникНоменклатура.ПометкаУдаления
ТОГДА 1
ИНАЧЕ 0
КОНЕЦ + ВЫБОР
КОГДА СправочникНоменклатура.ВидНоменклатуры.ИспользованиеХарактеристик = ЗНАЧЕНИЕ(Перечисление.ВариантыВеденияДополнительныхДанныхПоНоменклатуре.НеИспользовать)
ТОГДА 0
ИНАЧЕ 2
КОНЕЦ
КОНЕЦ КАК ИндексКартинки,
СправочникНоменклатура.ЕдиницаИзмерения КАК ЕдиницаИзмерения,
СправочникНоменклатура.Артикул КАК Артикул,
СправочникНоменклатура.НаименованиеПолное КАК НаименованиеПолное,
СправочникНоменклатура.Вес КАК Вес,
СправочникНоменклатура.СтавкаНДС КАК СтавкаНДС,
СправочникНоменклатура.ТипНоменклатуры КАК ТипНоменклатуры,
ТоварыНаСкладахОстатки.КоличествоОстаток КАК КоличествоОстаток
ИЗ
Справочник.Номенклатура КАК СправочникНоменклатура
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыНаСкладах.Остатки(&ТекДата, ) КАК ТоварыНаСкладахОстатки
ПО СправочникНоменклатура.Ссылка = ТоварыНаСкладахОстатки.Номенклатура
Заказчик хочет, чтобы в справочнике номенклатуры отображались дополнительно поля: Остаток в магазине на текущий момент, Последняя дата поступления и Поставщик. Я дорабатываю запрос динамического списка вот так и получаю эти поля на форму списка:
ВЫБРАТЬ
НоменклатураПоставщиков.Номенклатура КАК Номенклатура,
МАКСИМУМ(НоменклатураПоставщиков.ДатаПоследнегоПоступления) КАК ДатаПоследнегоПоступления
ПОМЕСТИТЬ ДатаПоступленияТаб
ИЗ
РегистрСведений.НоменклатураПоставщиков КАК НоменклатураПоставщиков
СГРУППИРОВАТЬ ПО
НоменклатураПоставщиков.Номенклатура
ИНДЕКСИРОВАТЬ ПО
Номенклатура
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
МАКСИМУМ(НоменклатураПоставщиков.Поставщик) КАК Поставщик,
НоменклатураПоставщиков.Номенклатура КАК Номенклатура,
НоменклатураПоставщиков.ДатаПоследнегоПоступления КАК ДатаПоследнегоПоступления
ПОМЕСТИТЬ ВТ_Поставщики
ИЗ
ДатаПоступленияТаб КАК ДатаПоступленияТаб
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.НоменклатураПоставщиков КАК НоменклатураПоставщиков
ПО ДатаПоступленияТаб.Номенклатура = НоменклатураПоставщиков.Номенклатура
И ДатаПоступленияТаб.ДатаПоследнегоПоступления = НоменклатураПоставщиков.ДатаПоследнегоПоступления
СГРУППИРОВАТЬ ПО
НоменклатураПоставщиков.Номенклатура,
НоменклатураПоставщиков.ДатаПоследнегоПоступления
ИНДЕКСИРОВАТЬ ПО
Поставщик,
Номенклатура
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
СправочникНоменклатура.Ссылка КАК Ссылка,
СправочникНоменклатура.Код КАК Код,
СправочникНоменклатура.Наименование КАК Наименование,
СправочникНоменклатура.ВидНоменклатуры КАК ВидНоменклатуры,
СправочникНоменклатура.ТоварнаяКатегория КАК ТоварнаяКатегория,
СправочникНоменклатура.Марка КАК Марка,
ВЫБОР
КОГДА СправочникНоменклатура.ЭтоГруппа
ТОГДА ВЫБОР
КОГДА СправочникНоменклатура.ПометкаУдаления
ТОГДА 7
ИНАЧЕ 6
КОНЕЦ
ИНАЧЕ ВЫБОР
КОГДА СправочникНоменклатура.ПометкаУдаления
ТОГДА 1
ИНАЧЕ 0
КОНЕЦ + ВЫБОР
КОГДА СправочникНоменклатура.ВидНоменклатуры.ИспользованиеХарактеристик = ЗНАЧЕНИЕ(Перечисление.ВариантыВеденияДополнительныхДанныхПоНоменклатуре.НеИспользовать)
ТОГДА 0
ИНАЧЕ 2
КОНЕЦ
КОНЕЦ КАК ИндексКартинки,
СправочникНоменклатура.ЕдиницаИзмерения КАК ЕдиницаИзмерения,
СправочникНоменклатура.Артикул КАК Артикул,
СправочникНоменклатура.НаименованиеПолное КАК НаименованиеПолное,
СправочникНоменклатура.Вес КАК Вес,
СправочникНоменклатура.СтавкаНДС КАК СтавкаНДС,
СправочникНоменклатура.ТипНоменклатуры КАК ТипНоменклатуры,
ТоварыНаСкладахОстатки.КоличествоОстаток КАК КоличествоОстаток,
ВТ_Поставщики.Поставщик КАК Поставщик,
ВТ_Поставщики.ДатаПоследнегоПоступления КАК ДатаПоследнегоПоступления
ИЗ
Справочник.Номенклатура КАК СправочникНоменклатура
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыНаСкладах.Остатки(&ТекДата, ) КАК ТоварыНаСкладахОстатки
ПО СправочникНоменклатура.Ссылка = ТоварыНаСкладахОстатки.Номенклатура
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Поставщики КАК ВТ_Поставщики
ПО СправочникНоменклатура.Ссылка = ВТ_Поставщики.Номенклатура
В этом примере, я вынужден сделать все в одном запросе, сразу исключив всякие неудобно-сомнительные конструкции типа "внешних функций" и "запросов в цикле"
Выводы
Спасибо, что дочитали данную спорную статью до выводов. Вывод данной статью в том - чтобы научиться экономить свое собственное время при постоянно меняющихся условиях - в "стресс-разработки", ввиду кучи неграмотных топ-менеджеров, которые в большинстве случаев и техническое задание не могут то представить перед началом работы, ну а менять его во время работы - это само-собой разумеющееся дело.
Я описал свою рабочую практику - это мой личный практический опыт. Применять его вам или нет - это ваш выбор и желание.
Так же, напишу еще раз здесь, что являюсь сторонником красивых решений задач. Красивое решение задачи - это решение, где единым запросом, получаем всю таблицу необходимых данных сразу на 100%, а затем "оформляем" эти данные либо в СКД, либо в обработке (независимо от того, строим ли мы отчет или обрабатываем документы).
Так же, в данный статье, я постарался объяснить и показал на практике, что правильное изучение механизмов запросов 1с и исключение из своей практики "запросов в цикле" поможет вам собирать и корректировать такой функционал платформы как "динамические списки". А без "динамических списков" сейчас не обходится ни одна современная конфигурация на управляемых формах.
Всем спасибо. Всем привет и до новых встреч!
Предыдущие материалы
Так же, я прошу посмотреть мои предыдущие статьи:
Динамический список. Апгрейд справочника "Номенклатура" типовой конфигурации с помощью расширения
Лайфхак работы с СКД. Собираем отчет
СКД. Лайфхак №2. Собираем отчет еще удобнее
СКД. Шаг 3. Используем макеты для оформления отчета
