При работе с крупными информационными базами 1С, где счет записей в таблицах идет на десятки миллионов, стандартные механизмы запросов могут сталкиваться с серьезными проблемами производительности. Типичная задача — поиск объектов одной таблицы, для которых отсутствуют записи в связанной таблице (так называемый Anti-Join). В этой статье мы разберем, как оптимизировать такой поиск, если стандартное ЛЕВОЕ СОЕДИНЕНИЕ с проверкой на NULL выполняется недопустимо долго.
Рассмотрим ситуацию: у нас есть справочник Контрагенты (32 млн записей) и регистр сведений РеквизитыКонтрагентов (65 млн записей). Нам нужно найти тех контрагентов, у которых нет записей в регистре. Стандартный запрос выглядит так:
ВЫБРАТЬ
Контрагенты.Наименование КАК Наименование,
Контрагенты.Ссылка КАК Ссылка
ИЗ
Справочник.Контрагенты КАК Контрагенты
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.РеквизитыКонтрагентов КАК РеквизитыКонтрагентов
ПО Контрагенты.Ссылка = РеквизитыКонтрагентов.Ссылка
ГДЕ
РеквизитыКонтрагентов.Ссылка ЕСТЬ NULL
Несмотря на наличие индексов по полям связи, выполнение такого запроса может занимать несколько минут. Почему так происходит? При таком объеме данных СУБД (например, MS SQL Server) зачастую выбирает алгоритм Hash Match или полное сканирование индексов (Index Scan) обеих таблиц. Это приводит к огромному количеству операций чтения и нагрузки на дисковую подсистему.
Один из эффективных способов ускорить выполнение — заменить прямое соединение на использование оператора ГДЕ НЕ ... В (...). Это позволяет оптимизатору SQL иначе взглянуть на план выполнения. Разберем два варианта реализации этого подхода.
Вариант с предварительной группировкой в подзапросе:
ВЫБРАТЬ
Контрагенты.Наименование КАК Наименование,
Контрагенты.Ссылка КАК Ссылка
ИЗ
Справочник.Контрагенты КАК Контрагенты
ГДЕ
НЕ Контрагенты.Ссылка В
(ВЫБРАТЬ
РеквизитыКонтрагентов.Ссылка КАК Ссылка
ИЗ РегистрСведений.РеквизитыКонтрагентов КАК РеквизитыКонтрагентов
СГРУППИРОВАТЬ ПО
РеквизитыКонтрагентов.Ссылка)
Вариант с имитацией оператора EXISTS:
В 1С нет прямого оператора EXISTS, но его можно имитировать через конструкцию ГДЕ НЕ 1 В (ВЫБРАТЬ ПЕРВЫЕ 1 1...). Это заставляет SQL-сервер прекращать поиск в связанной таблице сразу после нахождения первой же подходящей записи, что экономит ресурсы.
ВЫБРАТЬ
Контрагенты.Наименование КАК Наименование,
Контрагенты.Ссылка КАК Ссылка
ИЗ
Справочник.Контрагенты КАК Контрагенты
ГДЕ НЕ 1 В (ВЫБРАТЬ ПЕРВЫЕ 1
1
ИЗ РегистрСведений.РеквизитыКонтрагентов КАК РеквизитыКонтрагентов
ГДЕ РеквизитыКонтрагентов.Ссылка = Контрагенты.Ссылка)
Как показала практика, такие изменения могут сократить время выполнения запроса в 1.5–2 раза за счет изменения плана запроса на уровне СУБД.
Проанализируем ситуацию, когда нам нужно получить не только ссылку, но и дополнительные поля (например, Наименование, Код, ИНН). Выбор тяжелых полей (строк неограниченной длины или просто длинных строк) во время первичного соединения сильно замедляет процесс Merge или Hash Join.
Рассмотрим стратегию в два шага:
Ссылка «проблемных» объектов и поместим их во временную таблицу.
ВЫБРАТЬ
Контрагенты.Ссылка КАК Ссылка
ПОМЕСТИТЬ ВТ_ПустыеСсылки
ИЗ
Справочник.Контрагенты КАК Контрагенты
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.РеквизитыКонтрагентов КАК РеквизитыКонтрагентов
ПО Контрагенты.Ссылка = РеквизитыКонтрагентов.Ссылка
ГДЕ
РеквизитыКонтрагентов.Ссылка ЕСТЬ NULL
;
ВЫБРАТЬ
Контрагенты.Ссылка,
Контрагенты.Наименование
ИЗ
Справочник.Контрагенты КАК Контрагенты
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_ПустыеСсылки КАК ВТ_ПустыеСсылки
ПО Контрагенты.Ссылка = ВТ_ПустыеСсылки.Ссылка
Важный нюанс: Этот метод эффективен только в том случае, если вы точно знаете, что результат выборки будет небольшим. Если «пустых» записей миллионы, создание временной таблицы может, наоборот, замедлить процесс.
Если запрос должен выполняться часто (например, в регламентном задании каждые 10 минут), никакие оптимизации текста запроса не помогут на 32 миллионах записей так, как изменение структуры данных. Разберем основные архитектурные подходы:
Справочник.Контрагенты булевский реквизит ЕстьРеквизиты и установим для него признак «Индексировать». При записи данных в регистр мы будем обновлять этот флаг. Запрос по индексированному булеву полю к 32 млн записей отработает мгновенно.КонтрагентыБезРеквизитов. При удалении записи из основного регистра — добавляем ссылку туда, при добавлении — удаляем. Тогда нам вообще не придется сканировать огромные таблицы.Осторожно: Если данные в SQL попадают напрямую (минуя код 1С), стандартные подписки на события 1С не сработают. В этом случае необходимо настроить SQL-триггеры на уровне базы данных для поддержания актуальности флагов или очередей.
Проанализируем технические аспекты, которые могут тормозить запрос на стороне SQL-сервера:
Обновление статистики: На больших таблицах критически важно иметь актуальную статистику. Если статистика устарела, SQL-оптимизатор может думать, что пустых записей будет 10 миллионов вместо 60, и выберет самый медленный план сканирования. Рекомендуется выполнить UPDATE STATISTICS для обеих таблиц.
Покрывающие индексы: Если вы часто ищете по связке Ссылка + Наименование, на уровне SQL можно создать некластерный индекс, который включает в себя наименование. Это позволит серверу прочитать все данные из индекса, не обращаясь к «тяжелой» основной таблице справочника.
Настройка MDOP (Max Degree of Parallelism): Для запросов, сканирующих миллионы строк, параллелизм может как помочь, так и навредить. Выясним у администратора БД, не ограничено ли выполнение запроса одним ядром процессора, что часто бывает в сильно нагруженных системах.
Подводя итог, отметим: для разовых задач на 30+ млн записей оптимально использовать подзапросы или временные таблицы. Для регулярных задач единственным верным решением будет изменение структуры данных (внедрение флагов или вспомогательных таблиц).