При работе с периодическими регистрами сведений в 1С разработчики часто сталкиваются со следующей задачей: необходимо получить актуальные данные (последние записи) на определенную дату, но не по всем измерениям, которые заданы в структуре регистра, а только по некоторым из них. Типичный пример — регистр ЦеныНоменклатуры, где измерениями выступают Номенклатура и ТипЦены, а ресурсом — Цена. Перед нами стоит задача: получить последнюю установленную цену по каждой номенклатуре, независимо от того, к какому типу цен она относится.
Если написать простой запрос к виртуальной таблице СрезПоследних, СУБД вернет несколько строк для одной и той же номенклатуры, если для нее в системе зарегистрированы разные типы цен (например, оптовая и розничная). В этой статье мы подробно разберем, почему стандартный механизм работает именно так, проанализируем производительность различных подходов и построим правильный, оптимизированный запрос для решения этой задачи.
Для того чтобы понять суть проблемы, необходимо обратиться к архитектуре платформы 1С:Предприятие. Виртуальная таблица СрезПоследних всегда рассчитывает актуальность записей в разрезе абсолютно всех измерений, определенных в метаданных регистра сведений.
Даже если в секции ВЫБРАТЬ вашего запроса вы укажете только поля Номенклатура и Цена, проигнорировав ТипЦены, платформа на уровне СУБД все равно выполнит группировку по связке Номенклатура + ТипЦены. В результате СУБД вернет актуальную цену для каждого существующего типа цен этой номенклатуры. На выходе вы получите дублирующиеся строки для одного и того же товара, что противоречит исходной постановке задачи.
Рассмотрим ситуацию на примере. В регистре записаны следующие данные:
Если мы хотим получить самую последнюю цену вообще, нам должна вернуться одна строка: Товар 1 с ценой 150 руб. от 5 октября. Однако стандартный «Срез последних» вернет обе строки, поскольку для каждого типа цен эти записи являются «последними».
Существует соблазн решить эту задачу «в лоб» через физическую таблицу регистра сведений. Схема такого решения выглядит так: сгруппировать физическую таблицу по номенклатуре, найти МАКСИМУМ(Период), а затем соединить полученный результат с этой же таблицей по полям Номенклатура и Период для получения цены.
Проанализируем этот подход с точки зрения производительности:
СрезПоследних. Она по умолчанию отсекает всю историю изменений, оставляя в выборке только актуальные записи на текущий момент (по одной строке на каждую уникальную комбинацию номенклатуры и типа цен). Объём этих данных несравнимо меньше всей физической таблицы. И уже из этого подготовленного небольшого набора данных мы будем выбирать финальный максимум по периоду.Рассмотрим подробнее классическое решение этой задачи с использованием временных таблиц. Мы разобьем этот процесс на два основных шага.
Шаг 1. Получение среза последних по всем измерениям во временную таблицу
Сначала мы выберем данные из виртуальной таблицы СрезПоследних и поместим их во временную таблицу. Это позволит нам зафиксировать «актуальный срез» и не обращаться к тяжелой виртуальной таблице повторно.
Шаг 2. Выборка максимального периода и соединение данных
Затем мы выберем из временной таблицы уникальную номенклатуру и ее максимальный период. Полученный результат мы соединим с исходной временной таблицей по полям Номенклатура и Период, чтобы дотянуться до ресурса Цена.
Посмотрим на пример реализации такого запроса:
ВЫБРАТЬ
ЦеныНоменклатурыСрезПоследних.Номенклатура КАК Номенклатура,
ЦеныНоменклатурыСрезПоследних.Цена КАК Цена,
ЦеныНоменклатурыСрезПоследних.Период КАК Период
ПОМЕСТИТЬ втДанныеСреза
ИЗ
РегистрСведений.ЦеныНоменклатуры.СрезПоследних КАК ЦеныНоменклатурыСрезПоследних
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
втДанныеСреза.Номенклатура КАК Номенклатура,
МАКСИМУМ(втДанныеСреза.Цена) КАК Цена,
МаксимальныеДаты.МаксПериод КАК Период
ИЗ
(ВЫБРАТЬ
втДанные.Номенклатура КАК Номенклатура,
МАКСИМУМ(втДанные.Период) КАК МаксПериод
ИЗ
втДанныеСреза КАК втДанные
СГРУППИРОВАТЬ ПО
втДанные.Номенклатура) КАК МаксимальныеДаты
ВНУТРЕННЕЕ СОЕДИНЕНИЕ втДанныеСреза КАК втДанныеСреза
ПО МаксимальныеДаты.Номенклатура = втДанныеСреза.Номенклатура
И МаксимальныеДаты.МаксПериод = втДанныеСреза.Период
СГРУППИРОВАТЬ ПО
втДанныеСреза.Номенклатура,
МаксимальныеДаты.МаксПериод
Приведенный выше запрос отлично справится с задачей на небольших и средних базах данных. Однако на крупных корпоративных системах (особенно под управлением СУБД PostgreSQL) отсутствие индексов у временной таблицы втДанныеСреза может сыграть злую шутку. Оптимизатор СУБД может выбрать неоптимальный план выполнения (например, медленный Nested Loop вместо быстрого Hash Join), что критически замедлит выполнение всего отчета или обработки.
Чтобы гарантировать мгновенное внутреннее соединение на уровне СУБД, мы должны применить индексирование временной таблицы по связующим полям. В нашем случае связующими полями выступают Номенклатура и Период. Добавим инструкцию индексирования в наш запрос:
ВЫБРАТЬ
ЦеныНоменклатурыСрезПоследних.Номенклатура КАК Номенклатура,
ЦеныНоменклатурыСрезПоследних.Цена КАК Цена,
ЦеныНоменклатурыСрезПоследних.Период КАК Период
ПОМЕСТИТЬ втДанныеСреза
ИЗ
РегистрСведений.ЦеныНоменклатуры.СрезПоследних КАК ЦеныНоменклатурыСрезПоследних
ИНДЕКСИРОВАТЬ ПО
Номенклатура,
Период
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
втДанныеСреза.Номенклатура КАК Номенклатура,
МАКСИМУМ(втДанныеСреза.Цена) КАК Цена,
МаксимальныеДаты.МаксПериод КАК Период
ИЗ
(ВЫБРАТЬ
втДанные.Номенклатура КАК Номенклатура,
МАКСИМУМ(втДанные.Период) КАК МаксПериод
ИЗ
втДанныеСреза КАК втДанные
СГРУППИРОВАТЬ ПО
втДанные.Номенклатура) КАК МаксимальныеДаты
ВНУТРЕННЕЕ СОЕДИНЕНИЕ втДанныеСреза КАК втДанныеСреза
ПО МаксимальныеДаты.Номенклатура = втДанныеСреза.Номенклатура
И МаксимальныеДаты.МаксПериод = втДанныеСреза.Период
СГРУППИРОВАТЬ ПО
втДанныеСреза.Номенклатура,
МаксимальныеДаты.МаксПериод
Важное примечание: Индексация временных таблиц оправдана в том случае, если количество записей в выборке превышает несколько десятков или сотен строк. Если во временную таблицу попадает всего 5-10 записей, затраты на создание индекса могут превысить выгоду от его использования.
Внимательно проанализируем поведение функции МАКСИМУМ(Цена) во внешнем запросе. Зачем она там нужна? Она решает проблему «коллизии дат».
Представим ситуацию: в одну и ту же секунду (или день, в зависимости от периодичности регистра) для одной и той же номенклатуры были установлены и Оптовая цена (100 руб.), и Розничная цена (150 руб.). В таком случае МАКСИМУМ(Период) у них будет абсолютно одинаковым. Внутреннее соединение вернет две строки для этой номенклатуры. Чтобы избавиться от дублирования строк на этом этапе, мы вынуждены использовать агрегирующую функцию МАКСИМУМ(Цена).
Однако с точки зрения логики бизнеса это компромиссное и не всегда корректное решение. База данных просто выберет ту цену, которая математически больше (в нашем случае — 150 руб.), хотя бизнес-логика вашей компании, возможно, требовала бы приоритета оптовой цены или определенного алгоритма выбора.
Как решать эту коллизию правильно, если это критично для бизнес-процесса?
ВЫБОР КОГДА ....Период (которое хранит только дату и время) для точного позиционирования во времени следует использовать поле МоментВремени. Оно гарантирует уникальность и четкую последовательность записей за счет ссылки на сам документ-регистратор, исключая возникновение коллизий при совпадении времени с точностью до секунды.Начиная с современных версий платформы 1С:Предприятие 8.3, разработчикам стал доступен новый мощный механизм оптимизации периодических регистров сведений. В свойствах регистра в конфигураторе можно включить опцию «Разрешить итоги: Срез последних».
Выясним причину, почему это кардинально меняет производительность работы запросов:
СрезПоследних (без параметров даты или на текущий момент) СУБД больше не тратит ресурсы на расчет актуальности записей «на лету» из всей истории. Данные мгновенно считываются напрямую из готовой таблицы итогов.Если в вашей конфигурации для регистра включен этот режим, использование предложенного нами запроса с временной таблицей втДанныеСреза становится максимально быстрым, эффективным и масштабируемым решением, которое без проблем выдержит работу в высоконагруженных системах с миллионными объемами данных.