Соединения в запросах — без подзапросов и виртуальных таблиц

Директивный

диагностикаJoinWithSubQuery диагностикаJoinWithVirtualTable диагностикаFieldsFromJoinsWithoutIsNull диагностикаLogicalOrInJoinQuerySection

#запросы #производительность

Правило

Не соединяться с подзапросом. Результат подзапроса сначала помещается во временную таблицу (при необходимости — с индексом по полям соединения), и соединение выполняется с ней.
Не соединяться с виртуальной таблицей (.Остатки, .СрезПоследних и т.п.) — её результат так же выносится во временную таблицу.
Поля, полученные левым/правым соединением, оборачивать в ЕСТЬNULL(...) или явно проверять ЕСТЬ NULL — иначе NULL расползается по расчётам и группировкам.
Не использовать ИЛИ в условиях соединения (ПО ... ИЛИ ...) — оптимизатор теряет индексы; переформулировать через объединение или условие в ГДЕ.

Зачем

СУБД не имеет статистики по результату подзапроса и виртуальной таблицы, поэтому оптимизатор строит план вслепую: типичный итог — вложенные циклы по огромной выборке, и запрос, работавший секунду на тесте, виснет на минутах на боевых объёмах. Временная таблица даёт оптимизатору материализованный набор со статистикой и предсказуемый план. Это одна из самых частых причин «отчёт не открывается на проде».

ВЫБРАТЬ
    Товары.Номенклатура,
    Остатки.КоличествоОстаток
ИЗ
    Документ.Реализация.Товары КАК Товары
        ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыНаСкладах.Остатки(&Дата,) КАК Остатки
        ПО Товары.Номенклатура = Остатки.Номенклатура
            ИЛИ Товары.НоменклатураЗамена = Остатки.Номенклатура

ВЫБРАТЬ
    Остатки.Номенклатура,
    Остатки.КоличествоОстаток
ПОМЕСТИТЬ ВТ_Остатки
ИЗ
    РегистрНакопления.ТоварыНаСкладах.Остатки(&Дата,) КАК Остатки
ИНДЕКСИРОВАТЬ ПО
    Номенклатура
;
ВЫБРАТЬ
    Товары.Номенклатура,
    ЕСТЬNULL(ВТ_Остатки.КоличествоОстаток, 0) КАК Остаток
ИЗ
    Документ.Реализация.Товары КАК Товары
        ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Остатки КАК ВТ_Остатки
        ПО Товары.Номенклатура = ВТ_Остатки.Номенклатура

NULL тихо ломает и условия

Опасность NULL не только в расчётах: сравнение NULL с чем угодно — не Истина, поэтому условие в ГДЕ по полю левого соединения молча выбрасывает строки без пары. Классика — отчёт «дефицит» пропускает как раз худший случай:

ВЫБРАТЬ Товары.Номенклатура
ИЗ Справочник.Номенклатура КАК Товары
    ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Остатки КАК Остатки
    ПО Товары.Ссылка = Остатки.Номенклатура
ГДЕ
    Остатки.КоличествоОстаток < &Минимум
    // У номенклатуры БЕЗ остатков поле = NULL, сравнение не Истина —
    // позиции с нулевым остатком в «дефицит» не попали.

ГДЕ
    ЕСТЬNULL(Остатки.КоличествоОстаток, 0) < &Минимум
    // Нет записи об остатке = остаток 0 — попадает в дефицит.

Тот же эффект в обратную сторону: условие вида Остатки.Поле = &Значение в ГДЕ превращает левое соединение во внутреннее — если условие относится к присоединяемой таблице, его место в секции ПО или внутри временной таблицы.

Как обнаружить

Явные случаи ловят диагностики BSL LS из фронтматтера этой страницы (соединение с подзапросом/виртуальной таблицей, поле без ЕСТЬNULL, ИЛИ в ПО). Уже работающий медленный запрос диагностируется планом: типичный признак — nested loops по большой выборке там, где ожидался хеш/merge-join по индексу. Проверять на объёмах, сопоставимых с боевыми, — на двух тестовых записях любой план быстрый (см. перф-прогон горячих мест).

Когда не применять

Заведомо маленькие выборки в разовых обработках — временная таблица не даст выигрыша; но в коде конфигурации (отчёты, проведение, обмены) правило действует всегда: объём данных вырастет без вас.
ЕСТЬNULL не нужен, если NULL-поле тут же отфильтровывается условием ЕСТЬ NULL / ЕСТЬ НЕ NULL в ГДЕ.