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.
OPTION (RECOMPILE)hint to theINSERT...SELECT"kitchen sink" query.