Как выполнить отбор по GUID, получить нулевые остатки и оптимизировать запросы в 1С?

← На главную

В процессе разработки в среде "1С:Предприятие 8" программисты часто сталкиваются с нестандартным поведением запросов при получении остатков товаров, а также с необходимостью быстрого и производительного отбора данных. В этой статье мы подробно разберем несколько ключевых тем, затронутых практикующими разработчиками: как правильно выполнять отборы по уникальным идентификаторам (GUID), как гарантированно получать в отчете позиции с нулевыми остатками, почему нельзя использовать подзапросы в условиях и как бороться с расхождениями между движениями и остатками в регистрах.

Решение 1: Преобразование GUID в ссылку и передача в запрос

Довольно часто возникает ситуация, когда на входе у разработчика есть только строковые представления уникальных идентификаторов (GUID) элементов справочника, и по ним необходимо сделать отбор в запросе. Типичной ошибкой является попытка преобразовать ссылки к строке прямо в теле запроса или перебирать элементы в цикле встроенного языка. Сравнение строк в запросах крайне неэффективно, так как заставляет СУБД выполнять полное сканирование таблицы (Table Scan) вместо использования индексов.

Выясним, как сделать это правильно и быстро. Платформа "1С:Предприятие" позволяет сконструировать ссылку по известному GUID прямо в оперативной памяти компьютера без обращения к базе данных. Для этого используется метод менеджера справочника ПолучитьСсылку(). Сформированные ссылки мы помещаем в массив и передаем в качестве параметра запроса.

Рассмотрим пример кода, реализующий этот оптимальный подход:


// Создаем массив для хранения ссылок
МассивСсылок = Новый Массив;

// Инициализируем объекты УникальныйИдентификатор из строк
УИД_1 = Новый УникальныйИдентификатор("12345678-1234-1234-1234-1234567890ab");
УИД_2 = Новый УникальныйИдентификатор("87654321-4321-4321-4321-210987654321");

// Получаем ссылки без обращения к базе данных
Ссылка1 = Справочники.Номенклатура.ПолучитьСсылку(УИД_1);
Ссылка2 = Справочники.Номенклатура.ПолучитьСсылку(УИД_2);

// Наполняем массив параметрами
МассивСсылок.Добавить(Ссылка1);
МассивСсылок.Добавить(Ссылка2);

// Формируем и выполняем запрос
Запрос = Новый Запрос;
Запрос.Текст = 
    "ВЫБРАТЬ
    |	Номенклатура.Ссылка КАК Товар,
    |	Номенклатура.Код КАК Код
    |ИЗ
    |	Справочник.Номенклатура КАК Номенклатура
    |ГДЕ
    |	Номенклатура.Ссылка В (&МассивСсылок)";

Запрос.УстановитьПараметр("МассивСсылок", МассивСсылок);
РезультатЗапроса = Запрос.Выполнить();

Этот способ работает мгновенно. СУБД производит поиск записей по индексированному полю первичного ключа, что гарантирует максимальное быстродействие даже на таблицах объемом в миллионы записей.

Решение 2: Получение нулевых остатков и корректная обработка значений NULL

Проанализируем ситуацию, когда нам необходимо вывести в отчет весь список номенклатуры (или определенный сегмент), включая те позиции, остаток по которым в данный момент равен нулю. Начинающие разработчики часто пытаются получить такие данные напрямую из виртуальной таблицы остатков, например, РегистрНакопления.СвободныеОстатки.Остатки(). Однако этот подход ошибочен по своей сути.

Важно понимать: виртуальная таблица остатков физически не хранит в базе данных записи с нулевыми итогами. Как только остаток товара становится равным нулю, запись о нем исчезает из таблицы актуальных итогов СУБД. Поэтому простой запрос к таблице остатков никогда не вернет строки с нулевым балансом.

Для решения этой задачи мы должны использовать Левое соединение. Разберем этот механизм по шагам:

В качестве главной (левой) таблицы мы выбираем источник, содержащий полный список интересующих нас товаров. Это может быть справочник Справочник.Номенклатура или таблица сегментов РегистрСведений.НоменклатураСегмента.
К этой таблице мы применяем ЛЕВОЕ СОЕДИНЕНИЕ с виртуальной таблицей остатков регистра по ключевым полям связи (например, по номенклатуре и характеристике).
При левом соединении для тех товаров, по которым нет остатков, СУБД вернет значение NULL во всех полях таблицы остатков. Любая математическая операция с NULL (например, сложение или вычитание резервов) вернет результат NULL, из-за чего в итоговой таблице поле останется пустым. Чтобы этого избежать, мы обязаны использовать функцию ЕСТЬNULL(<Поле>, 0), которая принудительно заменяет отсутствующие значения числовым нулем.

Посмотрим на пример правильного построения запроса для получения в том числе и нулевых остатков:


ВЫБРАТЬ
	Сегменты.Номенклатура КАК Номенклатура,
	Сегменты.Характеристика КАК Характеристика,
	ЕСТЬNULL(СвободныеОстатки.ВНаличииОстаток, 0) КАК ВНаличии,
	ЕСТЬNULL(СвободныеОстатки.ВРезервеОстаток, 0) КАК ВРезерве,
	// Безопасное вычисление свободного остатка:
	ЕСТЬNULL(СвободныеОстатки.ВНаличииОстаток, 0) - ЕСТЬNULL(СвободныеОстатки.ВРезервеОстаток, 0) КАК ИтогоСвободно
ИЗ
	РегистрСведений.НоменклатураСегмента КАК Сегменты
	ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.СвободныеОстатки.Остатки( , 
		Номенклатура В (
			ВЫБРАТЬ СегментыОтбора.Номенклатура 
			ИЗ РегистрСведений.НоменклатураСегмента КАК СегментыОтбора
		)
	) КАК СвободныеОстатки
	ПО Сегменты.Номенклатура = СвободныеОстатки.Номенклатура
	И Сегменты.Характеристика = СвободныеОстатки.Характеристика

Решение 3: Оптимизация запросов — почему подзапросы в условиях вредны

В коде, представленном в обсуждении на форуме, можно встретить конструкцию отбора внутри параметров виртуальной таблицы остатков: (Номенклатура, Характеристика) В (ВЫБРАТЬ ... из ВременнойТаблицы). Согласно стандартам разработки и оптимизации "1С:Предприятие 8", использование вложенных подзапросов в условиях соединения, секциях ГДЕ и в параметрах виртуальных таблиц настоятельно не рекомендуется.

Выясним причины такого ограничения:

Непредсказуемость оптимизатора СУБД. При трансляции запроса из платформы 1С в СУБД (MS SQL, PostgreSQL) оптимизатор базы данных не может заранее оценить точное количество строк, которое вернет внутренний подзапрос. В результате СУБД строит неоптимальный план выполнения запроса. Вместо быстрого поиска по индексам (Index Seek) она может запустить полное сканирование огромных таблиц (Table Scan или Index Scan).
Проблема масштабируемости. Запрос, который отлично и быстро работает на тестовой базе с сотней записей, при переносе на реальную рабочую базу предприятия с тысячами документов начнет вызывать серьезные задержки и блокировки таблиц.

Для того чтобы сделать запрос производительным и безопасным, мы должны заменить вложенные условия на предварительную подготовку данных во Временных таблицах (ВТ). Проанализируем правильный паттерн разработки:

Сначала мы выбираем все данные для фильтрации в отдельный запрос и помещаем их во временную таблицу с помощью ключевого слова ПОМЕСТИТЬ.
Мы обязательно индексируем временную таблицу по тем полям, по которым в дальнейшем будет производиться связь или отбор.
В основном запросе мы связываем индексированную временную таблицу с целевой таблицей через ВНУТРЕННЕЕ СОЕДИНЕНИЕ или передаем ее в условия связи виртуальной таблицы.

Рассмотрим, как переписать проблемный запрос с использованием механизмов временных таблиц:


// 1. Помещаем сегменты номенклатуры во временную таблицу и индексируем ключевые поля
ВЫБРАТЬ РАЗРЕШЕННЫЕ РАЗЛИЧНЫЕ
	Сегменты.Номенклатура КАК Номенклатура,
	Сегменты.Характеристика КАК Характеристика
ПОМЕСТИТЬ ВтОтборПоСегменту
ИЗ
	РегистрСведений.НоменклатураСегмента КАК Сегменты
ИНДЕКСИРОВАТЬ ПО
	Номенклатура,
	Характеристика
;

// 2. Получаем остатки, используя внутреннее соединение с проиндексированной временной таблицей
ВЫБРАТЬ РАЗРЕШЕННЫЕ
	Остатки.Номенклатура КАК Номенклатура,
	Остатки.Характеристика КАК Характеристика,
	ЕСТЬNULL(Остатки.ВНаличииОстаток, 0) КАК ВНаличии,
	ЕСТЬNULL(Остатки.ВРезервеОстаток, 0) КАК ВРезерве
ИЗ
	ВтОтборПоСегменту КАК Отбор
	ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрНакопления.СвободныеОстатки.Остатки КАК Остатки
		ПО Отбор.Номенклатура = Остатки.Номенклатура
		И Отбор.Характеристика = Остатки.Характеристика

Использование этого шаблона проектирования гарантирует, что СУБД построит идеальный план выполнения, задействует системные индексы и выполнит запрос максимально быстро.

Решение 4: Почему "движения есть, а остатков нет" и как помогает пересчет итогов

Иногда разработчики сталкиваются с парадоксальной ситуацией: детальный отчет по движениям регистра (обороты) показывает наличие товара на складе, однако виртуальная таблица остатков на ту же дату возвращает пустую строку или некорректное значение.

Разберемся в архитектуре регистров накопления 1С, чтобы понять причину происходящего:

Платформа 1С не рассчитывает актуальные остатки "на лету" с самого начала ведения учета при каждом запросе пользователя — это занимало бы слишком много времени.
Для ускорения работы система хранит предварительно рассчитанные остатки на фиксированные точки времени (обычно на начало каждого месяца) в специальных служебных таблицах итогов.
При записи новых документов система одновременно добавляет движения в таблицу движений и обновляет данные в таблицах итогов.

Однако в результате сбоев сети, аварийного завершения работы СУБД или некорректно настроенных транзакций может произойти рассинхронизация: данные в таблице движений записались, а таблицы итогов не обновились. В профессиональной среде это называют "расползанием" или "битыми" итогами.

Для исправления этой неполадки необходимо выполнить процедуру Пересчета итогов. Она полностью очищает таблицы хранения остатков и заново рассчитывает их значения на основе детальных записей таблицы движений. Выполнить эту процедуру можно несколькими способами:

В режиме "Конфигуратор" открыть меню Администрирование — Тестирование и исправление..., установить флаг "Пересчет итогов" и запустить процесс.
В режиме "1С:Предприятие" (для администратора базы данных) перейти в стандартный интерфейс управления итогами (раздел "Поддержка и обслуживание" — "Режимы работы" — "Управление итогами") и запустить пересчет для проблемных регистров.

После завершения пересчета актуальные остатки придут в полное соответствие с совершенными движениями документов, и виртуальные таблицы запросов начнут возвращать корректную информацию.