Setup
I have a huge table of ~115,382,254 rows. The table is relatively simple and logs application process operations.
CREATE TABLE [data].[OperationData](
[SourceDeciveID] [bigint] NOT NULL,
[FileSource] [nvarchar](256) NOT NULL,
[Size] [bigint] NULL,
[Begin] [datetime2](7) NULL,
[End] [datetime2](7) NOT NULL,
[Date] AS (isnull(CONVERT([date],[End]),CONVERT([date],'19000101',(112)))) PERSISTED NOT NULL,
[DataSetCount] [bigint] NULL,
[Result] [int] NULL,
[Error] [nvarchar](max) NULL,
[Status] [int] NULL,
CONSTRAINT [PK_OperationData] PRIMARY KEY CLUSTERED
(
[SourceDeviceID] ASC,
[FileSource] ASC,
[End] ASC
))
CREATE TABLE [model].[SourceDevice](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NULL,
CONSTRAINT [PK_DataLogger] PRIMARY KEY CLUSTERED
(
[ID] ASC
))
ALTER TABLE [data].[OperationData] WITH CHECK ADD CONSTRAINT [FK_OperationData_SourceDevice] FOREIGN KEY([SourceDeviceID])
REFERENCES [model].[SourceDevice] ([ID])
The table is clustered at around 500 clusters and on a daily base.
Also, the table is well indexed by PK, statistics are up-to-date and the INDEXer get defraged every night.
Index based SELECTs are lightning fast and we had no problem with it.
Problem
I need to know the last (TOP) row by [End]
and partitioned by [SourceDeciveID]
. To get the very last [OperationData]
of every source device.
Question
I need to find a way to solve this in a good way and without bringing the DB to the limits.
Effort 1
The first try was obvious GROUP BY
or SELECT OVER PARTITION BY
query. The problem here is also obvious, every query has to scan over very partition order/find the top row. So the query is very slow and has a very high IO impact.
Example query 1
;WITH cte AS
(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY [SourceDeciveID] ORDER BY [End] DESC) AS rn
FROM [data].[OperationData]
)
SELECT *
FROM cte
WHERE rn = 1
Example query 2
SELECT *
FROM [data].[OperationData] AS d
CROSS APPLY
(
SELECT TOP 1 *
FROM [data].[OperationData]
WHERE [SourceDeciveID] = d.[SourceDeciveID]
ORDER BY [End] DESC
) AS ds
FAILED!
Effort 2
I created a help table to always hold a reference to TOP row.
CREATE TABLE [data].[LastOperationData](
[SourceDeciveID] [bigint] NOT NULL,
[FileSource] [nvarchar](256) NOT NULL,
[End] [datetime2](7) NOT NULL,
CONSTRAINT [PK_LastOperationData] PRIMARY KEY CLUSTERED
(
[SourceDeciveID] ASC
)
ALTER TABLE [data].[LastOperationData] WITH CHECK ADD CONSTRAINT [FK_LastOperationData_OperationData] FOREIGN KEY([SourceDeciveID], [FileSource], [End])
REFERENCES [data].[OperationData] ([SourceDeciveID], [FileSource], [End])
To fill up the table a created a trigger to always add/update the source row if higher [End]
column is inserted.
CREATE TRIGGER [data].[OperationData_Last]
ON [data].[OperationData]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
MERGE [data].[LastOperationData] AS [target]
USING (SELECT [SourceDeciveID], [FileSource], [End] FROM inserted) AS [source] ([SourceDeciveID], [FileSource], [End])
ON ([target].[SourceDeciveID] = [FileSource].[SourceDeciveID])
WHEN MATCHED AND [target].[End] < [source].[End] THEN
UPDATE SET [target].[FileSource] = source.[FileSource], [target].[End] = source.[End]
WHEN NOT MATCHED THEN
INSERT ([SourceDeciveID], [FileSource], [End])
VALUES (source.[SourceDeciveID], source.[FileSource], source.[End]);
END
The problem here is, that it also has a very huge IO impact and I don't know why.
As you can see here in the query plan it also executes a scan over the whole [OperationData]
table.
It has a huge overall impact on my DB.
FAILED!
CREATE TABLE
script but inside the query plan you will see the partitions. I will edit the question.PRIMARY KEY CLUSTERED
you think it may help?SELECT [SourceID], [Source], [End] FROM inserted
some how do a table scan on the[OperationData]
.