При разработке интеграционных механизмов (например, выгрузки данных в мобильную систему торговых представителей SalesWorks из 1С:Управление торговлей 10.3) разработчики часто сталкиваются с загадочным поведением запросов. Типичный сценарий: при формировании отчета или выгрузки за короткий период (1–3 месяца) все контрагенты попадают в выборку корректно. Однако стоит увеличить период до года, как часть клиентов, у которых точно были продажи, бесследно исчезает из результирующей таблицы.
В этой статье мы подробно разберем, почему возникает этот эффект, проанализируем внутренние механизмы работы платформы 1С:Предприятие с виртуальными таблицами и рассмотрим правильные архитектурные решения для исправления этой проблемы.
---
Основная причина кроется в особенностях оптимизации работы виртуальных таблиц оборотов (в нашем случае — таблицы РегистрНакопления.Продажи.Обороты) на уровне платформы 1С и СУБД. Проанализируем два ключевых фактора:
Обороты платформа 1С формирует SQL-запрос, который группирует записи физической таблицы по указанным измерениям (в исходном запросе это Контрагент и Номенклатура) и рассчитывает агрегированные суммы. Если за выбранный период сумма абсолютно всех числовых ресурсов по конкретной группировке (например, оборот по количеству и стоимости) оказывается равной точному нулю, платформа автоматически отсекает эту строку. Она просто не возвращается из СУБД в результат запроса.
15 000 - 15 000 = 0. Строка полностью исключается из виртуальной таблицы оборотов, и клиент «исчезает» из итогового списка.---
Один из участников обсуждения предложил установить в параметрах виртуальной таблицы периодичность Месяц. Выясним, почему этот способ временно решает проблему и какие скрытые угрозы он в себе несет.
При указании периодичности Месяц платформа 1С рассчитывает итоги отдельно для каждого календарного месяца внутри выбранного интервала. В результате СУБД возвращает две отдельные строки:
Поскольку ни один из этих месяцев по отдельности не дает нулевого итога, обе строки попадают в выборку. Последующее использование конструкции ВЫБРАТЬ РАЗЛИЧНЫЕ успешно сворачивает эти строки и оставляет контрагента в финальном списке.
Однако этот подход имеет критические недостатки:
Месяц оборот за январь сойдется в ноль. Контрагент все равно будет потерян.
---
Для гарантированного получения всех контрагентов, совершивших движения за период, рассмотрим два профессиональных метода решения задачи.
Если нам не важны итоговые числовые показатели (суммы и количества), а требуется лишь сам факт наличия активности контрагента по определенной номенклатуре, лучшим решением будет полный отказ от виртуальной таблицы Обороты. Вместо нее мы обратимся напрямую к основной физической таблице регистра накопления РегистрНакопления.Продажи.
В физической таблице продажи и возвраты хранятся в виде независимых записей (строк). Они никогда не группируются и не взаимоуничтожаются на уровне СУБД. Запрос к этой таблице с последующей группировкой гарантированно вернет всех клиентов, у которых были любые движения за указанный период.
Помимо логической ошибки со схлопыванием, исходный запрос из обсуждения содержит серьезные архитектурные изъяны, которые приводят к деградации производительности базы данных при росте ее объема. Проанализируем эти ошибки:
Номенклатура В ИЕРАРХИИ (&Номенклатура) прямо в параметрах виртуальной таблицы. СУБД строит неоптимальные планы запросов, пытаясь на лету рассчитать иерархическое дерево для миллионов записей.КонтактнаяИнформация напрямую. Соединение выполняется для каждой строки продаж до того, как сработает инструкция ВЫБРАТЬ РАЗЛИЧНЫЕ, что заставляет СУБД выполнять колоссальный объем лишней работы по поиску адресов.Рассмотрим правильный алгоритм построения запроса с использованием механизмов временных таблиц по шагам.
Шаг 1. Получение списка номенклатуры по иерархии во временную таблицу. Мы предварительно выбираем всю необходимую номенклатуру, входящую в иерархические группы, помещаем ее в отдельную временную таблицу и индексируем ее.
Шаг 2. Получение уникальных контрагентов из физической таблицы регистра. Выбираем только уникальные ссылки контрагентов, у которых были записи в регистре за период по номенклатуре из первой временной таблицы, и помещаем их во вторую временную таблицу.
Шаг 3. Финальное соединение с контактной информацией. К уже очищенному, уникальному и легкому списку контрагентов мы присоединяем справочник контрагентов и адреса. СУБД выполнит эту операцию мгновенно.
---
Реализуем описанный алгоритм на языке запросов 1С. Этот код решает проблему пропадающих контрагентов и работает в десятки раз быстрее первоначального варианта:
// ПАКЕТ 1: Отбираем номенклатуру по иерархии во временную таблицу
ВЫБРАТЬ
Номенклатура.Ссылка КАК Ссылка
ПОМЕСТИТЬ ВТ_НоменклатураФильтр
ИЗ
Справочник.Номенклатура КАК Номенклатура
ГДЕ
Номенклатура.Ссылка В ИЕРАРХИИ(&Номенклатура)
ИНДЕКСИРОВАТЬ ПО
Ссылка
;
// ПАКЕТ 2: Извлекаем только уникальных контрагентов из ФИЗИЧЕСКОЙ таблицы регистра
ВЫБРАТЬ РАЗЛИЧНЫЕ
РегПродажи.Контрагент КАК Контрагент
ПОМЕСТИТЬ ВТ_АктивныеКонтрагенты
ИЗ
РегистрНакопления.Продажи КАК РегПродажи
ГДЕ
РегПродажи.Период МЕЖДУ &Дата1 И &Дата2
И РегПродажи.Номенклатура В (ВЫБРАТЬ ВТ_НоменклатураФильтр.Ссылка ИЗ ВТ_НоменклатураФильтр)
И РегПродажи.Контрагент <> ЗНАЧЕНИЕ(Справочник.Контрагенты.ПустаяСсылка)
ИНДЕКСИРОВАТЬ ПО
Контрагент
;
// ПАКЕТ 3: Соединяем подготовленный список контрагентов со справочниками и адресами
ВЫБРАТЬ РАЗЛИЧНЫЕ
0 КАК OL_ID,
381 КАК SUBTYPE_ID,
0 КАК AREA_ID,
0 КАК OWNER_ID,
" " КАК MERCH_CODE,
Контрагенты.Код КАК PCOMP_CODE,
Контрагенты.Код КАК OL_CODE,
ВЫРАЗИТЬ(Контрагенты.НаименованиеПолное КАК СТРОКА(200)) КАК NAME,
Контрагенты.Наименование КАК TRADE_NAME,
"-" КАК DIRECTOR,
ВЫРАЗИТЬ(ЮрАдр.Представление КАК СТРОКА(200)) КАК ADDRESS,
ВЫРАЗИТЬ(ФактАдр.Представление КАК СТРОКА(200)) КАК DELIV_ADDR,
"-" КАК TELEPHONE,
"-" КАК FAX,
"-" КАК EMAIL,
"-" КАК ACCOUNTANT,
"-" КАК ACC_PHONE,
"-" КАК M_MANAGER,
"-" КАК MM_PHONE,
"-" КАК P_MANAGER,
"00:00" КАК OPEN_TIME,
"00:00" КАК CLOSE_TIME,
"00:00" КАК BREAK_FROM,
"00:00" КАК BREAK_TO,
Контрагенты.ОКОПФ КАК ZKPO,
ВЫБОР
КОГДА Контрагенты.ИНН ЕСТЬ NULL ИЛИ Контрагенты.ИНН = "" ТОГДА "Нет"
ИНАЧЕ Контрагенты.ИНН
КОНЕЦ КАК IPN,
"-" КАК VATN,
"-" КАК RR,
"-" КАК BANKCODE,
"-" КАК BANKNAME,
"-" КАК BANKADDR,
0 КАК LIC_USAGE,
ВЫБОР
КОГДА Контрагенты.ПометкаУдаления ТОГДА 9
ИНАЧЕ 2
КОНЕЦ КАК STATUS
ИЗ
ВТ_АктивныеКонтрагенты КАК АктивныеКонтрагенты
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Справочник.Контрагенты КАК Контрагенты
ПО АктивныеКонтрагенты.Contragent = Контрагенты.Ссылка
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.КонтактнаяИнформация КАК ЮрАдр
ПО АктивныеКонтрагенты.Contragent = ЮрАдр.Объект
И ЮрАдр.Вид = ЗНАЧЕНИЕ(Справочник.ВидыКонтактнойИнформации.ЮрАдресКонтрагента)
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.КонтактнаяИнформация КАК ФактАдр
ПО АктивныеКонтрагенты.Contragent = ФактАдр.Объект
И ФактАдр.Вид = ЗНАЧЕНИЕ(Справочник.ВидыКонтактнойИнформации.ФактАдресКонтрагента)
---
Подведем итоги нашего исследования. Использование виртуальной таблицы Обороты без детализации до регистратора при анализе больших периодов неизбежно ведет к потере клиентов из-за математического сложения продаж и возвратов в ноль.
Для построения качественных, быстродействующих и отказоустойчивых систем интеграции всегда отдавайте предпочтение обращению к физическим таблицам регистра, когда вам важен сам факт проведения хозяйственной операции, и не забывайте оптимизировать запросы с помощью механизмов временных таблиц и индексации полей связи.