Разработка эффективных отчетов и выборок в системе 1С частенько ставит перед программистом задачу отсеивания лишних данных. Типичный пример — необходимость получить список контрагентов, с которыми еще не велась работа (нет записей в регистре сведений), и одновременно вывести запланированные контакты. На первый взгляд задача кажется простой, однако неоптимальный или синтаксически неверный запрос на реальной базе данных с миллионами записей может привести к серьезному падению производительности или некорректным результатам.
В этой статье мы подробно разберем, как правильно реализовать подобную выборку, проанализируем распространенные ошибки начинающих разработчиков и рассмотрим профессиональные методы оптимизации запросов 1С.
Рассмотрим классическую ситуацию. У нас есть справочник Контрагенты, подчиненный ему справочник КонтактныеЛица и регистр сведений ДанныеОЗвонках. Нам нужно получить 20 «новых» контрагентов (которым еще не звонили) и добавить к ним список запланированных звонков.
Часто разработчики совершают грубую ошибку в синтаксисе связи таблиц, пытаясь наложить фильтрацию непосредственно в секции связи ПО. Рассмотрим некорректный пример:
ПО Контрагенты.Ссылка <> ДанныеОЗвонках.Контрагент.Ссылка ЕСТЬ NULL
Или еще один неработающий вариант из практики:
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ДанныеОЗвонках КАК ДанныеОЗвонках
ПО (ДанныеОЗвонках.Контрагент ЕСТЬ NULL)
Почему это не работает? Секция ПО (ON в терминах SQL) предназначена исключительно для определения правил сопоставления строк левой и правой таблиц по ключевым полям. Пытаясь написать туда условие ЕСТЬ NULL, мы нарушаем логику соединения. СУБД просто не сможет сопоставить строки таблиц, что приведет либо к получению пустого результата из правой таблицы, либо к неконтролируемому декартову произведению строк.
Правило сопоставления всегда должно идти по ключевым полям связи, а вот фильтрация отсутствующих записей (проверка на то, что связь не состоялась) должна производиться строго в секции ГДЕ (WHERE).
Правильный базовый подход для отсеивания уже обработанных контрагентов заключается в использовании левого соединения справочника с регистром сведений по ключевому полю (в нашем случае это контрагент) и последующей фильтрацией в секции ГДЕ по значению ЕСТЬ NULL для любого обязательного поля правой таблицы (обычно берут само поле связи или ключевое измерение).
Рассмотрим рабочий пример такого запроса:
ВЫБРАТЬ ПЕРВЫЕ 20
Контрагенты.Ссылка КАК Контрагент,
КонтактныеЛица.Ссылка КАК КонтактноеЛицо
ИЗ
Справочник.Контрагенты КАК Контрагенты
ЛЕВОЕ СОЕДИНЕНИЕ Справочник.КонтактныеЛица КАК КонтактныеЛица
ПО КонтактныеЛица.Владелец = Контрагенты.Ссылка
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ДанныеОЗвонках КАК ДанныеОЗвонках
ПО Контрагенты.Ссылка = ДанныеОЗвонках.Контрагент
ГДЕ
ДанныеОЗвонках.Контрагент ЕСТЬ NULL
При таком подходе СУБД берет всех контрагентов, пытается найти для них записи в регистре ДанныеОЗвонках. Если записей нет, то поля регистра в результирующей выборке заполняются значением NULL. Условие ГДЕ ДанныеОЗвонках.Контрагент ЕСТЬ NULL оставляет в итоговом результате только тех контрагентов, по которым не было найдено ни одной записи в регистре.
Очень часто для получения запланированных звонков разработчики объединяют выборку необзвоненных клиентов с таблицей среза последних записей регистра. При этом допускается серьезная ошибка оптимизации:
ИЗ
РегистрСведений.ДанныеОЗвонках.СрезПоследних КАК ДанныеОЗвонкахСрезПоследних
ГДЕ
ДанныеОЗвонкахСрезПоследних.ДатаПереноса >= &ТекущаяДата
В чем опасность такого кода? При использовании секции ГДЕ для виртуальных таблиц (таких как СрезПоследних, СрезПервых, Обороты и т.д.) СУБД сначала рассчитывает срез последних по абсолютно всем записям регистра в базе данных, строит временные индексы, выполняет тяжелые соединения на уровне СУБД, и только после получения огромного результирующего набора данных начинает фильтровать его по дате переноса. На базе данных с миллионами строк такой запрос вызовет колоссальную нагрузку, заблокирует таблицы и приведет к зависанию сеанса.
Как правильно? Любые условия фильтрации для виртуальных таблиц необходимо передавать строго в качестве их параметров. В этом случае СУБД наложит фильтр по дате и другим измерениям еще на этапе чтения физических таблиц и индексов БД, выбрав минимально необходимый объем данных:
ИЗ
РегистрСведений.ДанныеОЗвонках.СрезПоследних(, ДатаПереноса >= &ТекущаяДата) КАК ДанныеОЗвонкахСрезПоследних
Хотя конструкция ЛЕВОЕ СОЕДИНЕНИЕ ... ГДЕ Регистр.Контрагент ЕСТЬ NULL является стандартным решением, на таблицах объемом в несколько миллионов записей она может работать неоптимально. СУБД будет вынуждена выполнять полное сканирование (Scan) таблиц для сопоставления.
Для обеспечения стабильной и быстрой работы на высоконагруженных системах правильным стандартом разработки в 1С является разделение сложного запроса на пакеты с использованием временных таблиц:
ЕСТЬ NULL.Рассмотрим, как это выглядит на практике:
// ПАКЕТ 1: Выбираем только уникальные идентификаторы обзвоненных контрагентов
ВЫБРАТЬ РАЗЛИЧНЫЕ
ДанныеОЗвонках.Контрагент КАК Контрагент
ПОМЕСТИТЬ ВТ_Обзвоненные
ИЗ
РегистрСведений.ДанныеОЗвонках КАК ДанныеОЗвонках
;
////////////////////////////////////////////////////////////////////////////////
// ИНДЕКСИРУЕМ ВТ для моментального сопоставления в СУБД
ИНДЕКСИРОВАТЬ ПО
ВТ_Обзвоненные.Контрагент
;
////////////////////////////////////////////////////////////////////////////////
// ПАКЕТ 2: Основная выборка необзвоненных контрагентов
ВЫБРАТЬ ПЕРВЫЕ 20
Контрагенты.Ссылка КАК Контрагент,
КонтактныеЛица.Ссылка КАК КонтактноеЛицо
ИЗ
Справочник.Contragents КАК Контрагенты
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Обзвоненные КАК ВТ_Обзвоненные
ПО Контрагенты.Ссылка = ВТ_Обзвоненные.Контрагент
ЛЕВОЕ СОЕДИНЕНИЕ Справочник.КонтактныеЛица КАК КонтактныеЛица
ПО КонтактныеЛица.Владелец = Контрагенты.Ссылка
ГДЕ
ВТ_Обзвоненные.Контрагент ЕСТЬ NULL
Важное архитектурное замечание: убедитесь, что в метаданных вашего регистра сведений измерение Контрагент имеет свойство Индексировать (или является самым первым измерением в структуре регистра). Без этого СУБД все равно будет сканировать весь регистр целиком, сводя на нет преимущества временных таблиц.
При написании подобных запросов разработчики часто наступают на две скрытые логические ловушки.
Ловушка 1: Использование ПЕРВЫЕ без УПОРЯДОЧИТЬ ПО. Если вы используете конструкцию ВЫБРАТЬ ПЕРВЫЕ 20, но не указываете сортировку, СУБД вернет случайный набор записей. При каждом новом открытии формы или после реиндексации таблиц базы данных этот список может непредсказуемо меняться. Чтобы список выдачи для менеджера всегда оставался стабильным и предсказуемым, обязательно добавляйте сортировку:
УПОРЯДОЧИТЬ ПО
Контрагенты.Наименование
Ловушка 2: Размножение строк из-за левого соединения подчиненных таблиц. Обратите внимание на связь со справочником КонтактныеЛица:
ЛЕВОЕ СОЕДИНЕНИЕ Справочник.КонтактныеЛица КАК КонтактныеЛица
ПО КонтактныеЛица.Владелец = Контрагенты.Ссылка
У одного контрагента может быть зарегистрировано 3, 5 или более контактных лиц. При левом соединении строка контрагента размножится ровно столько раз, сколько у него контактных лиц. В итоге ограничение ПЕРВЫЕ 20 сработает на уровне строк результирующей выборки, и менеджер увидит в списке, например, всего 4-5 уникальных контрагентов, у каждого из которых вывелось по несколько контактных лиц.
Чтобы избежать этой проблемы, необходимо соединяться либо с основным контактным лицом (если такой реквизит предусмотрен в справочнике Контрагенты), либо предварительно группировать таблицу контактных лиц, чтобы выбирать для каждого контрагента ровно одного представителя (например, первого по алфавиту или по коду).
Объединим все озвученные рекомендации в один красивый, быстрый и отказоустойчивый запрос. Мы решим задачу вывода 20 необзвоненных контрагентов и запланированных звонков на будущие даты, применив пакетные запросы, правильные параметры виртуальных таблиц и сортировку.
ВЫБРАТЬ РАЗЛИЧНЫЕ
ДанныеОЗвонках.Контрагент КАК Контрагент
ПОМЕСТИТЬ ВТ_Обзвоненные
ИЗ
РегистрСведений.ДанныеОЗвонках КАК ДанныеОЗвонках
;
////////////////////////////////////////////////////////////////////////////////
ИНДЕКСИРОВАТЬ ПО
ВТ_Обзвоненные.Contragent
;
////////////////////////////////////////////////////////////////////////////////
// Выбираем строго 20 уникальных контрагентов, которым еще не звонили
ВЫБРАТЬ ПЕРВЫЕ 20
Контрагенты.Ссылка КАК Контрагент,
Контрагенты.ОсновноеКонтактноеЛицо КАК КонтактноеЛицо,
НЕОПРЕДЕЛЕНО КАК ТипЗвонка,
"" КАК РезультатОбзвона,
ДАТАВРЕМЯ(1, 1, 1) КАК ДатаПереноса
ПОМЕСТИТЬ ВТ_ИтоговыйРезультат
ИЗ
Справочник.Контрагенты КАК Контрагенты
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Обзвоненные КАК ВТ_Обзвоненные
ПО Контрагенты.Ссылка = ВТ_Обзвоненные.Контрагент
ГДЕ
ВТ_Обзвоненные.Контрагент ЕСТЬ NULL
УПОРЯДОЧИТЬ ПО
Контрагенты.Наименование
ОБЪЕДИНИТЬ ВСЕ
// Добавляем к ним запланированные звонки из виртуальной таблицы с параметрами
ВЫБРАТЬ
ДанныеОЗвонкахСрезПоследних.Контрагент,
ДанныеОЗвонкахСрезПоследних.Контрагент.ОсновноеКонтактноеЛицо,
ДанныеОЗвонкахСрезПоследних.ТипЗвонка,
ДанныеОЗвонкахСрезПоследних.РезультатОбзвона,
ДанныеОЗвонкахСрезПоследних.ДатаПереноса
ИЗ
РегистрСведений.ДанныеОЗвонках.СрезПоследних(, ДатаПереноса >= &ТекущаяДата) КАК ДанныеОЗвонкахСрезПоследних
;
////////////////////////////////////////////////////////////////////////////////
// Финальный выгружаемый пакет с единой сортировкой
ВЫБРАТЬ
ВТ_ИтоговыйРезультат.Контрагент КАК Контрагент,
ВТ_ИтоговыйРезультат.КонтактноеЛицо КАК КонтактноеЛицо,
ВТ_ИтоговыйРезультат.ТипЗвонка КАК ТипЗвонка,
ВТ_ИтоговыйРезультат.РезультатОбзвона КАК РезультатОбзвона,
ВТ_ИтоговыйРезультат.ДатаПереноса КАК ДатаПереноса
ИЗ
ВТ_ИтоговыйРезультат КАК ВТ_ИтоговыйРезультат
УПОРЯДОЧИТЬ ПО
Контрагент.Наименование
Представленный шаблон гарантирует высокую скорость работы в СУБД MS SQL или PostgreSQL даже при объеме базы данных в несколько миллионов записей, исключает появление дублей контрагентов из-за подчиненных справочников и возвращает гарантированно упорядоченный, предсказуемый результат.