Проблема соединения двух таблиц (LEFT JOIN: 26 млн строк слева, 4 млн строк справа)

Question

Всем здравствуйте! Столкнулся с непонятной для меня проблемой, когда не могу сджойнить пару таблиц (LEFT JOIN, 28 млн строк слева, 4 млн строк справа). Уже три дня перебираю различные варианты, пока что успеха не достиг. Это повергает в уныние. Оптимизатор по умолчанию выбирает LOOP, но и принудительный перевод в HASH/MERGE не даёт результата даже при ограничении выборки, не говоря уже о полном наборе данных в таблицах :'(

Преднастройки:

SQL Server Microsoft SQL Server 2017 (RTM-GDR) (KB4505224) - 14.0.2027.2 (X64) Jun 15 2019 00:26:19 Copyright (C) 2017 Microsoft Corporation Developer Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 (Build 9600: ) (Hypervisor)
БД в режиме совместимости SQL Server 2017 (140)
Статистики таблиц запроса пересчитаны с FULLSCAN

Таблицы:

В кабинетах, работающих по расписанию, производится отпуск услуг.

Таблица ServicePointScheduleEveryDay

с информацией о рабочих по расписанию интервалах кабинетов на каждый календарный день. Интервалы не пересекаются. Количество строк за всё время 28 206 685.


    CREATE TABLE staging.ServicePointScheduleEveryDay
    (
        ServicePointId dbo.SD_ID NOT NULL,  --кабинет 
        [DayOfWeek] dbo.SD_ID NOT NULL,     --номер дня недели
        ScheduleId dbo.SD_ID NOT NULL,      --id расписания
        StartDateTime datetime NULL,        --начало рабочего интервала кабинета в конкретную календарную дату
        EndDateTime datetime NULL,          --конец рабочего интервала кабинета в конкретную календарную дату
        
        CONSTRAINT [PK_ServicePointScheduleEveryDay] PRIMARY KEY CLUSTERED 
        (
            [ServicePointId] ASC,
            [StartDateTime] ASC,
            [EndDateTime] ASC
        )
    )

Таблица VScheduleItem

с информацией об отпусках услуг, можно считать их интервалами занятости. Количество строк за всё время: 3 894 942. Относительно интервалов доступности интервалы занятости могут:

совпадать с размером рабочей ячейки кабинета
занимать несколько рабочих ячеек кабинета (не обязательно целых)
находиться внутри рабочей ячейки Параллельно может проходить несколько интервалов занятости


    CREATE TABLE staging.VScheduleItem
    (
        [Id] [bigint] IDENTITY(1,1) NOT NULL,
        ServicePointId dbo.SD_ID NULL,      --кабинет
        StartDateTime datetime NOT NULL,    --начало отпуска услуги (на одно время может приходиться несколько услуг) / интервала занятости кабинета в конкретную календарную дату 
        EndDateTime datetime NULL           --конец отпуска услуги (на одно время может приходиться несколько услуг) / интервала занятости кабинета в конкретную календарную дату 
        
        CONSTRAINT [PK_VScheduleItem] PRIMARY KEY NONCLUSTERED 
        (
            [Id] ASC
        )   
    )
    
    CREATE CLUSTERED INDEX pr_id ON staging.VScheduleItem
    (
        ServicePointId ASC,
        StartDateTime ASC,
        EndDateTime ASC
    )

Задача

Для каждого из рабочих интервалов кабинетов на каждый календарный день (ServicePointScheduleEveryDay) определить количество приходящихся интервалов занятости (VScheduleItem).

Проблема/предпринимаемые попытки решения

Попытка 1. Изначально пытался зачем-то вставлять результат в новую таблицу ServicePointScheduleEDScheduleItemCount, где поля совпадают с таблицей ServicePointScheduleEveryDay, плюс добавляется новое поле ScheduleItemCount.

Напомню, в левой таблице 28 млн строк, в правой таблице - 4 млн. Пытался ограничить в WHERE для одного кабинета (ServicePointId = 5), при таком раскладе в левой таблице 376 852 строк, в правой - 87 832. Дождаться результата выполнения запроса не удаётся. Смена LEFT на INNER (хоть это и не в интересах моей задачи) положительного результата не даёт. По умолчанию оптимизатор выбирает LOOP JOIN и ожидает получить миллиарды записей. Принудительный хинт в MERGE/HASH успеха не дают.


    INSERT INTO [staging].[ServicePointScheduleEDScheduleItemCount]
               ([ServicePointId]
               ,[DayOfWeek]
               ,[ScheduleId]
               ,[StartDateTime]
               ,[EndDateTime]
               ,[ScheduleItemCount])
        SELECT 
            sps.ServicePointId
            ,sps.DayOfWeek
            ,sps.ScheduleId
            ,sps.StartDateTime
            ,sps.EndDateTime
            ,SUM(CASE WHEN si.StartDateTime IS NOT NULL THEN 1 ELSE 0 END) ScheduleItemCount
        FROM 
            staging.ServicePointScheduleEveryDay sps
            LEFT MERGE/*HASH*/ JOIN staging.VScheduleItem si ON 
                sps.ServicePointId = si.ServicePointId 
                AND sps.StartDateTime < si.EndDateTime AND sps.EndDateTime > si.StartDateTime --условие пересечения временных интервалов
        WHERE sps.ServicePointId = 5 --даже при ограничении на один кабинет результат не удаётся получить
        GROUP BY 
            sps.ServicePointId
            ,sps.DayOfWeek
            ,sps.ScheduleId
            ,sps.StartDateTime
            ,sps.EndDateTime

В попытках 2 и 3 временно отказался от результата в виде количества приходящихся интервалов занятости, а просто хотел определить был ли занят рабочий интервал или нет: в таблицу ServicePointScheduleEveryDay добавил битовое поле IsBusySegment.

Попытка 2. Обновлять ServicePointScheduleEveryDay.IsBusySegment


    UPDATE ed
    SET IsBusySegment = 1 
    FROM
        staging.ServicePointScheduleEveryDay ed
        INNER MERGE JOIN staging.VScheduleItem sps ON
                sps.ServicePointId = ed.ServicePointId 
                AND (sps.StartDateTime < ed.EndDateTime) 
                AND (sps.EndDateTime > ed.StartDateTime)
                AND sps.ServicePointId = 5      
    WHERE 
        ed.ServicePointId = 5

Попытка 3. Обновлять ServicePointScheduleEveryDay.IsBusySegment для случаев WHERE EXISTS записи в таблице интервалов занятости. При таком раскладе оптимизатор выбирает LOOP JOIN, повлиять на выбор способа не нашёл.

БЛОГ НА HUSL