Skip to content

Instantly share code, notes, and snippets.

@IDisposable

IDisposable/PerformanceHack.sql

Last active August 3, 2023 20:06
Show Gist options
  • Save IDisposable/c81197333f57687322a8b2726280370d to your computer and use it in GitHub Desktop.
Save IDisposable/c81197333f57687322a8b2726280370d to your computer and use it in GitHub Desktop.
Download ZIP
Sometimes you have to break out the sledge hammer...
Raw
PerformanceHack.sql
-- Creates the event reporting rollup tables and configures the triggers
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[EventAccumHourly](
[SliceDate] DATETIME NOT NULL CONSTRAINT [CK_EventAccumHourly_SliceDate] CHECK ([SliceDate]=DATEADD(HOUR,DATEDIFF(HOUR,0,[SliceDate]),0))
,[ClientId] BIGINT NOT NULL
,[SiteId] BIGINT NOT NULL
,[AlertId] BIGINT NOT NULL
,[Events_1] INT NOT NULL
,[Events_2] INT NOT NULL
,[Events_3] INT NOT NULL
,[Events_4] INT NOT NULL
,[Events_5] INT NOT NULL
,[Events_6] INT NOT NULL
,[Events_7] INT NOT NULL
,[Events_8] INT NOT NULL
,[Events_9] INT NOT NULL
CONSTRAINT [PK_EventAccumHourly] PRIMARY KEY CLUSTERED
(
[SliceDate] ASC
,[ClientId] ASC
,[SiteId] ASC
,[AlertId] ASC
)
)
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_EventAccumHourly_Client] ON [dbo].[EventAccumHourly]
(
[ClientId] ASC
,[SiteId] ASC
,[AlertId] ASC
,[SliceDate] ASC
)
INCLUDE ([Events_1],[Events_2],[Events_3],[Events_4],[Events_5],[Events_6],[Events_7],[Events_8],[Events_9])
GO
CREATE TABLE [dbo].[EventAccumDaily](
[SliceDate] DATE NOT NULL CONSTRAINT [CK_EventAccumDaily_SliceDate] CHECK ([SliceDate]=DATEADD(DAY,DATEDIFF(DAY,0,[SliceDate]),0))
,[ClientId] BIGINT NOT NULL
,[SiteId] BIGINT NOT NULL
,[AlertId] BIGINT NOT NULL
,[Events_1] INT NOT NULL
,[Events_2] INT NOT NULL
,[Events_3] INT NOT NULL
,[Events_4] INT NOT NULL
,[Events_5] INT NOT NULL
,[Events_6] INT NOT NULL
,[Events_7] INT NOT NULL
,[Events_8] INT NOT NULL
,[Events_9] INT NOT NULL
CONSTRAINT [PK_EventAccumDaily] PRIMARY KEY CLUSTERED
(
[SliceDate] ASC
,[ClientId] ASC
,[SiteId] ASC
,[AlertId] ASC
)
)
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_EventAccumDaily_Client] ON [dbo].[EventAccumDaily]
(
[ClientId] ASC
,[SiteId] ASC
,[AlertId] ASC
,[SliceDate] ASC
)
INCLUDE ([Events_1],[Events_2],[Events_3],[Events_4],[Events_5],[Events_6],[Events_7],[Events_8],[Events_9])
GO
CREATE TABLE [dbo].[EventAccumMonthly](
[SliceDate] DATE NOT NULL CONSTRAINT [CK_EventAccumMonthly_SliceDate] CHECK ([SliceDate]=DATEADD(MONTH,DATEDIFF(MONTH,0,[SliceDate]),0))
,[ClientId] BIGINT NOT NULL
,[SiteId] BIGINT NOT NULL
,[AlertId] BIGINT NOT NULL
,[Events_1] BIGINT NOT NULL
,[Events_2] BIGINT NOT NULL
,[Events_3] BIGINT NOT NULL
,[Events_4] BIGINT NOT NULL
,[Events_5] BIGINT NOT NULL
,[Events_6] BIGINT NOT NULL
,[Events_7] BIGINT NOT NULL
,[Events_8] BIGINT NOT NULL
,[Events_9] BIGINT NOT NULL
CONSTRAINT [PK_EventAccumMonthly] PRIMARY KEY CLUSTERED
(
[SliceDate] ASC
,[ClientId] ASC
,[SiteId] ASC
,[AlertId] ASC
)
)
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_EventAccumMonthly_Client] ON [dbo].[EventAccumMonthly]
(
[ClientId] ASC
,[SiteId] ASC
,[AlertId] ASC
,[SliceDate] ASC
)
INCLUDE ([Events_1],[Events_2],[Events_3],[Events_4],[Events_5],[Events_6],[Events_7],[Events_8],[Events_9])
GO
CREATE TABLE [dbo].[EventAccumYearly](
[SliceDate] DATE NOT NULL CONSTRAINT [CK_EventAccumYearly_SliceDate] CHECK ([SliceDate]=DATEADD(YEAR,DATEDIFF(YEAR,0,[SliceDate]),0))
,[ClientId] BIGINT NOT NULL
,[SiteId] BIGINT NOT NULL
,[AlertId] BIGINT NOT NULL
,[Events_1] BIGINT NOT NULL
,[Events_2] BIGINT NOT NULL
,[Events_3] BIGINT NOT NULL
,[Events_4] BIGINT NOT NULL
,[Events_5] BIGINT NOT NULL
,[Events_6] BIGINT NOT NULL
,[Events_7] BIGINT NOT NULL
,[Events_8] BIGINT NOT NULL
,[Events_9] BIGINT NOT NULL
CONSTRAINT [PK_EventAccumYearly] PRIMARY KEY CLUSTERED
(
[SliceDate] ASC
,[ClientId] ASC
,[SiteId] ASC
,[AlertId] ASC
)
)
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_EventAccumYearly_Client] ON [dbo].[EventAccumYearly]
(
[ClientId] ASC
,[SiteId] ASC
,[AlertId] ASC
,[SliceDate] ASC
)
INCLUDE ([Events_1],[Events_2],[Events_3],[Events_4],[Events_5],[Events_6],[Events_7],[Events_8],[Events_9])
GO
CREATE TRIGGER [dbo].[TRG_EventAccumHourly]
ON [dbo].[EventAccumHourly]
AFTER INSERT, DELETE, UPDATE
AS BEGIN
SET NOCOUNT ON;
DECLARE @changes TABLE(
[SliceDate] DATE NOT NULL
,[SliceMonth] AS DATEADD(MONTH, DATEDIFF(MONTH, 0, [SliceDate]), 0)
,[SliceYear] AS DATEADD(YEAR, DATEDIFF(YEAR, 0, [SliceDate]), 0)
,[ClientId] BIGINT NOT NULL
,[SiteId] BIGINT NOT NULL
,[AlertId] BIGINT NOT NULL
,[Events_1] INT NOT NULL
,[Events_2] INT NOT NULL
,[Events_3] INT NOT NULL
,[Events_4] INT NOT NULL
,[Events_5] INT NOT NULL
,[Events_6] INT NOT NULL
,[Events_7] INT NOT NULL
,[Events_8] INT NOT NULL
,[Events_9] INT NOT NULL
, PRIMARY KEY CLUSTERED([SliceDate],[ClientId],[SiteId],[AlertId]))
;WITH Hourlys AS
(
SELECT [SliceDate]=DATEADD(DAY, DATEDIFF(DAY, 0, I.[SliceDate]), 0)
, [ClientId]
, [SiteId]
, [AlertId]
, [Events_1]
, [Events_2]
, [Events_3]
, [Events_4]
, [Events_5]
, [Events_6]
, [Events_7]
, [Events_8]
, [Events_9]
FROM INSERTED AS I
UNION ALL
SELECT [SliceDate]=DATEADD(DAY, DATEDIFF(DAY, 0, D.[SliceDate]), 0)
, [ClientId]
, [SiteId]
, [AlertId]
, [Events_1]=-D.[Events_1]
, [Events_2]=-D.[Events_2]
, [Events_3]=-D.[Events_3]
, [Events_4]=-D.[Events_4]
, [Events_5]=-D.[Events_5]
, [Events_6]=-D.[Events_6]
, [Events_7]=-D.[Events_7]
, [Events_8]=-D.[Events_8]
, [Events_9]=-D.[Events_9]
FROM DELETED AS D
)
, SummedHourlys AS
(
SELECT [SliceDate]
, [ClientId]
, [SiteId]
, [AlertId]
, [Events_1]=SUM([Events_1])
, [Events_2]=SUM([Events_2])
, [Events_3]=SUM([Events_3])
, [Events_4]=SUM([Events_4])
, [Events_5]=SUM([Events_5])
, [Events_6]=SUM([Events_6])
, [Events_7]=SUM([Events_7])
, [Events_8]=SUM([Events_8])
, [Events_9]=SUM([Events_9])
FROM Hourlys
GROUP BY [SliceDate], [ClientId], [SiteId], [AlertId]
)
INSERT INTO @changes([SliceDate],[ClientId],[SiteId],[AlertId],[Events_1],[Events_2],[Events_3],[Events_4],[Events_5],[Events_6],[Events_7],[Events_8],[Events_9])
SELECT [SliceDate],[ClientId],[SiteId],[AlertId],[Events_1],[Events_2],[Events_3],[Events_4],[Events_5],[Events_6],[Events_7],[Events_8],[Events_9]
FROM SummedHourlys
WHERE ([Events_1] <> 0 OR [Events_2] <> 0 OR [Events_3] <> 0 OR [Events_4] <> 0 OR [Events_5] <> 0 OR [Events_6] <> 0 OR [Events_7] <> 0 OR [Events_8] <> 0 OR [Events_9] <> 0)
IF NOT EXISTS (SELECT * FROM @changes) RETURN
MERGE [dbo].[EventAccumDaily] AS t
USING (SELECT [SliceDate],[ClientId],[SiteId],[AlertId],[Events_1],[Events_2],[Events_3],[Events_4],[Events_5],[Events_6],[Events_7],[Events_8],[Events_9]
FROM @changes
) AS s([SliceDate],[ClientId],[SiteId],[AlertId],[Events_1],[Events_2],[Events_3],[Events_4],[Events_5],[Events_6],[Events_7],[Events_8],[Events_9])
ON t.[SliceDate]=s.[SliceDate] AND t.[ClientId]=s.[ClientId] AND t.[SiteId]=s.[SiteId] AND t.[AlertId]=s.[AlertId]
WHEN NOT MATCHED BY TARGET --AND s.[SliceDate] >= DATEADD(DAY, -60, DATEADD(DAY,DATEDIFF(DAY,0,GETUTCDATE()),0))
THEN INSERT([SliceDate],[ClientId],[SiteId],[AlertId],[Events_1],[Events_2],[Events_3],[Events_4],[Events_5],[Events_6],[Events_7],[Events_8],[Events_9])
VALUES(s.[SliceDate],s.[ClientId],s.[SiteId],s.[AlertId],s.[Events_1],s.[Events_2],s.[Events_3],s.[Events_4],s.[Events_5],s.[Events_6],s.[Events_7],s.[Events_8],s.[Events_9])
WHEN MATCHED THEN
UPDATE SET
t.[Events_1] += s.[Events_1]
, t.[Events_2] += s.[Events_2]
, t.[Events_3] += s.[Events_3]
, t.[Events_4] += s.[Events_4]
, t.[Events_5] += s.[Events_5]
, t.[Events_6] += s.[Events_6]
, t.[Events_7] += s.[Events_7]
, t.[Events_8] += s.[Events_8]
, t.[Events_9] += s.[Events_9]
;
;MERGE [dbo].[EventAccumMonthly] AS t
USING (SELECT
[SliceDate]=[SliceMonth]
, [ClientId]
, [SiteId]
, [AlertId]
, [Events_1]=SUM(CAST([Events_1] AS BIGINT))
, [Events_2]=SUM(CAST([Events_2] AS BIGINT))
, [Events_3]=SUM(CAST([Events_3] AS BIGINT))
, [Events_4]=SUM(CAST([Events_4] AS BIGINT))
, [Events_5]=SUM(CAST([Events_5] AS BIGINT))
, [Events_6]=SUM(CAST([Events_6] AS BIGINT))
, [Events_7]=SUM(CAST([Events_7] AS BIGINT))
, [Events_8]=SUM(CAST([Events_8] AS BIGINT))
, [Events_9]=SUM(CAST([Events_9] AS BIGINT))
FROM @changes
GROUP BY [SliceMonth], [ClientId], [SiteId], [AlertId]
) AS s([SliceDate],[ClientId],[SiteId],[AlertId],[Events_1],[Events_2],[Events_3],[Events_4],[Events_5],[Events_6],[Events_7],[Events_8],[Events_9])
ON t.[SliceDate]=s.[SliceDate] AND t.[ClientId]=s.[ClientId] AND t.[SiteId]=s.[SiteId] AND t.[AlertId]=s.[AlertId]
WHEN NOT MATCHED BY TARGET --AND s.SliceDate >= DATEADD(MONTH, -24, DATEADD(MONTH,DATEDIFF(MONTH,0,GETUTCDATE()),0))
THEN INSERT([SliceDate],[ClientId],[SiteId],[AlertId],[Events_1],[Events_2],[Events_3],[Events_4],[Events_5],[Events_6],[Events_7],[Events_8],[Events_9])
VALUES(s.[SliceDate],s.[ClientId],s.[SiteId],s.[AlertId],s.[Events_1],s.[Events_2],s.[Events_3],s.[Events_4],s.[Events_5],s.[Events_6],s.[Events_7],s.[Events_8],s.[Events_9])
WHEN MATCHED THEN
UPDATE SET
t.[Events_1] += s.[Events_1]
, t.[Events_2] += s.[Events_2]
, t.[Events_3] += s.[Events_3]
, t.[Events_4] += s.[Events_4]
, t.[Events_5] += s.[Events_5]
, t.[Events_6] += s.[Events_6]
, t.[Events_7] += s.[Events_7]
, t.[Events_8] += s.[Events_8]
, t.[Events_9] += s.[Events_9]
;
;MERGE [dbo].[EventAccumYearly] AS t
USING (SELECT
[SliceDate]=[SliceYear]
, [ClientId]
, [SiteId]
, [AlertId]
, [Events_1]=SUM(CAST([Events_1] AS BIGINT))
, [Events_2]=SUM(CAST([Events_2] AS BIGINT))
, [Events_3]=SUM(CAST([Events_3] AS BIGINT))
, [Events_4]=SUM(CAST([Events_4] AS BIGINT))
, [Events_5]=SUM(CAST([Events_5] AS BIGINT))
, [Events_6]=SUM(CAST([Events_6] AS BIGINT))
, [Events_7]=SUM(CAST([Events_7] AS BIGINT))
, [Events_8]=SUM(CAST([Events_8] AS BIGINT))
, [Events_9]=SUM(CAST([Events_9] AS BIGINT))
FROM @changes
GROUP BY [SliceYear], [ClientId], [SiteId], [AlertId]
) AS s([SliceDate],[ClientId],[SiteId],[AlertId],[Events_1],[Events_2],[Events_3],[Events_4],[Events_5],[Events_6],[Events_7],[Events_8],[Events_9])
ON t.[SliceDate]=s.[SliceDate] AND t.[ClientId]=s.[ClientId] AND t.[SiteId]=s.[SiteId] AND t.[AlertId]=s.[AlertId]
WHEN NOT MATCHED BY TARGET --AND s.SliceDate >= DATEADD(MONTH, -24, DATEADD(MONTH,DATEDIFF(MONTH,0,GETUTCDATE()),0))
THEN INSERT([SliceDate],[ClientId],[SiteId],[AlertId],[Events_1],[Events_2],[Events_3],[Events_4],[Events_5],[Events_6],[Events_7],[Events_8],[Events_9])
VALUES(s.[SliceDate],s.[ClientId],s.[SiteId],s.[AlertId],s.[Events_1],s.[Events_2],s.[Events_3],s.[Events_4],s.[Events_5],s.[Events_6],s.[Events_7],s.[Events_8],s.[Events_9])
WHEN MATCHED THEN
UPDATE SET
t.[Events_1] += s.[Events_1]
, t.[Events_2] += s.[Events_2]
, t.[Events_3] += s.[Events_3]
, t.[Events_4] += s.[Events_4]
, t.[Events_5] += s.[Events_5]
, t.[Events_6] += s.[Events_6]
, t.[Events_7] += s.[Events_7]
, t.[Events_8] += s.[Events_8]
, t.[Events_9] += s.[Events_9]
;
END
GO
CREATE TRIGGER [dbo].[TRG_ClientEvent]
ON [dbo].[ClientEvent]
AFTER INSERT,DELETE,UPDATE
AS
BEGIN
SET NOCOUNT ON;
-- if no work to be done, bail out early
IF NOT EXISTS (SELECT * FROM INSERTED) AND NOT EXISTS (SELECT * FROM DELETED) RETURN
-- now update all the hourly reporting slots based on the newly inserted rows.
; WITH deltas([ClientId],[SiteId],[AlertId],[Event],[SliceDate],[Delta]) AS
(SELECT I.[ClientId],I.[SiteId],[AlertId]=COALESCE(I.[AlertID],0),I.[Event],[SliceDate]=DATEADD(HOUR,DATEDIFF(HOUR,0,I.[TimestampUtc]),0),[Delta]=1
FROM INSERTED AS I
UNION ALL
SELECT D.[ClientId],D.[SiteId],[AlertId]=COALESCE(D.[AlertID],0),D.[Event],[SliceDate]=DATEADD(HOUR,DATEDIFF(HOUR,0,D.[TimestampUtc]),0),[Delta]=-1
FROM DELETED AS D)
, merged ([SliceDate],[ClientId],[SiteId],[AlertId],[Events_1],[Events_2],[Events_3],[Events_4],[Events_5],[Events_6],[Events_7],[Events_8],[Events_9]) AS
(SELECT
D.[SliceDate]
,D.[ClientId]
,D.[SiteId]
,D.[AlertId]
,[Events_1]=SUM(CASE WHEN [Event]=1 THEN D.[Delta] ELSE 0 END)
,[Events_2]=SUM(CASE WHEN [Event]=2 THEN D.[Delta] ELSE 0 END)
,[Events_3]=SUM(CASE WHEN [Event]=3 THEN D.[Delta] ELSE 0 END)
,[Events_4]=SUM(CASE WHEN [Event]=4 THEN D.[Delta] ELSE 0 END)
,[Events_5]=SUM(CASE WHEN [Event]=5 THEN D.[Delta] ELSE 0 END)
,[Events_6]=SUM(CASE WHEN [Event]=6 THEN D.[Delta] ELSE 0 END)
,[Events_7]=SUM(CASE WHEN [Event]=7 THEN D.[Delta] ELSE 0 END)
,[Events_8]=SUM(CASE WHEN [Event]=8 THEN D.[Delta] ELSE 0 END)
,[Events_9]=SUM(CASE WHEN [Event]=9 THEN D.[Delta] ELSE 0 END)
FROM deltas AS D
WHERE [Event] IS NOT NULL
GROUP BY [SliceDate],[ClientId],[SiteId],[AlertId])
MERGE [dbo].[EventAccumHourly] AS t
USING (SELECT [SliceDate],[ClientId],[SiteId],[AlertId],[Events_1],[Events_2],[Events_3],[Events_4],[Events_5],[Events_6],[Events_7],[Events_8],[Events_9]
FROM merged
WHERE ([Events_1] <> 0 OR [Events_2] <> 0 OR [Events_3] <> 0 OR [Events_4] <> 0 OR [Events_5] <> 0 OR [Events_6] <> 0 OR [Events_7] <> 0 OR [Events_8] <> 0 OR [Events_9] <> 0)
) AS s([SliceDate],[ClientId],[SiteId],[AlertId],[Events_1],[Events_2],[Events_3],[Events_4],[Events_5],[Events_6],[Events_7],[Events_8],[Events_9])
ON t.[SliceDate]=s.[SliceDate]
AND t.[ClientId]=s.[ClientId]
AND t.[SiteId]=s.[SiteId]
AND t.[AlertId]=s.[AlertId]
WHEN NOT MATCHED BY TARGET THEN
INSERT([SliceDate],[ClientId],[SiteId],[AlertId],[Events_1],[Events_2],[Events_3],[Events_4],[Events_5],[Events_6],[Events_7],[Events_8],[Events_9])
VALUES(s.[SliceDate],s.[ClientId],s.[SiteId],s.[AlertId],s.[Events_1],s.[Events_2],s.[Events_3],s.[Events_4],s.[Events_5],s.[Events_6],s.[Events_7],s.[Events_8],s.[Events_9])
WHEN MATCHED THEN
UPDATE SET
t.[Events_1] += s.[Events_1]
, t.[Events_2] += s.[Events_2]
, t.[Events_3] += s.[Events_3]
, t.[Events_4] += s.[Events_4]
, t.[Events_5] += s.[Events_5]
, t.[Events_6] += s.[Events_6]
, t.[Events_7] += s.[Events_7]
, t.[Events_8] += s.[Events_8]
, t.[Events_9] += s.[Events_9]
;
END
GO
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment