How would you index this?

Question

1.00/5 (1 vote)

See more:

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 5-Aug-19 3:39am

me@dagsunde.com

Updated 6-Aug-19 7:09am

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

MadMyche · Answer 1 · 2019-08-06T05:29:00

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

SQL

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

SQL

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

SQL

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

SQL

ALTER TABLE Telemetry
ADD LastMessage BIT NULL DEFAULT (0)
GO

And again a corresponding Stored Procedure for populating it

SQL

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

SQL

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.

Gerry Schmitz · Answer 2 · 2019-08-05T09:55:00

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[^]

Posted 5-Aug-19 9:55am

Gerry Schmitz

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.

[no name] 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).

Jörgen Andersson · Answer 3 · 2019-08-06T07:09:00

Solution 3

Not tested, but try a CTE:

SQL

WITH devices AS (
    SELECT  DISTINCT Devicename
    FROM     Telemetry
    )
SELECT  Devicename
        Max(Sentat)
FROM    Telemetry
WHERE   Devicename IN (Select devicename from devices)

Posted 6-Aug-19 7:09am

Jörgen Andersson

How would you index this?

3 solutions

Solution 2

Solution 1

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us