Как в 1С найти элементы справочника, которые не используются в документах?

← На главную

В процессе разработки и поддержки систем на платформе 1С:Предприятие часто возникает задача очистки базы данных от "мусора" или анализа неиспользуемых данных. Типичный пример такой задачи — поиск элементов справочника, которые ни разу не были выбраны в документах. Рассмотрим, как правильно составить запрос для решения этой проблемы, разберем типичные ошибки новичков и проанализируем различные способы оптимизации для работы с большими объемами данных.

Разбор типичной ошибки при использовании соединений

Проанализируем ситуацию, описанную автором темы. Попытка выполнить ЛЕВОЕ СОЕДИНЕНИЕ от таблицы документов к таблице справочника с условием ПО (Документ1.Реквизит1 <> Справочник1.Ссылка) является логически неверной. Причина в том, что если в базе данных вообще нет документов (пустая таблица), то левое соединение от пустого множества всегда вернет пустой результат. Кроме того, условие "не равно" в соединении порождает так называемое декартово произведение, что крайне негативно сказывается на производительности.

Вариант 1: Классическое левое соединение (LEFT JOIN)

Рассмотрим самый простой и эффективный способ для небольших и средних справочников. Основная идея заключается в том, что мы выбираем все элементы из справочника и пытаемся "присоединить" к ним документы. Те элементы справочника, для которых документов не нашлось, будут иметь значение NULL в полях присоединенной таблицы.

Разберем этот запрос по шагам:


ВЫБРАТЬ
    Справочник1.Ссылка КАК Ссылка
ИЗ
    Справочник.Справочник1 КАК Справочник1
        ЛЕВОЕ СОЕДИНЕНИЕ Документ.Документ1 КАК Документ1
        ПО Справочник1.Ссылка = Документ1.Реквизит1
ГДЕ
    Документ1.Ссылка ЕСТЬ NULL

В данном случае мы используем условие ГДЕ Документ1.Ссылка ЕСТЬ NULL. Это ключевой момент: если в Документ1 нет записи с таким реквизитом, ссылка на сам документ будет пустой (NULL). Этот метод работает надежно даже в том случае, если таблица документов абсолютно пуста — в этом случае запрос вернет полный список элементов справочника.

Вариант 2: Использование вложенного запроса (НЕ В...)

Посмотрим на другой подход, который часто кажется более понятным с точки зрения человеческой логики: "выбрать всё из справочника, кроме тех ссылок, которые есть в документах".


ВЫБРАТЬ
    Справочник1.Ссылка КАК Ссылка
ИЗ
    Справочник.Справочник1 КАК Справочник1
ГДЕ
    НЕ Справочник1.Ссылка В
            (ВЫБРАТЬ РАЗЛИЧНЫЕ
                Документ1.Реквизит1
            ИЗ
                Документ.Документ1 КАК Документ1)

Проанализируем этот вариант. Несмотря на наглядность, у него есть несколько особенностей:

Производительность: На уровне SQL-запроса конструкция NOT IN (которая генерируется платформой 1С) может работать медленнее, чем LEFT JOIN, особенно в старых версиях СУБД.
NULL-значения: Если во вложенном запросе среди результатов окажется значение NULL, то в классическом SQL всё условие NOT IN может вернуть пустой результат. Платформа 1С обычно обрабатывает это корректно, но об этом стоит помнить.
Читаемость: Для опытных разработчиков вложенные запросы в секции ГДЕ считаются менее предпочтительными, чем явные соединения.

Вариант 3: Оптимизация через временные таблицы

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

Рассмотрим правильный алгоритм оптимизации:


// Шаг 1: Собираем все используемые ссылки в маленькую временную таблицу
ВЫБРАТЬ РАЗЛИЧНЫЕ
    Документ1.Реквизит1 КАК Ссылка
ПОМЕСТИТЬ ВТ_ИспользуемыеСсылки
ИЗ
    Документ.Документ1 КАК Документ1
ГДЕ
    Документ1.Реквизит1 <> ЗНАЧЕНИЕ(Справочник.Справочник1.ПустаяСсылка)
ИНДЕКСИРОВАТЬ ПО
    Ссылка;

////////////////////////////////////////////////////////////////////////////////
// Шаг 2: Выполняем соединение справочника с подготовленным списком
ВЫБРАТЬ
    Справочник1.Ссылка КАК Ссылка
ИЗ
    Справочник.Справочник1 КАК Справочник1
        ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ИспользуемыеСсылки КАК ВТ
        ПО Справочник1.Ссылка = ВТ.Ссылка
ГДЕ
    ВТ.Ссылка ЕСТЬ NULL

Почему этот метод лучше? Во-первых, мы используем ВЫБРАТЬ РАЗЛИЧНЫЕ, что существенно сокращает объем данных во временной таблице. Во-вторых, мы применяем ИНДЕКСИРОВАТЬ ПО, что позволяет СУБД мгновенно находить соответствия во второй части запроса. Это значительно снижает нагрузку на дисковую подсистему и процессор.

Важные практические советы

При решении подобных задач в реальных конфигурациях (например, 1C:ERP или 1С:Управление торговлей) необходимо учитывать дополнительные условия бизнеса. Рассмотрим основные из них:

Пометка на удаление: Часто требуется найти элементы, которые не просто не используются, но и сами не помечены на удаление. В этом случае добавьте условие ГДЕ НЕ Справочник1.ПометкаУдаления.
Проведенность документов: Если элемент справочника указан в черновике документа, считается ли он используемым? Если нет, добавьте во временную таблицу условие ГДЕ Документ1.Проведен.
Индексация реквизита: Для того чтобы любой из вышеуказанных запросов работал быстро, реквизит Реквизит1 в документе должен иметь свойство "Индексировать". Если индекс отсутствует, поиск по миллионам документов превратится в "Full Table Scan" (полное сканирование таблицы), что может "подвесить" базу.
Иерархия справочника: Если справочник иерархический, не забудьте отсечь группы (папки), так как они технически являются элементами справочника, но в документах обычно не выбираются. Используйте условие ГДЕ НЕ Справочник1.ЭтоГруппа.

Подводя итог, можно сказать: для простых разовых задач идеально подходит ЛЕВОЕ СОЕДИНЕНИЕ с проверкой на NULL. Для высоконагруженных систем и регламентных заданий по очистке данных всегда следует отдавать предпочтение схеме с использованием временных таблиц и индексации.