Вопрос оптимального извлечения данных о номенклатуре с сопутствующей информацией, такой как текущие остатки или актуальные цены, является одним из ключевых при разработке высокопроизводительных систем на платформе 1С:Предприятие. На первый взгляд, задача кажется простой, однако для достижения минимального времени выполнения запроса требуется глубокое понимание механизмов работы платформы и умение применять различные техники оптимизации. В рамках этого обсуждения мы рассмотрим, как можно построить такой запрос наиболее эффективно, используя преимущества платформы 1С и общие принципы оптимизации SQL.
Начнем с анализа общих подходов, которые могут помочь нам существенно ускорить получение данных.
Одним из самых распространенных и на первый взгляд простых способов получения информации об остатках или ценах для каждой номенклатуры является использование левого соединения (ЛЕВОЕ СОЕДИНЕНИЕ) справочника Номенклатура с соответствующими регистрами. Этот подход позволяет получить все элементы номенклатуры, даже те, для которых нет данных в соединяемом регистре (например, нет остатков или не установлена цена).
Для получения остатков: РегистрНакопления.ТоварыНаСкладах.Остатки
Для определения текущих остатков товаров на складах мы будем использовать виртуальную таблицу Остатки регистра накопления ТоварыНаСкладах. Эта таблица специально предназначена для получения актуальных данных об остатках на определенную дату. Если нас интересуют текущие остатки без привязки к конкретной дате, мы можем вызвать ее без параметров даты, что в некоторых случаях позволяет платформе сгенерировать более эффективный запрос к физической таблице остатков.
Важный совет: всегда передавайте условия отбора непосредственно в параметры виртуальной таблицы Остатки. Например, если нам нужны остатки по определенным складам или видам номенклатуры, правильнее будет передать эти условия в саму функцию виртуальной таблицы, а не использовать их во внешней секции ГДЕ. Это позволяет платформе сформировать более оптимальный план запроса, исключив из обработки лишние данные на ранних этапах.
ВЫБРАТЬ
Номенклатура.Ссылка КАК Номенклатура,
Номенклатура.Наименование КАК Наименование,
Остатки.КоличествоОстаток КАК Количество
ИЗ
Справочник.Номенклатура КАК Номенклатура
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыНаСкладах.Остатки(
&ДатаОстатков,
Склад В (&СписокСкладов) И Номенклатура В (&СписокНоменклатуры)
) КАК Остатки
ПО Номенклатура.Ссылка = Остатки.Номенклатура
ГДЕ
Номенклатура.ЭтоГруппа = ЛОЖЬ
Для получения цен: РегистрСведений.ЦеныНоменклатуры.СрезПоследних
Аналогично остаткам, для получения актуальных цен номенклатуры мы можем использовать виртуальную таблицу СрезПоследних регистра сведений ЦеныНоменклатуры. Она позволяет получить последнюю запись регистра по каждому измерению на указанную дату.
Рассмотрим пример запроса, предложенного на форуме:
ВЫБРАТЬ
Номенклатура1.Наименование КАК Наименование,
Номенклатура1.Артикул КАК Артикул,
Цены.Цена КАК Цена
ИЗ
Справочник.Номенклатура КАК Номенклатура1
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры.СрезПоследних КАК Цены
ПО Номенклатура1.Ссылка = Цены.Номенклатура.Ссылка
ГДЕ
Номенклатура1.ЭтоГруппа = ЛОЖЬ
Здесь мы соединяем справочник Номенклатура с СрезПоследних регистра ЦеныНоменклатуры. Важно заметить, что в условии соединения мы используем Цены.Номенклатура.Ссылка, чтобы избежать ошибок типизации и гарантировать корректное сравнение ссылок. Если вас интересуют цены не на текущий момент, а на определенную дату, обязательно передавайте эту дату в параметры виртуальной таблицы СрезПоследних: РегистрСведений.ЦеныНоменклатуры.СрезПоследних(&ДатаСреза).
Использование регистра себестоимости
В некоторых случаях, если требуется получить не только цены или остатки, но и информацию о себестоимости, мы можем применить левое соединение с соответствующим регистром себестоимости. Принципы работы с ним будут аналогичны регистрам накопления, требуя корректного указания измерений и ресурсов.
Несмотря на кажущуюся простоту, запросы к виртуальным таблицам, таким как СрезПоследних и Остатки, могут быть весьма ресурсоемкими, особенно на больших объемах данных или при работе с СУБД PostgreSQL. Разберем подробнее, как избежать распространенных ловушек.
Передача условий в параметры виртуальных таблиц
Это одно из важнейших правил оптимизации. Вместо того чтобы писать:
ВЫБРАТЬ
Остатки.Номенклатура,
Остатки.КоличествоОстаток
ИЗ
РегистрНакопления.ТоварыНаСкладах.Остатки() КАК Остатки
ГДЕ
Остатки.Номенклатура В (&СписокНоменклатуры)
Гораздо эффективнее будет:
ВЫБРАТЬ
Остатки.Номенклатура,
Остатки.КоличествоОстаток
ИЗ
РегистрНакопления.ТоварыНаСкладах.Остатки(
&ДатаОстатков,
Номенклатура В (&СписокНоменклатуры)
) КАК Остатки
Когда условия передаются непосредственно в параметры виртуальной таблицы, платформа может сгенерировать SQL-запрос, который сразу же отфильтрует данные на уровне СУБД, избегая сканирования всей таблицы и последующей фильтрации.
Осторожность при работе с СрезПоследних
На форуме справедливо отмечается, что "соединяться напрямую со срезом последних вообще не оптимально". Это связано с тем, что СрезПоследних, особенно при сложных условиях или при использовании с PostgreSQL, может преобразовываться в СУБД в неоптимальные подзапросы. Если вы замечаете, что запрос со СрезПоследних работает медленно, то рассмотрите следующий пункт – использование временных таблиц.
СрезПоследних без даты
В некоторых случаях, когда нам нужны текущие остатки или цены, и мы не указываем дату в параметрах виртуальной таблицы СрезПоследних, платформа может сгенерировать более эффективный запрос, напрямую обращаясь к физической таблице без необходимости сложной группировки по периоду. Однако, это справедливо, если во внешнем запросе используются все измерения. В любом случае, всегда тестируйте производительность разных подходов.
Временные таблицы являются мощным инструментом оптимизации, особенно когда мы имеем дело со сложными запросами или медленными виртуальными таблицами. Разберем их применение.
Когда использовать временные таблицы?
Рекомендуем применять временные таблицы в следующих случаях:
СрезПоследних или Остатки работает медленно, особенно на PostgreSQL. Мы можем вынести обращение к виртуальной таблице в отдельный запрос и сохранить его результаты во временной таблице.Индексирование временных таблиц
Критически важно: временные таблицы следует всегда индексировать, если это дает прирост производительности. Индексы помогают СУБД значительно ускорить поиск и соединение данных. Если временная таблица используется в нескольких условиях выбора или соединениях, стройте индекс для наиболее часто проверяемого условия. Однако, если временная таблица используется всего 1-2 раза, накладные расходы на создание индекса могут не окупиться, и явное индексирование может даже замедлить запрос.
Пример использования временной таблицы для цен:
// Шаг 1: Получаем срез последних цен во временную таблицу
ВЫБРАТЬ
Цены.Номенклатура,
Цены.Цена
ПОМЕСТИТЬ ВТ_Цены
ИЗ
РегистрСведений.ЦеныНоменклатуры.СрезПоследних(&ДатаСреза) КАК Цены
;
// Шаг 2: Индексируем временную таблицу для быстрого соединения
ИНДЕКСИРОВАТЬ ПО ВТ_Цены.Номенклатура
;
// Шаг 3: Соединяем справочник с временной таблицей
ВЫБРАТЬ
Номенклатура1.Наименование КАК Наименование,
Номенклатура1.Артикул КАК Артикул,
ВТ_Цены.Цена КАК Цена
ИЗ
Справочник.Номенклатура КАК Номенклатура1
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Цены КАК ВТ_Цены
ПО Номенклатура1.Ссылка = ВТ_Цены.Номенклатура
ГДЕ
Номенклатура1.ЭтоГруппа = ЛОЖЬ
Работа с менеджером временных таблиц
Для всех временных таблиц, участвующих в одном пакете запросов, должен использоваться единый менеджер временных таблиц. Это обеспечивает корректное взаимодействие между запросами и гарантирует, что временные таблицы будут доступны на протяжении всего пакета.
Еще один вариант, который может ускорить выполнение запросов, это использование конструкции ОБЪЕДИНИТЬ ВСЁ. На форуме упоминается, что "возможно, будет работать быстрее через Конструкцию 'Объединить ВСЁ'".
Когда это полезно?
Мы рекомендуем использовать ОБЪЕДИНИТЬ ВСЁ вместо логической связки ИЛИ в условиях отбора, особенно когда ИЛИ применяется к разным полям, которые не охватываются одним индексом. Использование ИЛИ часто приводит к тому, что СУБД не может эффективно использовать индексы, что вынуждает ее сканировать большие объемы данных.
Пример: вместо ГДЕ (Поле1 = Значение1 ИЛИ Поле2 = Значение2), лучше разбить запрос на два и объединить их:
ВЫБРАТЬ
МойОбъект.Поле1,
МойОбъект.Поле2
ИЗ
Справочник.МойОбъект КАК МойОбъект
ГДЕ
МойОбъект.Поле1 = &Значение1
ОБЪЕДИНИТЬ ВСЁ
ВЫБРАТЬ
МойОбъект.Поле1,
МойОбъект.Поле2
ИЗ
Справочник.МойОбъект КАК МойОбъект
ГДЕ
МойОбъект.Поле2 = &Значение2
Такой подход позволяет СУБД использовать индексы для каждого подзапроса отдельно, а затем объединить отфильтрованные результаты, что значительно быстрее.
Индексы являются фундаментальным условием для построения эффективного плана запроса. Они ускоряют поиск информации в таблицах, сокращая количество итераций при соединении и выборке данных.
Как работают индексы?
Индекс — это по сути отсортированный список значений одного или нескольких полей таблицы, со ссылками на строки, содержащие эти значения. Когда СУБД получает запрос, она сначала проверяет наличие подходящих индексов. Если индекс существует и соответствует условиям запроса (особенно условиям ГДЕ и ПО в соединениях), СУБД может быстро найти нужные строки, не перебирая всю таблицу целиком.
Для быстрого поиска данных по индексу необходимо, чтобы условия запроса соответствовали полям индекса, начиная с первого, и шли подряд. Например, если у нас есть составной индекс по полям (Поле1, Поле2, Поле3), то запросы по Поле1, по Поле1 и Поле2 будут эффективно использовать индекс, а запрос только по Поле2 — нет.
Виды индексов в 1С
Платформа 1С создает индексы автоматически для:
Код, Наименование, Ссылка для справочников.Мы также можем создавать явные индексы, устанавливая флажок "Индексировать" для реквизитов или измерений в конфигураторе, или добавляя поля в "Критерии отбора". Это позволяет нам дополнительно оптимизировать запросы, которые часто используют определенные поля в условиях отбора или соединениях.
Помимо основных техник, существуют и другие нюансы, которые мы должны учитывать для максимально эффективной работы запросов.
Избегание "двухточечных" обращений (разыменование полей составного типа)
Обращение к полям через точку, например, Номенклатура.ВидНоменклатуры.Наименование, может привести к созданию дополнительных соединений и подзапросов, что замедляет выполнение. По возможности, старайтесь соединяться с объектами напрямую и получать нужные поля из них, или минимизируйте такие обращения в секциях ГДЕ и ПО.
Остатки() vs прямой запрос к виртуальной таблице Остатки
С точки зрения производительности, нет существенной разницы между использованием метода Остатки() (например, в модуле объекта или менеджера) и прямым запросом к виртуальной таблице Остатки регистра накопления. Платформа использует один и тот же механизм для генерации SQL-запроса. Однако, запросы предоставляют нам значительно больше гибкости для фильтрации, группировки и установки блокировок.
Разделение итогов для регистров накопления
Этот механизм может помочь снять ограничения при одновременной записи в регистр, добавляя специальное техническое поле-разделитель в таблицу итогов. Однако, при контроле остатков 1С все равно блокирует данные регистра без учета разделителя, что нужно иметь в виду.
Агрегаты регистров накопления
Агрегаты могут значительно сократить время выполнения запросов, особенно для часто используемых отчетов, требующих суммирования данных регистра. Они представляют собой предварительно рассчитанные итоги по определенным измерениям. В конфигураторе мы можем задать, по каким измерениям и с какой периодичностью строить агрегаты, или довериться платформе, вызвав метод ОпределитьОптимальныеАгрегаты(). Использование агрегатов требует их актуализации, что может потреблять ресурсы.
В заключение, оптимальный вывод номенклатуры с остатками или ценами в 1С — это не единичное решение, а комплексный подход, требующий применения различных техник оптимизации. Мы проанализировали важность правильного использования левых соединений, особенностей работы с виртуальными таблицами (особенно СрезПоследних), применения временных таблиц для разбиения сложных запросов и индексирования, а также оператора ОБЪЕДИНИТЬ ВСЁ. Помните, что всегда стоит тестировать различные подходы на своих реальных данных, чтобы определить наиболее эффективный вариант для конкретной задачи и конфигурации.