В практике разработки на платформе 1С:Предприятие часто возникают ситуации, когда простой на первый взгляд запрос начинает «тормозить» на реальных объемах данных или выдавать некорректные результаты. Особенно это актуально для конфигураций на обычных формах, таких как 1С:Управление торговлей 10.3, где активно используются механизмы свойств и категорий. Разберем типичную задачу: получение данных о продажах номенклатуры в разрезе определенного дополнительного свойства. Проанализируем ошибки в исходном запросе и выработаем максимально эффективное решение.
Рассмотрим исходный текст запроса, который вызвал затруднения у автора:
ВЫБРАТЬ
ЗначенияСвойствОбъектов.Значение КАК ВидТовара,
ПродажиОбороты.Номенклатура КАК Номенклатура,
СУММА(ПродажиОбороты.СтоимостьОборот) КАК Сумма
ИЗ
РегистрНакопления.Продажи.Обороты(&ДатаНачала, &ДатаОкончания, , ) КАК ПродажиОбороты
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЗначенияСвойствОбъектов КАК ЗначенияСвойствОбъектов
ПО ПродажиОбороты.Номенклатура = ЗначенияСвойствОбъектов.Объект
ГДЕ
ЗначенияСвойствОбъектов.Свойство.Код = "000000978"
СГРУППИРОВАТЬ ПО
ПродажиОбороты.Номенклатура,
ЗначенияСвойствОбъектов.Значение
При внимательном изучении мы можем выделить несколько критических моментов, которые замедляют выполнение и считаются «плохим тоном» в разработке 1С:
ЛЕВОЕ СОЕДИНЕНИЕ) виртуальной таблицы Обороты с физической таблицей регистра сведений — это серьезная ошибка. SQL-оптимизатор часто не может построить эффективный план выполнения для такого «бутерброда», что ведет к сканированию лишних индексов или даже всей таблицы.Объект в регистре ЗначенияСвойствОбъектов имеет составной тип (Справочник.Номенклатура, Справочник.Контрагенты и др.). Обращение ЗначенияСвойствОбъектов.Свойство.Код заставляет систему генерировать дополнительные JOIN на уровне СУБД ко всем таблицам, входящим в тип, что кратно замедляет работу."000000978" делает запрос негибким и медленным. Сравнение строк в SQL всегда медленнее, чем сравнение ссылок (уникальных идентификаторов).ГДЕ по правой таблице в левом соединении автоматически превращает его во ВНУТРЕННЕЕ СОЕДИНЕНИЕ. Если у товара не заполнено свойство, он просто не попадет в отчет, хотя ЛЕВОЕ СОЕДИНЕНИЕ подразумевает обратное.Разберем наиболее правильный и масштабируемый подход. Основная идея заключается в том, чтобы сначала подготовить данные о продажах, затем подготовить данные о свойствах, и только после этого соединить две небольшие «материализованные» временные таблицы. Выясним, как это реализовать по шагам.
Шаг 1. Получение данных о продажах. Поместим обороты во временную таблицу. Это позволит СУБД зафиксировать набор данных и использовать его статистику для дальнейшего соединения.
Шаг 2. Получение свойств. Выберем только нужные нам свойства номенклатуры, сразу отфильтровав их по ссылке на свойство, переданной в параметре.
Шаг 3. Итоговое соединение. Соединим подготовленные данные.
// Сначала подготовим таблицу продаж
ВЫБРАТЬ
ПродажиОбороты.Номенклатура КАК Номенклатура,
ПродажиОбороты.СтоимостьОборот КАК Сумма
ПОМЕСТИТЬ ВТ_Продажи
ИЗ
РегистрНакопления.Продажи.Обороты(&ДатаНачала, &ДатаОкончания, , ) КАК ПродажиОбороты
;
////////////////////////////////////////////////////////////////////////////////
// Теперь подготовим свойства, используя ВЫРАЗИТЬ для типизации
ВЫБРАТЬ
ЗначенияСвойствОбъектов.Объект КАК Номенклатура,
ЗначенияСвойствОбъектов.Значение КАК ВидТовара
ПОМЕСТИТЬ ВТ_Свойства
ИЗ
РегистрСведений.ЗначенияСвойствОбъектов КАК ЗначенияСвойствОбъектов
ГДЕ
ЗначенияСвойствОбъектов.Свойство = &СвойствоСсылка
И ЗначенияСвойствОбъектов.Объект ССЫЛКА Справочник.Номенклатура
;
////////////////////////////////////////////////////////////////////////////////
// Финальная выборка с индексацией
ВЫБРАТЬ
ВТ_Свойства.ВидТовара КАК ВидТовара,
ВТ_Продажи.Номенклатура КАК Номенклатура,
СУММА(ВТ_Продажи.Сумма) КАК Сумма
ИЗ
ВТ_Продажи КАК ВТ_Продажи
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_Свойства КАК ВТ_Свойства
ПО ВТ_Продажи.Номенклатура = ВТ_Свойства.Номенклатура
СГРУППИРОВАТЬ ПО
ВТ_Свойства.ВидТовара,
ВТ_Продажи.Номенклатура
Проанализируем ситуацию, когда база данных содержит миллионы записей. В некоторых случаях работа напрямую с физической таблицей регистра накопления (вместо виртуальной таблицы Обороты) может быть быстрее, если мы правильно накладываем фильтры по индексам. Посмотрим на пример такого подхода, где мы сначала выбираем нужную номенклатуру по свойству, индексируем её, а затем «приклеиваем» к ней продажи.
Рассмотрим этот метод подробнее:
ВЫБРАТЬ
ЗначенияСвойствОбъектов.Объект КАК Номенклатура,
ЗначенияСвойствОбъектов.Значение КАК ВидТовара
ПОМЕСТИТЬ ВТ_НоменклатураСоСвойством
ИЗ
РегистрСведений.ЗначенияСвойствОбъектов КАК ЗначенияСвойствОбъектов
ГДЕ
ЗначенияСвойствОбъектов.Свойство = &ПараметрСвойство
И ЗначенияСвойствОбъектов.Объект ССЫЛКА Справочник.Номенклатура
ИНДЕКСИРОВАТЬ ПО
Номенклатура
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТ_НоменклатураСоСвойством.ВидТовара КАК ВидТовара,
ПродажиОбороты.Номенклатура КАК Номенклатура,
ПродажиОбороты.СтоимостьОборот КАК Сумма
ИЗ
РегистрНакопления.Продажи.Обороты(&ДатаНачала, &ДатаОкончания, ,
Номенклатура В (ВЫБРАТЬ Номенклатура ИЗ ВТ_НоменклатураСоСвойством)) КАК ПродажиОбороты
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_НоменклатураСоСвойством КАК ВТ_НоменклатураСоСвойством
ПО ПродажиОбороты.Номенклатура = ВТ_НоменклатураСоСвойством.Номенклатура
ИТОГИ
СУММА(Сумма)
ПО
ВидТовара
В данном примере мы используем важный прием — фильтрацию внутри параметров виртуальной таблицы с помощью вложенного запроса к проиндексированной временной таблице. Это позволяет 1С передать на уровень SQL конкретный список идентификаторов номенклатуры, что значительно сужает область поиска в таблицах итогов.
Давайте разберем технические причины, по которым предложенные изменения ускоряют систему:
ПОМЕСТИТЬ, система создает реальную таблицу в базе данных tempdb. У этой таблицы есть статистика, по которой SQL-сервер понимает: «Ага, тут всего 500 строк, я буду использовать метод соединения Nested Loops». Без этого он может ошибочно выбрать Hash Match, что на малых объемах только замедлит процесс.ИНДЕКСИРОВАТЬ ПО создает индекс на временной таблице. При соединении двух таблиц по проиндексированным полям сложность операции падает с квадратичной до логарифмической.AnyRef на уровне SQL. Без ВЫРАЗИТЬ или проверки ССЫЛКА СУБД приходится проверять соответствие типа для каждой записи. Явное указание типа отсекает лишние логические проверки.&СвойствоСсылка вместо Код = "..." позволяет SQL-серверу использовать кэширование планов запросов. Если вы измените код в тексте запроса, SQL будет вынужден перекомпилировать план, что является ресурсоемкой операцией.Подводя итог, можно сказать, что «секрет» быстрого запроса в 1С 8.3 (и тем более в старой доброй УТ 10.3) заключается в декомпозиции задачи. Не пытайтесь получить все данные одним сложным запросом с множеством соединений. Построение цепочки из временных таблиц с предварительной фильтрацией и обязательной индексацией полей связи — это золотой стандарт разработки.
Основные правила, которые мы усвоили:
ВЫРАЗИТЬ или ССЫЛКА.ЕстьNULL, если данные могут отсутствовать.Соблюдение этих простых принципов позволит вашим отчетам работать мгновенно даже на базах с историей в несколько десятилетий.