Я разрабатываю таблицу с очень специализированным шаблоном использования. Таблица будет постоянно регистрироваться с ограниченным трафиком - ~ 25 записей в секунду, а затем каждую ночь я запускаю один большой запрос для извлечения большого количества данных.
Мой сценарий создания таблицы в настоящее время выглядит так:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (select * from sysobjects where name='records' and xtype='U')
CREATE TABLE [dbo].[records](
[TripID] varchar(255) NOT NULL,
[RecordTimeUTC] datetime2(0) NOT NULL,
[TimeOfDaySeconds] [int] NOT NULL,
[T0Latitude] [float] NOT NULL,
[T0Longitude] [float] NOT NULL,
[T1Latitude] [float] NULL,
[T1Longitude] [float] NULL,
[T2Latitude] [float] NULL,
[T2Longitude] [float] NULL,
[T3Latitude] [float] NULL,
[T3Longitude] [float] NULL,
[T4Latitude] [float] NULL,
[T4Longitude] [float] NULL,
[T5Latitude] [float] NULL,
[T5Longitude] [float] NULL,
[VehicleID] [int] NULL,
[ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY
) ON [PRIMARY]
GO
IF NOT EXISTS (select * from sys.indexes where name='TripIDRecordTimeIndex' and object_id = OBJECT_ID('dbo.records'))
CREATE INDEX TripIDRecordTimeIndex ON records (TripID, RecordTimeUTC desc)
GO
IF NOT EXISTS (select * from sys.indexes where name='TripIDIndex' and object_id = OBJECT_ID('dbo.records'))
CREATE INDEX TripIDIndex ON records (TripID)
GO
IF NOT EXISTS (select * from sys.indexes where name='RecordTimeUTCIndex' and object_id = OBJECT_ID('dbo.records'))
CREATE INDEX RecordTimeUTCIndex ON records (RecordTimeUTC desc)
GO
IF NOT EXISTS (select * from sys.objects where name like 'UniqueConstraint2' and parent_object_id = OBJECT_ID('dbo.records'))
ALTER TABLE [dbo].[records] ADD CONSTRAINT UniqueConstraint2 UNIQUE(VehicleID, RecordTimeUTC desc);
GO
IF NOT EXISTS (select * from sys.indexes where name='VehicleIDIndex' and object_id = OBJECT_ID('dbo.records'))
CREATE INDEX VehicleIDIndex ON records (VehicleID)
GO
В настоящее время в моей таблице около 60 миллионов записей, размер которой меньше 50 ГБ.
Запрос на извлечение данных занимает очень много времени. В настоящее время это занимает более часа. Я не уверен, что основной причиной является дизайн моей таблицы или дизайн запроса (хотя, вероятно, и то, и другое).
Мне нужно извлечь последнее количество X элементов для каждого TripID в указанном мной наборе TripID. Существует около 10 тысяч различных идентификаторов, и я обычно хочу запросить примерно половину из них. X также различается между ними, поэтому мой лучший способ запроса в настоящее время - сгенерировать скрипт, который выглядит примерно так:
SELECT rs.* FROM (SELECT *, ROW_NUMBER() over (Partition BY TripID ORDER BY RecordTimeUTC DESC ) AS Rank FROM records where TripID in (20141000,20441000,30011022,30011021,30011008,30012029,30012028,30012027,30011007,30011019,30011018,30012026,30012025,30012024,30011017,30011016,30012023,30012022,30011015,30011014,30012021,30012020,30011013,30011012,30013000,30013001,30013019,30013009,30011011,30011010,30011009,30013008,30013007,30012010,30012009,30013005,30013004,30013003,30012014,30012019,30013021,30013020,30011006,30011004,30012018,30012017,30012016,30013006,30011003,30011002,30012015,30012013,30013013,30013002,30011001,30011000,30011020,30012012,30012011,30011005,30011030,30012001,30012008,30012007,30011029,30011028,30012006,30012005,30011031,30011027,30012004,30012003,30011026,30011025,30011024,30012002,30012000,30012031,30011023,30012030,30015005,30016006,30016013,30016012,30014020,30014019,30014018,30016011,30016010,30014017,30014016,30016009,30016008,30014015,30014013,30014012,30016005,30016004,30016003,30014010,30014009,30016002,30016001,30014008,30014007,30016000,30016007,30014006,30014005,30014004,30014003,30014002,30014001,30014000,30014023,30014014,30015012,30015004,30015003,30013018,30013017,30015002,30015001,30013016,30013015,30013014,30015000,30015013,30015011,30013012,30013011,30015010,30015009,30013010,30014011,30015008,30015007,30014022,30014021,30015006,33651001,33661006)) rs WHERE Rank <= 690
UNION
SELECT rs.* FROM (SELECT *, ROW_NUMBER() over (Partition BY TripID ORDER BY RecordTimeUTC DESC ) AS Rank FROM records where TripID in (20431003,20431002,20431001,20432003,20432002,20432001,30221001,33861002,33861003)) rs WHERE Rank <= 855
UNION
SELECT rs.* FROM (SELECT *, ROW_NUMBER() over (Partition BY TripID ORDER BY RecordTimeUTC DESC ) AS Rank FROM records where TripID in (20171029,20171030,20002002,26122001)) rs WHERE Rank <= 45
UNION
...
(Приведенный выше запрос возвращает 690 экземпляров каждой поездки в первом списке, 855 экземпляров каждого во втором, 45 в третьем и т. Д. Запрос намного больше этого - это всего лишь его фрагмент. В общем, я извлечь 10-15 миллионов строк)
Как уже упоминалось, моя работа ужасна. Это облачная штука? Это дизайн? Стоит ли использовать кластерный индекс? (Пробовал это для TripID, но это было еще хуже). Могу я как-нибудь улучшить свой запрос? Извлечь такое же количество экземпляров для каждого идентификатора, например, а затем отфильтровать?
Я заметил, что у меня есть пара дополнительных индексов, которые могут не использоваться в моем запросе. Я просто попытался добавить еще, так как производительность вставки не является проблемой. В моем запросе планировалось использовать TripIDRecordTimeIndex.
Даже при масштабировании плана данных в Azure SQL до S7 (800 DPU) я не могу заставить это работать быстро. Любая обратная связь приветствуется.
Изменить: я недавно изменил TripID с int на varchar (255) - это нарушит мою производительность?
Edit2: План выполнения: 
Ссылка для скачивания полного плана выполнения
Edit3: Выяснили, что добавление кавычек ('') вокруг идентификаторов TripID, которые я запрашиваю, значительно улучшило производительность!
Edit4: Я добавил индекс, как это было предложено TheGameiswar - и разница в день и ночь! Спасибо! Прилагается новый план выполнения.
