我们最近发布了一个新功能版本,该版本向一个大型表(约 3500 万行)添加了大约 25 个新列,现在我们遇到了一些重大的查询性能问题。我认为这与作为这些新列的一部分添加的大量数据有关,但它也可能是基于索引、基于查询或其他我没有想到的东西。
该表包含徽章刷卡信息以及刷卡人员的信息。它位于 AWS RDS 数据库中,我可以完全控制架构,但不能控制 RDS 实例本身。该表的架构如下:
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
);
我们有一个基于 Web 的应用程序,允许用户查询这些数据并根据用户选择的时间间隔(每小时、每天、每周、每月、每年)在图表中显示汇总数据。他们几乎可以根据所有这些列进行筛选。
该表上有索引(我没有创建这些,可能需要修改):
- PrimaryObjectID 和 ObjectID 非唯一、非聚集
- DoorID 和 SiteID 非唯一、非聚集
- DoorID 非唯一、非聚集
- SiteID 和 SwipetimeUTC 非唯一、非聚集
- DoorID 非唯一、非聚集
- ObjectID 非唯一、非聚类
- 在 SwipetimeEST 上不唯一、不聚集
- DoorID 和 SwipetimeUTC 不唯一、不聚集
- SwipetimeUTC 上不唯一、不聚集
我们使用以下存储过程查询这些数据。我们为许多 WHERE 传递一个 JSON 数组,因为用户可以为许多过滤器选择多个值。用户还可以选择将数据分组或不分组,如您在 CASE 语句中看到的那样。我知道这个存储过程不太好:
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
我们遇到了一些相当严重的性能问题,存储过程需要一分钟以上的时间才能以每周的间隔返回 12 个月的数据,或者需要 15-20 秒的加载时间才能返回 1 个月的每日数据。
以下是我尝试过的一些方法:
- 直接在表中缓存查询的日期部分,这样就不需要每次都进行计算
- 将表格拆分成两个表格,一个用于显示最近 12 个月的数据,另一个用于显示其他所有数据。这是因为我们假设大多数人只会查看最近几个月的数据,因此如果我们可以为他们提供更好的体验,我宁愿这样做。
通过这些更改,我看到了改进,但这还不够,我不确定下一步该怎么做。对于架构,我添加了年、月、日、周、小时 INT 列,并使用相应的日期部分更新了所有现有数据。正如我提到的,我将数据拆分为两个表(SwipesComplete
(滚动 12 个月)和SwipesCompleteArchive
(> 12 个月前))
以下是更新后的存储过程:
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.
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.
Further notes:
nvarchar(max)
.EXISTS
, and then you don't needCOUNT(DISTINCT
and it can just be a more efficientCOUNT(*)
.