me@dagsunde.com Ответов: 3

Как бы вы это проиндексировали?

Как бы вы это проиндексировали?

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


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


Цель состоит в том, чтобы получить последнюю строку для каждого имени устройства.


ТИА...

Что я уже пробовал:

Мой текущий индекс:
CREATE NONCLUSTERED INDEX [IX_UNIQUE_LAST_ROWS] ON [dbo].[Telemetry]
(
[DeviceName] ASC
,[SentAt] DESC
)


Это дает мне индексное сканирование вместо индексного поиска. Это занимает 10 секунд на 228K строк таблицы.

В результате получается 10 уникальных имен устройств с указанием времени их последнего обновления.

3 Ответов

Рейтинг:
1

Jörgen Andersson

Не проверено, но попробуйте CTE:

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

Рейтинг:
0

MadMyche

Индексы действительно не помогут вам с этой агрегатной функцией; однако есть альтернативы, если вы можете добавить столбцы, таблицы или процедуры. Предлагаемые увеличения будут определяться количеством устройств и процентом строк, которые они вносят.
Чтобы все стало лучше... у нас есть несколько вариантов...

Допустим, мы пошли по пути нормализации и создали TelemetryDevice таблица должна представлять собой уникальный список устройств и некоторую простую информацию о нем

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
Затем мы могли бы создать/изменить хранимую процедуру, которая заполняет таблицу журнала телеметрии и обновляет таблицу устройств
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
Чтобы сделать это действительно нормализованным, ваша существующая таблица будет изменена, чтобы быть фактическим DeviceID; и хранимая процедура будет скорректирована соответствующим образом.

Ваш новый запрос будет следующим 
SELECT DeviceName, LastContact
FROM Telemetry
-- GROUP BY not needed
-- ORDER BY optional
Поскольку имя устройства является первичным ключом, вам не нужно будет добавлять индекс

Другой подход заключается в добавлении столбца в существующую таблицу
ALTER TABLE Telemetry
ADD LastMessage BIT NULL DEFAULT (0)
GO
И снова соответствующая хранимая процедура для его заполнения
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

Тогда ваш новый запрос будет следующим 
SELECT DeviceName, SentAt
FROM Telemetry
WHERE LastMessage = 1
-- GROUP BY not needed
-- ORDER BY optional
Что было бы полезно, если бы у вас были индексы на DeviceName сами по себе и уникальный индекс DeviceName и столбца LastMessage.

Richard Deeming

Для варианта 2 я был бы склонен использовать отфильтрованный индекс[^]. :)

MadMyche

Верно, и его можно даже определить как уникальный отфильтрованный индекс

Рейтинг:
0

Gerry Schmitz

Попробуйте SQL TOP 1 с предложением WHERE для устройств; либо явные имена устройств, либо с помощью отдельного подзапроса; например

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

me@dagsunde.com

Это было бы неплохо, если бы существовало фиксированное, хорошо известное количество цветов (или, в моем случае, имен устройств)...
Но я не знаю ни количества имен устройств в любой данный момент, ни их имен.

I последняя строка, введенная для каждого имени устройства в базе данных, одна строка для каждого существующего имени устройства.

Gerry Schmitz

Я думал, что сказал "в противном случае" сначала запустить отдельный подзапрос для имен устройств.

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

Затем выбрать "верх", где в ...

Или проделать долгий путь.

(И вы действительно упомянули какой-то результат "10 устройств"; так что ваш "домен" не так уж велик. Иначе какой смысл в этом замечании? И "устройства" просто не появляются сами по себе).