Quando configuramos o Data Guard, habilitamos o registro forçado. Se configurarmos a replicação manual (via rsync e catalogação dos arquivos no destino + recuperação do banco de dados), precisaremos ativar o log forçado também?
ao tentar impedir que o crescimento automático aconteça, recebi este script legal aqui .
entretanto, se isso acontecer, preciso saber como descobrir. isso posso fazer usando este script muito bom de Max Vernon .
print @@servername + ' - ' + SUBSTRING(@@version,1,COALESCE(CHARINDEX('Copyright',@@version,0)-1,108))
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT OFF
/*
Description: display growth events for all databases on the instance
by: Max Vernon
date: 2014-10-01
*/
DECLARE @Version NVARCHAR(255);
DECLARE @VersionINT INT;
SET @Version = CONVERT(NVARCHAR(255),SERVERPROPERTY('ProductVersion'));
SET @VersionINT = CONVERT(INT, SUBSTRING(@Version,1 ,CHARINDEX('.',@Version)-1));
DECLARE @cmd NVARCHAR(2000);
SET @cmd = '';
IF @VersionINT >= 9
BEGIN
SET @cmd =
'
DECLARE @trcfilename VARCHAR(1000);
SELECT @trcfilename = path
FROM sys.traces WITH(NOLOCK)
WHERE is_default = 1;
IF COALESCE(@trcfilename,'''') <> ''''
BEGIN
SELECT [Radhe]=
'''''''' + @@SERVERNAME + '''''','''''' +
DB_NAME(mf.database_id) + '''''','''''' +
mf.name + '''''','' +
CONVERT(VARCHAR(255), a.NumberOfGrowths) + '','' +
CONVERT(VARCHAR(255), CAST(a.DurationOfGrowthsInSeconds AS decimal(38, 20)))
FROM
(
SELECT
tt.DatabaseID AS database_id,
tt.FileName AS LogicalFileName,
COUNT(*) AS NumberOfGrowths,
SUM(tt.Duration / (1000 * 1000.0)) AS DurationOfGrowthsInSeconds
FROM sys.fn_trace_gettable(@trcfilename, default) tt
WHERE (EventClass IN (92, 93))
GROUP BY
tt.DatabaseID,
tt.FileName
) a
INNER JOIN sys.master_files mf ON
(mf.database_id = a.database_id) AND
(mf.name = a.LogicalFileName);
END
ELSE
BEGIN
SELECT @@SERVERNAME, ''NO TRACE FILE'';
END
';
EXEC sp_executesql @cmd;
END
ELSE
BEGIN
SELECT [SERVER NAME]=@@SERVERNAME, [Product Version]=SERVERPROPERTY('ProductVersion');
END
o que está faltando e eu gostaria de acrescentar é: quando aconteceu o crescimento automático?
como posso descobrir isso?
Nossa empresa possui uma instância RDS de um banco de dados Postgres na AWS. Não tenho muita experiência com AWS.
Gostaria de executar uma consulta muito longa no banco de dados (VACUUM FULL) que pode levar muitas horas ou até dias. Posso me conectar ao banco de dados do meu laptop criando um túnel para a AWS usando uma ferramenta chamada "passaporte".
O problema é que se eu iniciar uma consulta no meu laptop e minha conexão com a Internet cair, ou o laptop reiniciar, ou qualquer outra coisa acontecer na rede entre meu laptop e o computador da Amazon, a consulta será interrompida e perderei qualquer saída do consulta pode gerar.
Nos velhos tempos, quando eu tinha um servidor rodando um sistema operacional com um banco de dados, eu podia me conectar a esse sistema operacional (área de trabalho remota ou ssh ou qualquer outro) e então executar um programa nesse servidor (pgAdmin, psql, etc) que se conectaria ao banco de dados localmente, ao "localhost". Então eu poderia deixá-lo rodando, desconectar do servidor e voltar no dia seguinte, conectar-me novamente e ver os resultados naquela instância do pgAdmin ainda em execução naquele servidor. Nesse caso não havia rede entre a aplicação que estava executando a consulta e o banco de dados.
É possível ter algo assim com o RDS? Como?
Estou usando o PieCloudDB Cloud Edition agora e percebi que o PieCloudDB oferece suporte ao modelo de faturamento pré-pago. Quero saber se ele suporta outros modelos de faturamento. Quero medir o custo de diferentes modelos com base no meu uso. Se for compatível, como posso alterá-lo?
Eu tenho uma tabela com cerca de 1 milhão de linhas que está sendo tentada para ser lida por um programa Java por meio da interface TQL (ou seja, usando apenas a API Java normal, sem SQL). Mas quando tento ler o contêiner, encontro um erro de memória:
Exception in thread "main" com.toshiba.mwcloud.gs.common.GSStatementException: [1043:CM_MEMORY_LIMIT_EXCEEDED] Memory limit exceeded (name=transactionWork.workerStack, requestedSize=67108880, totalSizeLimit=134217728, freeSizeLimit=1048576, totalSize=74448896, freeSize=0) (address=172.18.0.2:10001, partitionId=29)
a solução óbvia é aumentar o storeMemoryLimit e, portanto, aumentei-o do padrão de 1.024 MB para o dobro, em 2.048 MB. Embora eu tenha aumentado o limite de memória, estou recebendo o mesmo erro ao ser lido no programa Java, pois os valores de totalSizeLimit etc permanecem os mesmos, o que significa que aumentar ainda mais o storeMemoryLimit não ajudaria neste caso (embora eu tenha tentado ).
Também mudei o método de compactação de NONE para o novo ZSTD na esperança de que ele pudesse compactar o conjunto de dados pequeno o suficiente para ser lido de uma vez sem atingir o limite de memória, mas isso também falhou.
Minha única outra idéia é mudar de TQL para SQL e aumentar o limite de memória SQL, mas prefiro não mudar todo o meu código.
Alguma idéia de como ler esta tabela inteira de uma só vez?
Eu tenho um pacote dtsx que importa dados de um csv para uma tabela, ele trunca a tabela antes de carregar os dados na tabela.
O problema é que às vezes por um motivo ou outro o pacote falha, por isso termino com uma tabela vazia, gostaria de envolver o fluxo de trabalho dentro de algum tipo de transação e restaurar a tabela em caso de falha.
Existe alguma maneira de conseguir isso?
Preciso ser capaz de iterar todas as tabelas e emitir um TRUNCATEcomando, porém, não quero que o loop seja interrompido por bloqueio. Essencialmente, estou procurando a melhor maneira de determinar se um SCH-Mbloqueio pode ser obtido antes de emitir um comando truncado.
Microsoft SQL Server 2019 (RTM-CU26) - 15.0.4365.2 (X64) Standard Edition (64 bits) no Windows Server 2016 Datacenter 10.0
Existe uma tabela que parece ter atualizações constantes. Há também um procedimento armazenado que é executado periodicamente e itera por tabelas e tenta truncar uma partição de dados usando o comando abaixo:
TRUNCATE TABLE [dbo].[Table] WITH (PARTITIONS (10))
O procedimento de loop sempre fica travado na mesma tabela, a tabela com atualizações constantes falha mesmo quando os dados não estão sendo atualizados na partição destinada à exclusão.
O processo de chamada não pode ser atualizado. Existe um truque para vincular as atualizações a uma partição fora da modificação da consulta ou alguma outra maneira de forçar as partições que não mudam a não serem bloqueadas/bloqueadas?
Um bloqueio de página causará falha no truncamento da partição? Por exemplo, duas partições poderiam compartilhar uma única página e tentar excluir com a partição (2) falha porque algum processo está atualizando dados na partição 1 com a qual 2 está compartilhando uma página?
CREATE TABLE [dbo].[Table]
(
[TableID] INT NOT NULL
...
, [SourceID] NVARCHAR(4000) NOT NULL
, CONSTRAINT [PK_Table_TableID] PRIMARY KEY NONCLUSTERED([TableTableID] ASC , [SourceID] ASC) ON psSourceID(SourceID)
, CONSTRAINT [CIX_Table_TableID] UNIQUE CLUSTERED ([SourceID] ASC, [TableID] ASC) ON psSourceID(SourceID)
)
ON psSourceID(SourceID)
GO
ALTER TABLE [dbo].[Table] SET (LOCK_ESCALATION = AUTO)
GO
O TRUNCATEcomando está sendo bloqueado por uma sessão que executa atualizações na tabela. Em ambientes inferiores, um processo de bloqueio nunca foi atingido após muitas tentativas, portanto, presumia-se que WITH PARTITIONsó colocaria bloqueios de metadados por partição, não por toda a tabela.
MySQL: 8.0.35 - Distribuição de origem
Eu tenho uma coluna de texto longo (DC2Type: array) e alguns valores de caracteres incorretos em uma string de coluna, então quero substituir este formato de dados:
a:2:{s:7:"content";s:6:"text 1";s:3:"url";s:19:"https://someurl.com";}
para este formato de dados:
a:2:{i:0;s:6:"text 1";i:1;s:19:"https://someurl.com";}
então substitua: s:7:"content" por: i:0
e substitua: s:3:"url" por: i:1
como fazer isso ?
cumprimentos
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.