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.
