При работе с запросами в системе 1С:Предприятие начинающие разработчики часто сталкиваются с ситуацией, когда результат выборки из виртуальных таблиц СрезПоследних содержит огромное количество дублирующихся строк. Казалось бы, срез должен возвращать только одну актуальную запись, но при соединении нескольких таблиц количество строк может вырасти в геометрической прогрессии. В этой статье мы подробно разберем, почему так происходит, как работают механизмы виртуальных таблиц и как правильно оптимизировать такие запросы, чтобы получить корректный результат.
Рассмотрим ситуацию, когда необходимо получить данные из двух регистров сведений, например, ДвижениеКонтингента и КурсыУчебныхГрупп. Разработчик использует ВНУТРЕННЕЕ СОЕДИНЕНИЕ виртуальных таблиц СрезПоследних по полю «Группа».
Проанализируем пример кода, который часто приводит к избыточным данным:
Запрос = Новый Запрос;
Запрос.Текст = "ВЫБРАТЬ
| КурсыУчебныхГруппСрезПоследних.УчебнаяГруппа КАК УчебнаяГруппа,
| КурсыУчебныхГруппСрезПоследних.Курс КАК Курс
|ИЗ
| РегистрСведений.ДвижениеКонтингента.СрезПоследних КАК ДвижениеКонтингентаСрезПоследних
| ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.КурсыУчебныхГрупп.СрезПоследних КАК КурсыУчебныхГруппСрезПоследних
| ПО ДвижениеКонтингентаСрезПоследних.Группа = КурсыУчебныхГруппСрезПоследних.УчебнаяГруппа";
Проблема здесь заключается в фундаментальном понимании того, как СрезПоследних формирует результат. Срез выбирает последние записи в разрезе всех измерений регистра. Если в регистре ДвижениеКонтингента помимо измерения Группа есть другие измерения (например, Студент, ВидОбучения), то для одной и той же группы «СрезПоследних» вернет столько записей, сколько уникальных комбинаций других измерений существует на актуальную дату.
Даже если в секции ВЫБРАТЬ мы указываем только «Группу» и «Курс», система сначала выполняет соединение всех полей, обеспечивающих уникальность записи в срезе. В итоге мы получаем множество одинаковых пар «Группа-Курс», так как они соответствуют разным значениям других измерений, которые остались «за кадром» нашего запроса.
Самый простой и быстрый способ избавиться от дублей в итоговой таблице — использовать оператор РАЗЛИЧНЫЕ. Он заставляет систему сравнить все результирующие строки и оставить только уникальные комбинации выбранных полей.
Посмотрим на исправленный текст запроса:
Запрос.Текст = "ВЫБРАТЬ РАЗЛИЧНЫЕ
| КурсыУчебныхГруппСрезПоследних.УчебнаяГруппа КАК УчебнаяГруппа,
| КурсыУчебныхГруппСрезПоследних.Курс КАК Курс
|ИЗ ...";
Этот метод эффективен, когда вам нужно просто «схлопнуть» результат. Однако стоит помнить, что РАЗЛИЧНЫЕ применяется к уже сформированному набору данных. Если промежуточная выборка содержит миллионы строк, производительность может пострадать, так как СУБД придется выполнять сортировку или хеширование огромного объема данных в памяти.
Более строгим и гибким подходом является использование секции СГРУППИРОВАТЬ ПО. Она позволяет не только убрать дубли, но и подготовить запрос для использования агрегатных функций (например, если нам нужно будет посчитать количество студентов в группе или сумму).
Разберем пример с группировкой:
Запрос.Текст = "ВЫБРАТЬ
| КурсыУчебныхГруппСрезПоследних.УчебнаяГруппа КАК УчебнаяГруппа,
| КурсыУчебныхГруппСрезПоследних.Курс КАК Курс
|ИЗ
| РегистрСведений.ДвижениеКонтингента.СрезПоследних КАК ДвижениеКонтингентаСрезПоследних
| ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.КурсыУчебныхГрупп.СрезПоследних КАК КурсыУчебныхГруппСрезПоследних
| ПО ДвижениеКонтингентаСрезПоследних.Группа = КурсыУчебныхГруппСрезПоследних.УчебнаяГруппа
|СГРУППИРОВАТЬ ПО
| КурсыУчебныхГруппСрезПоследних.УчебнаяГруппа,
| КурсыУчебныхГруппСрезПоследних.Курс";
Для оптимизатора SQL-запросов конструкции РАЗЛИЧНЫЕ и СГРУППИРОВАТЬ ПО (без агрегатных функций) часто выглядят идентично, но для разработчика 1С использование группировки считается более профессиональным стилем, так как оно явно указывает на структуру итогового набора данных.
Выясним причину, по которой соединение виртуальных таблиц «напрямую» считается плохим тоном в разработке на платформе 8.3. Когда мы пишем СрезПоследних ВНУТРЕННЕЕ СОЕДИНЕНИЕ СрезПоследних, платформа генерирует сложный SQL-запрос с вложенными подзапросами. СУБД вынуждена одновременно вычислять два среза и соединять их, что часто приводит к неоптимальному плану выполнения и избыточному сканированию таблиц итогов.
Проанализируем правильный алгоритм работы через временные таблицы:
Пример оптимизированного кода:
Запрос = Новый Запрос;
Запрос.Текст = "ВЫБРАТЬ РАЗЛИЧНЫЕ
| ДвижениеКонтингентаСрезПоследних.Группа КАК Группа
|ПОМЕСТИТЬ ВТ_Группы
|ИЗ
| РегистрСведений.ДвижениеКонтингента.СрезПоследних КАК ДвижениеКонтингентаСрезПоследних;
|
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ
| ВТ_Группы.Группа КАК УчебнаяГруппа,
| КурсыУчебныхГруппСрезПоследних.Курс КАК Курс
|ИЗ
| ВТ_Группы КАК ВТ_Группы
| ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.КурсыУчебныхГрупп.СрезПоследних КАК КурсыУчебныхГруппСрезПоследних
| ПО ВТ_Группы.Группа = КурсыУчебныхГруппСрезПоследних.УчебнаяГруппа";
Запрос.Выполнить();
Важный момент: Если мы передадим список групп во вторую виртуальную таблицу в качестве параметра &Условие, запрос отработает еще быстрее, так как 1С применит фильтр на этапе получения среза, а не после него.
Посмотрим на типичную ошибку: отсутствие параметров в скобках после СрезПоследних. Если оставить их пустыми, 1С будет получать срез на текущий момент времени. Но если данные вносятся в базу «будущим числом» (что иногда случается из-за ошибок пользователей), такие записи также попадут в выборку.
Рекомендуется всегда явно указывать Период и, по возможности, Условие. Использование фильтра внутри параметров виртуальной таблицы Регистр.СрезПоследних(&Период, Группа В (&СписокГрупп)) значительно эффективнее, чем фильтрация через ГДЕ или ПО в основном теле запроса. Это происходит потому, что фильтр в параметрах отсекает ненужные записи еще на уровне формирования виртуальной таблицы в СУБД.
Рассмотрим ситуацию, когда дубли сохраняются даже после всех манипуляций. Это может происходить, если регистр имеет периодичность (например, В пределах секунды) и в одну и ту же секунду были созданы записи с абсолютно одинаковыми измерениями. В этом случае СрезПоследних может вернуть обе записи, так как платформа не может определить, какая из них была создана последней (если только регистр не подчинен регистратору, где уникальность гарантируется позицией документа на оси времени).
Также не забывайте про влияние реквизитов и ресурсов. Хотя они не участвуют в формировании ключа уникальности СрезаПоследних, наличие различных значений в ресурсах при одинаковых измерениях (в случае ошибок записи в регистр) может привести к неоднозначному поведению выборки в некоторых специфических конфигурациях.
Для того чтобы ваши запросы к регистрам сведений были быстрыми и не содержали лишних строк, придерживайтесь следующих правил:
СГРУППИРОВАТЬ ПО или РАЗЛИЧНЫЕ.ВНУТРЕННЕЕ СОЕДИНЕНИЕ. Если нужен полный список из первого регистра с дополнением данных из второго — выбирайте ЛЕВОЕ СОЕДИНЕНИЕ.Применяя эти методы, мы обеспечиваем не только корректность данных в табличной части, но и высокую скорость работы программы даже при значительном росте объема информационной базы.