Problemas com desempenho lento do procedimento armazenado em 35 milhões de linhas com tabela CTE/Temp

Recentemente, lançamos um novo recurso em nosso aplicativo que adicionou cerca de 25 novas colunas a uma tabela grande (cerca de 35 milhões de linhas) e agora estamos tendo alguns problemas importantes de desempenho de consulta. Presumo que esteja relacionado à enorme quantidade de dados que foram adicionados como parte dessas novas colunas, mas também pode ser baseado em índice, em consulta ou em qualquer outra coisa em que não estou pensando.

Esta tabela contém informações sobre a passagem do crachá junto com informações sobre a pessoa que fez a passagem. Ele reside em um banco de dados AWS RDS e tenho controle total sobre o esquema, mas não sobre a própria instância do RDS. O esquema desta tabela é:

CREATE TABLE [occupancy].[SwipesComplete] (
    [PrimaryObjectID] varchar,
    [ObjectID] int,
    [UserID] varchar, 
    [Name] varchar,
    [PersonnelTypeID] varchar,
    [DoorName] varchar,
    [SwipetimeUTC] datetime,
    [SwipetimeEST] datetime,
    [DoorID] int,
    [SiteID] int,
    [GroupDesc1] varchar,
    [GroupDesc2] varchar,
    [GroupDesc3] varchar,
    [GroupDesc4] varchar,
    [GroupDesc5] varchar,
    [GroupDesc6] varchar,
    [GroupDesc7] varchar,
    [GroupDesc8] varchar,
    [GroupDesc9] varchar,
    [GroupDesc10] varchar,
    [GroupDesc11] varchar,
    [GroupDesc12] varchar,
    [Company] varchar,
    [Site] varchar,
    [Lab_User] int,
    [PersonAssignedBuildingRoomID] varchar,
    [GroupName] varchar,
    [NeighborhoodAssignedSiteCode] varchar,
    [NeighborhoodAssignedSeat] varchar,
    [NeighborhoodName] varchar,
    [PersonnelType] varchar,
    [EmploymentType] varchar,
    [PTFriendlyName] varchar,
    [PersonAssignedBuildingLocID] int,
    [PersonAssignedAreaLocID] int,
    [PersonAssignedFloorLocID] int,
    [LocationCorrelationSourceID] int,
    [SwipeBuildingLocID] int
);

Temos um aplicativo baseado na web que permite aos usuários consultar esses dados e exibir dados agregados em um gráfico com base em um intervalo selecionado pelo usuário (por hora, diariamente, semanalmente, mensalmente, anualmente). Eles podem filtrar quase todas essas colunas.

Existem índices nesta tabela (não os criei e talvez precisem ser modificados):

• Não exclusivo, não agrupado em PrimaryObjectID e ObjectID
• Não exclusivo, não agrupado em DoorID e SiteID
• Não exclusivo, não agrupado no DoorID
• Não exclusivo, não agrupado em SiteID e SwipetimeUTC
• Não exclusivo, não agrupado no DoorID
• Não exclusivo, não agrupado em ObjectID
• Não exclusivo, não agrupado em SwipetimeEST
• Não exclusivo, não agrupado em DoorID e SwipetimeUTC
• Não exclusivo, não agrupado em SwipetimeUTC

Estamos consultando esses dados usando o seguinte procedimento armazenado. Passamos uma matriz JSON para muitos WHEREs porque os usuários podem escolher vários valores para muitos dos filtros. Os usuários também podem optar por agrupar ou não os dados, como você pode ver na instrução CASE. Eu sei que este procedimento armazenado não é ótimo:

CREATE PROCEDURE [occupancy].[GetUniqueOccupancyByRange]        
(
       @StartDate datetime
       ,@EndDate datetime
       ,@Interval nvarchar(20)
       ,@PTFriendlyName VARCHAR(4000)
       ,@SET VARCHAR(4000)
       ,@Function VARCHAR(4000)
       ,@BusinessUnit VARCHAR(4000)
       ,@AssignedSite VARCHAR(4000) -- assigned site
       ,@AssignedLocID VARCHAR(4000) -- assigned building
       ,@NeighborhoodName VARCHAR(4000)
       ,@SwipeLocID VARCHAR(4000)
       ,@SiteID INT
       ,@GroupBy VARCHAR(255)
)  
AS  
  BEGIN  
    if @Interval = 'Hourly'
        BEGIN
            WITH MyCTE AS 
            (
                SELECT ObjectID, DATEPART(year, SwipetimeEST) AS year, DATEPART(month, SwipetimeEST) AS month, DATEPART(week, SwipetimeEST) AS week, DATEPART(day, SwipetimeEST) AS day, DATEPART(hour, SwipetimeEST) AS hour,
                    -- Mapping the @GroupBy options to the respective columns
                    CASE 
                        WHEN @GroupBy = 'None' THEN NULL
                        WHEN @GroupBy = 'Personnel Type' THEN PTFriendlyName
                        WHEN @GroupBy = 'Senior Executive Team' THEN GroupDesc3
                        WHEN @GroupBy = 'Assigned Building' THEN l.BuildingLocName
                        WHEN @GroupBy = 'Swipes by Building' THEN loc.BuildingLocName
                    END AS GroupingClause
                FROM [Database_Server].[occupancy].[SwipesComplete] sc
                LEFT JOIN [Database_Server].[occupancy].[DoorsComplete] dc
                ON (sc.DoorID = dc.DoorID)
                LEFT JOIN [Join_Database].[site].[LocationInformation] l 
                ON (sc.PersonAssignedBuildingLocID = l.LocID)
                LEFT JOIN [Join_Database].[site].[LocationInformation] loc 
                ON (sc.SwipeBuildingLocID = loc.LocID)
                WHERE sc.SiteID = @SiteID 
                AND SwipetimeUTC BETWEEN @StartDate AND @EndDate
                AND (sc.Name IS NULL OR sc.Name NOT LIKE '%Visitor%')
                AND dc.AssetLocID IS NOT NULL 
                AND dc.AssetLocID != 5
                AND sc.SwipetimeEST IS NOT NULL
                AND (@NeighborhoodName IS NULL OR sc.NeighborhoodName IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@NeighborhoodName)))
                AND (@SET IS NULL OR sc.GroupDesc3 IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@SET)))
                AND (@Function IS NULL OR sc.GroupDesc4 IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@Function)))
                AND (@BusinessUnit IS NULL OR sc.GroupDesc5 IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@BusinessUnit)))
                AND (@PTFriendlyName IS NULL OR  sc.PTFriendlyName IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@PTFriendlyName)))
                AND (@AssignedLocID IS NULL OR sc.PersonAssignedBuildingLocID IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@AssignedLocID)))
                AND (@AssignedSite IS NULL OR sc.Site IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@AssignedSite))) 
                AND (@SwipeLocID is NULL OR sc.SwipeBuildingLocID IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@SwipeLocID)))
            )
            SELECT COUNT(DISTINCT(ObjectID)) as count, year, month, week, day, hour, GroupingClause AS GroupedBy
            FROM MyCTE
            GROUP BY year, month, week, day, hour, GroupingClause
    END

    if @Interval = 'Daily'
        BEGIN
            WITH MyCTE AS 
                (
                    SELECT ObjectID, DATEPART(year, SwipetimeEST) AS year, DATEPART(month, SwipetimeEST) AS month, DATEPART(week, SwipetimeEST) AS week, DATEPART(day, SwipetimeEST) AS day, 
                        CASE 
                            WHEN @GroupBy = 'None' THEN NULL
                            WHEN @GroupBy = 'Personnel Type' THEN PTFriendlyName
                            WHEN @GroupBy = 'Senior Executive Team' THEN GroupDesc3
                            WHEN @GroupBy = 'Assigned Building' THEN l.BuildingLocName
                            WHEN @GroupBy = 'Swipes by Building' THEN loc.BuildingLocName
                        END AS GroupingClause
                    FROM [Database_Server].[occupancy].[SwipesComplete] sc
                    LEFT JOIN [Database_Server].[occupancy].[DoorsComplete] dc
                    ON (sc.DoorID = dc.DoorID)
                    LEFT JOIN [Join_Database].[site].[LocationInformation] l
                    ON (sc.PersonAssignedBuildingLocID = l.LocID)
                    LEFT JOIN [Join_Database].[site].[LocationInformation] loc 
                    ON (sc.SwipeBuildingLocID = loc.LocID)
                    WHERE sc.SiteID = @SiteID 
                    AND SwipetimeUTC BETWEEN @StartDate AND @EndDate
                    AND (sc.Name IS NULL OR sc.Name NOT LIKE '%Visitor%')
                    AND dc.AssetLocID IS NOT NULL 
                    AND dc.AssetLocID != 5
                    AND sc.SwipetimeEST IS NOT NULL
                    AND (@NeighborhoodName IS NULL OR sc.NeighborhoodName IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@NeighborhoodName)))
                    AND (@SET IS NULL OR sc.GroupDesc3 IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@SET)))
                    AND (@Function IS NULL OR sc.GroupDesc4 IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@Function)))
                    AND (@BusinessUnit IS NULL OR sc.GroupDesc5 IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@BusinessUnit)))
                    AND (@PTFriendlyName IS NULL OR sc.PTFriendlyName IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@PTFriendlyName)))
                    AND (@AssignedLocID IS NULL OR sc.PersonAssignedBuildingLocID IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@AssignedLocID)))
                    AND (@AssignedSite IS NULL OR sc.Site IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@AssignedSite)))
                    AND (@SwipeLocID is NULL OR sc.SwipeBuildingLocID IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@SwipeLocID)))
                )
                SELECT COUNT(DISTINCT(ObjectID)) as count, year, month, week, day, GroupingClause as GroupedBy
                FROM MyCTE
                GROUP BY year, month, week, day, GroupingClause
        END

    if @Interval = 'Weekly'
        BEGIN
            WITH MyCTE AS 
                (
                    SELECT ObjectID, DATEPART(year, SwipetimeEST) AS year, DATEPART(week, SwipetimeEST) AS week, 
                        CASE 
                            WHEN @GroupBy = 'None' THEN NULL
                            WHEN @GroupBy = 'Personnel Type' THEN PTFriendlyName
                            WHEN @GroupBy = 'Senior Executive Team' THEN GroupDesc3
                            WHEN @GroupBy = 'Assigned Building' THEN l.BuildingLocName
                            WHEN @GroupBy = 'Swipes by Building' THEN loc.BuildingLocName
                        END AS GroupingClause
                    FROM [Database_Server].[occupancy].[SwipesComplete] sc
                    LEFT JOIN [Database_Server].[occupancy].[DoorsComplete] dc
                    ON (sc.DoorID = dc.DoorID)
                    LEFT JOIN [Join_Database].[site].[LocationInformation] l
                    ON (sc.PersonAssignedBuildingLocID = l.LocID)
                    LEFT JOIN [Join_Database].[site].[LocationInformation] loc 
                    ON (sc.SwipeBuildingLocID = loc.LocID)
                    WHERE sc.SiteID = @SiteID 
                    AND SwipetimeUTC BETWEEN @StartDate AND @EndDate
                    AND (sc.Name IS NULL OR sc.Name NOT LIKE '%Visitor%')
                    AND dc.AssetLocID IS NOT NULL 
                    AND dc.AssetLocID != 5
                    AND sc.SwipetimeEST IS NOT NULL
                    AND (@NeighborhoodName IS NULL OR sc.NeighborhoodName IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@NeighborhoodName)))
                    AND (@SET IS NULL OR sc.GroupDesc3 IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@SET)))
                    AND (@Function IS NULL OR sc.GroupDesc4 IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@Function)))
                    AND (@BusinessUnit IS NULL OR sc.GroupDesc5 IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@BusinessUnit)))
                    AND (@PTFriendlyName IS NULL OR sc.PTFriendlyName IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@PTFriendlyName)))
                    AND (@AssignedLocID IS NULL OR sc.PersonAssignedBuildingLocID IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@AssignedLocID)))
                    AND (@AssignedSite IS NULL OR sc.Site IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@AssignedSite)))
                    AND (@SwipeLocID is NULL OR sc.SwipeBuildingLocID IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@SwipeLocID)))
                )
                SELECT COUNT(DISTINCT(ObjectID)) as count, year, week, GroupingClause as GroupedBy
                FROM MyCTE
                GROUP BY year, week, GroupingClause
        END

    if @Interval = 'Monthly'
        BEGIN
            WITH MyCTE AS 
                (
                    SELECT ObjectID, DATEPART(year, SwipetimeEST) AS year, DATEPART(month, SwipetimeEST) AS month, 
                        CASE 
                            WHEN @GroupBy = 'None' THEN NULL
                            WHEN @GroupBy = 'Personnel Type' THEN PTFriendlyName
                            WHEN @GroupBy = 'Senior Executive Team' THEN GroupDesc3
                            WHEN @GroupBy = 'Assigned Building' THEN l.BuildingLocName
                            WHEN @GroupBy = 'Swipes by Building' THEN loc.BuildingLocName
                        END AS GroupingClause
                    FROM [Database_Server].[occupancy].[SwipesComplete] sc
                    LEFT JOIN [Database_Server].[occupancy].[DoorsComplete] dc
                    ON (sc.DoorID = dc.DoorID)
                    LEFT JOIN [Join_Database].[site].[LocationInformation] l
                    ON (sc.PersonAssignedBuildingLocID = l.LocID)
                    LEFT JOIN [Join_Database].[site].[LocationInformation] loc 
                    ON (sc.SwipeBuildingLocID = loc.LocID)
                    WHERE sc.SiteID = @SiteID 
                    AND SwipetimeUTC BETWEEN @StartDate AND @EndDate
                    AND (sc.Name IS NULL OR sc.Name NOT LIKE '%Visitor%')
                    AND dc.AssetLocID IS NOT NULL 
                    AND dc.AssetLocID != 5
                    AND sc.SwipetimeEST IS NOT NULL
                    AND (@NeighborhoodName IS NULL OR sc.NeighborhoodName IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@NeighborhoodName)))
                    AND (@SET IS NULL OR sc.GroupDesc3 IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@SET)))
                    AND (@Function IS NULL OR sc.GroupDesc4 IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@Function)))
                    AND (@BusinessUnit IS NULL OR sc.GroupDesc5 IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@BusinessUnit)))
                    AND (@PTFriendlyName IS NULL OR sc.PTFriendlyName IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@PTFriendlyName)))
                    AND (@AssignedLocID IS NULL OR sc.PersonAssignedBuildingLocID IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@AssignedLocID)))
                    AND (@AssignedSite IS NULL OR sc.Site IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@AssignedSite)))
                    AND (@SwipeLocID is NULL OR sc.SwipeBuildingLocID IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@SwipeLocID)))
                )
                SELECT COUNT(DISTINCT(ObjectID)) as count, year, month, GroupingClause AS GroupedBy
                FROM MyCTE
                GROUP BY year, month, GroupingClause
        END

    if @Interval = 'Yearly'
       BEGIN
        WITH MyCTE AS 
            (
                SELECT ObjectID, DATEPART(year, SwipetimeEST) AS year, 
                    CASE 
                        WHEN @GroupBy = 'None' THEN NULL
                        WHEN @GroupBy = 'Personnel Type' THEN PTFriendlyName
                        WHEN @GroupBy = 'Senior Executive Team' THEN GroupDesc3
                        WHEN @GroupBy = 'Assigned Building' THEN l.BuildingLocName
                        WHEN @GroupBy = 'Swipes by Building' THEN loc.BuildingLocName
                    END AS GroupingClause
                FROM [Database_Server].[occupancy].[SwipesComplete] sc
                LEFT JOIN [Database_Server].[occupancy].[DoorsComplete] dc
                ON (sc.DoorID = dc.DoorID)
                LEFT JOIN [Join_Database].[site].[LocationInformation] l
                ON (sc.PersonAssignedBuildingLocID = l.LocID)
                LEFT JOIN [Join_Database].[site].[LocationInformation] loc 
                ON (sc.SwipeBuildingLocID = loc.LocID)
                WHERE sc.SiteID = @SiteID 
                AND SwipetimeUTC BETWEEN @StartDate AND @EndDate
                AND (sc.Name IS NULL OR sc.Name NOT LIKE '%Visitor%')
                AND dc.AssetLocID IS NOT NULL 
                AND dc.AssetLocID != 5
                AND sc.SwipetimeEST IS NOT NULL
                AND (@NeighborhoodName IS NULL OR sc.NeighborhoodName IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@NeighborhoodName)))
                AND (@SET IS NULL OR sc.GroupDesc3 IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@SET)))
                AND (@Function IS NULL OR sc.GroupDesc4 IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@Function)))
                AND (@BusinessUnit IS NULL OR sc.GroupDesc5 IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@BusinessUnit)))
                AND (@PTFriendlyName IS NULL OR sc.PTFriendlyName IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@PTFriendlyName)))
                AND (@AssignedLocID IS NULL OR sc.PersonAssignedBuildingLocID IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@AssignedLocID)))
                AND (@AssignedSite IS NULL OR sc.Site IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@AssignedSite)))
                AND (@SwipeLocID is NULL OR sc.SwipeBuildingLocID IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@SwipeLocID)))
            )
            SELECT COUNT(DISTINCT(ObjectID)) as count, year, GroupingClause AS GroupedBy
            FROM MyCTE
            GROUP BY year, GroupingClause
    END
  END
GO

Estamos tendo alguns problemas importantes de desempenho em que o procedimento armazenado levará mais de um minuto para retornar 12 meses de dados em um intervalo semanal ou tempos de carregamento de 15 a 20 segundos para 1 mês de dados diários.

Aqui estão algumas coisas que tentei:

• Armazenar em cache as partes da data da consulta diretamente na tabela para que não precisem ser calculadas todas as vezes
• Dividir a tabela em duas tabelas, uma para os dados dos últimos 12 meses e outra para todo o resto. Isso ocorre porque supõe-se que a maioria das pessoas analisará os dados apenas dos últimos meses; portanto, se pudermos proporcionar-lhes uma experiência melhor, prefiro fazer isso.

Com essas mudanças estou vendo uma melhora, mas não é suficiente e não sei para onde ir a partir daqui. Para o esquema, adicionei colunas INT de ano, mês, dia, semana, hora e atualizei todos os dados existentes com suas partes de dados correspondentes. E como mencionei, divido os dados em duas tabelas ( SwipesComplete(rolando 12 meses) e SwipesCompleteArchive(> 12 meses))

Aqui está o procedimento armazenado atualizado:

CREATE OR ALTER PROCEDURE [occupancy].[FIDBGetUniqueOccupancyByRange] (
    @StartDate datetime,
    @EndDate datetime,
    @Interval nvarchar(20),
    @PTFriendlyName VARCHAR(4000),
    @SET VARCHAR(4000),
    @Function VARCHAR(4000),
    @BusinessUnit VARCHAR(4000),
    @AssignedSite VARCHAR(4000), -- assigned site
    @AssignedLocID VARCHAR(4000), -- assigned building
    @NeighborhoodName VARCHAR(4000),
    @SwipeLocID VARCHAR(4000),
    @SiteID INT,
    @GroupBy VARCHAR(255)
) 
AS 
BEGIN 
    CREATE TABLE #TempTable (
        ObjectID INT,
        year INT,
        month INT,
        week INT,
        day INT,
        hour INT,
        GroupingClause VARCHAR(4000)
    );

    INSERT INTO #TempTable (ObjectID, year, month, week, day, hour, GroupingClause)
    SELECT
        sc.ObjectID,
        sc.year,
        sc.month,
        sc.week,
        sc.day,
        sc.hour,
        CASE
            WHEN @GroupBy = 'None' THEN NULL
            WHEN @GroupBy = 'Personnel Type' THEN sc.PTFriendlyName
            WHEN @GroupBy = 'Senior Executive Team' THEN sc.GroupDesc3
            WHEN @GroupBy = 'Assigned Building' THEN l.BuildingLocName
            WHEN @GroupBy = 'Swipes by Building' THEN loc.BuildingLocName
        END AS GroupingClause
    FROM
        [Database_Server].[occupancy].[SwipesComplete] sc
        LEFT JOIN [Database_Server].[occupancy].[DoorsComplete] dc ON (sc.DoorID = dc.DoorID)
        LEFT JOIN [Join_Database].[site].[LocationInformation] l ON (sc.PersonAssignedBuildingLocID = l.LocID)
        LEFT JOIN [Join_Database].[site].[LocationInformation] loc ON (sc.SwipeBuildingLocID = loc.LocID)
    WHERE
        sc.SiteID = @SiteID
        AND sc.SwipetimeUTC BETWEEN @StartDate AND @EndDate
        AND (sc.Name IS NULL OR sc.Name NOT LIKE '%Visitor%')
        AND dc.AssetLocID IS NOT NULL AND dc.AssetLocID != 5
        AND sc.SwipetimeEST IS NOT NULL
        AND (@NeighborhoodName IS NULL OR sc.NeighborhoodName IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@NeighborhoodName)))
        AND (@SET IS NULL OR sc.GroupDesc3 IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@SET)))
        AND (@Function IS NULL OR sc.GroupDesc4 IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@Function)))
        AND (@BusinessUnit IS NULL OR sc.GroupDesc5 IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@BusinessUnit)))
        AND (@PTFriendlyName IS NULL OR sc.PTFriendlyName IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@PTFriendlyName)))
        AND (@AssignedLocID IS NULL OR sc.PersonAssignedBuildingLocID IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@AssignedLocID)))
        AND (@AssignedSite IS NULL OR sc.Site IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@AssignedSite)))
        AND (@SwipeLocID IS NULL OR sc.SwipeBuildingLocID IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@SwipeLocID)));

    IF @StartDate <= DATEADD(year, -1, GETDATE())
    BEGIN
        INSERT INTO #TempTable (ObjectID, year, month, week, day, hour, GroupingClause)
        SELECT
            sc.ObjectID,
            sc.year,
            sc.month,
            sc.week,
            sc.day,
            sc.hour,
            CASE
                WHEN @GroupBy = 'None' THEN NULL
                WHEN @GroupBy = 'Personnel Type' THEN sc.PTFriendlyName
                WHEN @GroupBy = 'Senior Executive Team' THEN sc.GroupDesc3
                WHEN @GroupBy = 'Assigned Building' THEN l.BuildingLocName
                WHEN @GroupBy = 'Swipes by Building' THEN loc.BuildingLocName
            END AS GroupingClause
        FROM
            [Database_Server].[occupancy].[SwipesCompleteArchive] sc
            LEFT JOIN [Database_Server].[occupancy].[DoorsComplete] dc ON (sc.DoorID = dc.DoorID)
            LEFT JOIN [Join_Database].[site].[LocationInformation] l ON (sc.PersonAssignedBuildingLocID = l.LocID)
            LEFT JOIN [Join_Database].[site].[LocationInformation] loc ON (sc.SwipeBuildingLocID = loc.LocID)
        WHERE
            sc.SiteID = @SiteID
            AND sc.SwipetimeUTC BETWEEN @StartDate AND @EndDate
            AND (sc.Name IS NULL OR sc.Name NOT LIKE '%Visitor%')
            AND dc.AssetLocID IS NOT NULL AND dc.AssetLocID != 5
            AND sc.SwipetimeEST IS NOT NULL
            AND (@NeighborhoodName IS NULL OR sc.NeighborhoodName IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@NeighborhoodName)))
            AND (@SET IS NULL OR sc.GroupDesc3 IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@SET)))
            AND (@Function IS NULL OR sc.GroupDesc4 IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@Function)))
            AND (@BusinessUnit IS NULL OR sc.GroupDesc5 IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@BusinessUnit)))
            AND (@PTFriendlyName IS NULL OR sc.PTFriendlyName IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@PTFriendlyName)))
            AND (@AssignedLocID IS NULL OR sc.PersonAssignedBuildingLocID IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@AssignedLocID)))
            AND (@AssignedSite IS NULL OR sc.Site IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@AssignedSite)))
            AND (@SwipeLocID IS NULL OR sc.SwipeBuildingLocID IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@SwipeLocID)));
    END

    IF @Interval = 'Hourly' 
    BEGIN
        SELECT 
            COUNT(DISTINCT(ObjectID)) AS count,
            year,
            month,
            week,
            day,
            hour,
            GroupingClause AS GroupedBy
        FROM #TempTable
        GROUP BY year, month, week, day, hour, GroupingClause;
    END
    ELSE IF @Interval = 'Daily' 
    BEGIN
        SELECT 
            COUNT(DISTINCT(ObjectID)) AS count,
            year,
            month,
            week,
            day,
            GroupingClause AS GroupedBy
        FROM #TempTable
        GROUP BY year, month, week, day, GroupingClause;
    END
    ELSE IF @Interval = 'Weekly' 
    BEGIN
        SELECT 
            COUNT(DISTINCT(ObjectID)) AS count,
            year,
            week,
            GroupingClause AS GroupedBy
        FROM #TempTable
        GROUP BY year, week, GroupingClause;
    END
    ELSE IF @Interval = 'Monthly' 
    BEGIN
        SELECT 
            COUNT(DISTINCT(ObjectID)) AS count,
            year,
            month,
            GroupingClause AS GroupedBy
        FROM #TempTable
        GROUP BY year, month, GroupingClause;
    END
    ELSE IF @Interval = 'Yearly' 
    BEGIN
        SELECT 
            COUNT(DISTINCT(ObjectID)) AS count,
            year,
            GroupingClause AS GroupedBy
        FROM #TempTable
        GROUP BY year, GroupingClause;
    END

    DROP TABLE #TempTable;
END

Where do I go from here? In splitting the data into multiple tables actually going to help? Is there a way to query what I want without needing to use a temp table or a CTE? If a user wants to query 12 months of data, it's going to insert ~6 million rows into the temp table if they have no additional filters, which is a ton. I'm at a loss for how to make this performant enough.

I'm willing to change up the schema if I have to, but I'd love to find a way to solve this with some index changes or query changes. At this point anything will help.