Click here to Skip to main content
14,268,557 members
Rate this:
Please Sign up or sign in to vote.
See more:
How would you index this?

SELECT DeviceName, MAX(SentAt) 
FROM Telemetry
Group by DeviceName


DeviceName varchar(50) NOT NULL,
SentAt DateTime NOT NULL


The goal is to get the last row for each DeviceName.


TIA...

What I have tried:

My current index is:
CREATE NONCLUSTERED INDEX [IX_UNIQUE_LAST_ROWS] ON [dbo].[Telemetry]
(
[DeviceName] ASC
,[SentAt] DESC
)


This gives me an Index Scan instead of an Index Seek. This takes 10 seconds on a 228K rows table.

The result is the 10 unique DeviceNames, with their last update time.
Posted
Updated 6-Aug-19 7:09am
Rate this:
Please Sign up or sign in to vote.

Solution 2

Index(es) really are not going to help you with this aggregate function; however, there are alternatives if you can add columns, tables, or procedures. The increases offered are going to be determined by the quantity of devices and the percentage of rows they contribute.
To make things better... we have a couple of options...

Let's say we went on a normalization approach and created a TelemetryDevice table to be a unique listing of the devices and some simple information about it
CREATE TABLE TelemetryDevice (
	DeviceID INT IDENTITY(1,1) NOT NULL,
	DeviceName NVARCHAR(100) NOT NULL,
	LastContact DATETIME NULL,
	-- other "device property" fields
	CONSTRAINT PK_TelemetryDevice_DeviceName PRIMARY KEY CLUSTERED ([DeviceID] ASC) ON [PRIMARY]
)  ON [PRIMARY]
GO
We could then Create/Alter a Stored Procedure which populatates the Telemetry log table AND updates the Device table
CREATE PROCEDURE Telemetry_Log_IncomingCommunication (
	@DeviceName VARCHAR(50)
--,	Whatever data values you have
) AS 
BEGIN
	DECLARE @Now DATETIME = GetDate()

	INSERT Telemetry (DeviceName, SentAt /* data columns */)
	VALUES (@DeviceName, @Now /* data values */)

	UPDATE TelemetryDevice
	SET    LastContact = @Now
	WHERE  DeviceName = @DeviceName
END
GO
To make this truly normalized, your existing table would be modified to be the actual DeviceID; and the Stored Procedure would be adjusted accordingly.

Your new query would be
SELECT DeviceName, LastContact
FROM Telemetry
-- GROUP BY not needed
-- ORDER BY optional
As the DeviceName is a Primary Key, you would not need to add an index

The other approach would be to add a column to your existing table
ALTER TABLE Telemetry
ADD LastMessage BIT NULL DEFAULT (0)
GO
And again a corresponding Stored Procedure for populating it
ALTER PROCEDURE Telemetry_Log_IncomingCommunication (
	@DeviceName VARCHAR(50)
--,	Whatever values you are passing in     
) AS 
BEGIN
	DECLARE @Now DATETIME = GetDate()

	UPDATE Telemetry
	SET LastMessage = 0
	WHERE DeviceName = @DeviceName

	INSERT Telemetry (DeviceName, SentAt, LastMessage /* data columns */)
	VALUES (@DeviceName, @Now, 1 /* data values */)
END
GO

Your new query would then be
SELECT DeviceName, SentAt
FROM Telemetry
WHERE LastMessage = 1
-- GROUP BY not needed
-- ORDER BY optional
Which would benefit IF you had indexes on DeviceName by itself and a Unique Index of DeviceName and the LastMessage column.
   
Comments
Richard Deeming 8-Aug-19 11:46am
   
For option 2, I'd be inclined to use a filtered index[^]. :)
MadMyche 8-Aug-19 12:50pm
   
True, and it could even be defined as a Unique Filtered Index
Rate this:
Please Sign up or sign in to vote.

Solution 1

Try a SQL TOP 1 with a WHERE clause for the devices; either explicit device names or via a DISTINCT subquery; e.g.

SELECT TOP(1) Model, Color, Price  
FROM dbo.Cars  
WHERE Color = 'red'  
UNION ALL  
SELECT TOP(1) Model, Color, Price  
FROM dbo.Cars  
WHERE Color = 'blue'  
ORDER BY Price ASC;  
GO


TOP (Transact-SQL) - SQL Server | Microsoft Docs[^]
   
Comments
me@dagsunde.com 6-Aug-19 7:51am
   
That would be nice, if there was a fixed, well known number of Colors (or in my case, DeviceNames)...
But I don't know the number of DeviceNames at any given point, nor do I know their names.

I the last row entered for each deviceName in the database, one row for each existing DeviceName.
Gerry Schmitz 6-Aug-19 11:55am
   
I thought I said to "otherwise" run a DISTINCT subquery for the device names first.

https://www.w3schools.com/sql/sql_distinct.asp

You then select top WHERE IN ...

Or do it the long way.

(And you did mention some result of "10 devices"; so your "domain" is not that large. Otherwise, what was the point of that remark? And "devices" just don't happen to come along by themselves).
Rate this:
Please Sign up or sign in to vote.

Solution 3

Not tested, but try a CTE:
WITH devices AS (
    SELECT  DISTINCT Devicename
    FROM     Telemetry
    )
SELECT  Devicename
        Max(Sentat)
FROM    Telemetry
WHERE   Devicename IN (Select devicename from devices)
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month