Trouble with slow stored procedure performance on 35M rows with CTE/Temp table

Question

We recently went through a new feature release to our app that added about 25 new columns to a large table (~35 million rows), and now we're having some major query performance issues. I assume that it is related to the massive amount of data that was added as part of these new columns, but it could also be index-based, query-based, or something else I'm not thinking of.

This table holds badge swipe information along with information about the person who swiped. It lives in an AWS RDS database, and I have full control over the schema, but not the RDS instance itself. The schema of this table is:

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
);

We have a web-based application that allows users to query this data and display aggregated data in a chart based on a user-selected interval (Hourly, Daily, Weekly, Monthly, Yearly). They can filter by almost all of these columns.

There are indexes on this table (I didn't create these, and they may need to be modified):

• Non-Unique, non-clustered on PrimaryObjectID and ObjectID
• Non-Unique, non-clustered on DoorID and SiteID
• Non-Unique, non-clustered on DoorID
• Non-Unique, non-clustered on SiteID and SwipetimeUTC
• Non-Unique, non-clustered on DoorID
• Non-Unique, non-clustered on ObjectID
• Non-Unique, non-clustered on SwipetimeEST
• Non-Unique, non-clustered on DoorID and SwipetimeUTC
• Non-Unique, non-clustered on SwipetimeUTC

We're querying this data using the following stored procedure. We pass a JSON array for a lot of the WHEREs because users can pick multiple values for many of the filters. Users can also choose to group or not group the data together as you can see in the CASE statement. I know this stored procedure is not great:

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

We're having some pretty major performance issues where the stored procedure will take over a minute to return 12 months of data at a weekly interval, or 15-20 second load times for 1 month of daily data.

Here are a few things I've tried:

• Caching the dateparts of the query on the table directly so they don't need to be calculated each time
• Splitting the table into two tables, one for the latest 12 months of data, and one for everything else. This is because the assumption is most people will only be looking at data for the last few months, so if we can give them a better experience I would rather do that.

With those changes I am seeing an improvement, but it's not enough and I'm not sure where to go from here. For the schema, I added year, month, day, week, hour INT columns and updated all of the existing data with their corresponding dateparts. And as I mentioned, I split the data into two tables (SwipesComplete (rolling 12 months), and SwipesCompleteArchive (> 12 months old))

Here is the updated stored procedure:

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.

Answer 1

This is a classic Kitchen Sink Query. While you could add OPTION (RECOMPILE) as a quick fix, this comes at a cost of recompiling on every run.

Instead, you should build a dynamic query. Don't forget to properly parameterize the dynamic query.

CREATE OR ALTER PROCEDURE [occupancy].[GetUniqueOccupancyByRange]        
(
       @StartDate datetime
       ,@EndDate datetime
       ,@Interval nvarchar(20)
       ,@PTFriendlyName NVARCHAR(MAX)
       ,@SET NVARCHAR(MAX)
       ,@Function NVARCHAR(MAX)
       ,@BusinessUnit NVARCHAR(MAX)
       ,@AssignedSite NVARCHAR(MAX) -- assigned site
       ,@AssignedLocID NVARCHAR(MAX) -- assigned building
       ,@NeighborhoodName NVARCHAR(MAX)
       ,@SwipeLocID NVARCHAR(MAX)
       ,@SiteID INT
       ,@GroupBy VARCHAR(255)
)  
AS  

DECLARE @sql nvarchar(max), @groupByCols nvarchar(max);

SET @groupByCols = CONCAT_WS(N',
',
    N'year',
    CASE WHEN @Interval IN ('Daily', 'Monthly', 'Hourly') THEN N'month' END,
    CASE WHEN @Interval IN ('Daily', 'Weekly', 'Hourly') THEN N'week' END,
    CASE WHEN @Interval IN ('Daily', 'Weekly', 'Monthly', 'Hourly') THEN N'day' END,
    CASE WHEN @Interval IN ('Daily', 'Weekly', 'Monthly', 'Hourly') THEN N'hour' END,
    CASE @GroupBy
        WHEN 'Personnel Type' THEN N'PTFriendlyName'
        WHEN 'Senior Executive Team' THEN N'GroupDesc3'
        WHEN 'Assigned Building' THEN N'l.BuildingLocName'
        WHEN 'Swipes by Building' THEN N'loc.BuildingLocName'
    END
  );

SET @sql = N'
WITH MyCTE AS 
(
    SELECT
      ObjectID,    -- compiler will remove unnecessary columns
      DATEPART(year, sc.SwipetimeEST) AS year,
      DATEPART(month, sc.SwipetimeEST) AS month,
      DATEPART(week, sc.SwipetimeEST) AS week,
      DATEPART(day, sc.SwipetimeEST) AS day,
      DATEPART(hour, sc.SwipetimeEST) AS hour,
      PTFriendlyName,
      GroupDesc3,
      l.BuildingLocName,
      loc.BuildingLocName
    FROM Database_Server.occupancy.SwipesComplete sc
    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
';

IF @NeighborhoodName IS NOT NULL
    SET @sql += N'
      AND sc.NeighborhoodName IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@NeighborhoodName)))';

IF @SET IS NOT NULL
    SET @sql += N'
      AND sc.GroupDesc3 IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@SET))';

IF @Function IS NOT NULL
    SET @sql += N'
      AND sc.GroupDesc4 IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@Function))';

IF @BusinessUnit IS NOT NULL
    SET @sql += N'
      sc.GroupDesc5 IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@BusinessUnit))';

IF @PTFriendlyName IS NOT NULL
    SET @sql += N'
      AND sc.PTFriendlyName IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@PTFriendlyName))';

IF @AssignedLocID IS NOT NULL
    SET @sql += N'
      AND sc.PersonAssignedBuildingLocID IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@AssignedLocID))';

IF @AssignedSite IS NOT NULL
    SET @sql += N'
      AND sc.Site IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@AssignedSite)))';

IF @SwipeLocID IS NOT NULL
    SET @sql += N'
      AND sc.SwipeBuildingLocID IN (SELECT value COLLATE SQL_Latin1_General_CP1_CI_AS FROM OPENJSON(@SwipeLocID))';

SET @sql += N'
)
SELECT
  COUNT(DISTINCT(cte.ObjectID)) as count,
' + @groupByCols + N'
FROM MyCTE cte
GROUP BY
  ' + @groupByCols;


PRINT @sql;    -- your friend

EXEC sp_executesql @sql,

      N'@StartDate datetime
       ,@EndDate datetime
       ,@PTFriendlyName NVARCHAR(MAX)
       ,@SET NVARCHAR(MAX)
       ,@Function NVARCHAR(MAX)
       ,@BusinessUnit NVARCHAR(MAX)
       ,@AssignedSite NVARCHAR(MAX) -- assigned site
       ,@AssignedLocID NVARCHAR(MAX) -- assigned building
       ,@NeighborhoodName NVARCHAR(MAX)
       ,@SwipeLocID NVARCHAR(MAX)
       ,@SiteID INT',

        @StartDate = @StartDate,
        @EndDate = @EndDate,
        @PTFriendlyName = @PTFriendlyName,
        @SET = @SET,
        @Function = @Function,
        @BusinessUnit = @BusinessUnit,
        @AssignedSite = @AssignedSite,
        @AssignedLocID = @AssignedLocID,
        @NeighborhoodName = @NeighborhoodName,
        @SwipeLocID = @SwipeLocID,
        @SiteID = @SiteID;

Further notes:

• The JSONs should be typed as nvarchar(max).
• I strongly suggest you change the JSONs into Table Valued Parameters (with inline primary keys) for better peformance.
• You should also add/remove joins conditionally if possible. I don't know your schema and intended results so I haven't done that, it's also hard to see what's going on when half the columns don't have table aliases.
• Possibly some or all of those joins should actually be EXISTS, and then you don't need COUNT(DISTINCT and it can just be a more efficient COUNT(*).