Приветствую!
Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64)
В БД имеем таблицы следующего вида, с индексами:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
CREATE TABLE [dbo].[Temperatures](
[DateAndTime] [datetime2](7) NULL,
[TagIndex] [smallint] NULL,
[Val] [float] NULL
) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [TemperaturesIndex] ON [dbo].[Temperatures]
(
[DateAndTime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
Данные в таблицах имеют вид:
DateAndTimeTagIndexVal2018-11-08 11:11:59.000000007.014465332031252018-11-08 11:11:59.000000016.806945800781252018-11-08 11:11:59.000000027.15197753906252018-11-08 11:11:59.000000037.075378417968752018-11-08 11:11:59.000000046.170684814453132018-11-08 11:11:59.000000056.462219238281252018-11-08 11:11:59.000000067.959045410156252018-11-08 11:11:59.000000077.969421386718752018-11-08 11:11:59.000000086.334503173828132018-11-08 11:11:59.000000097.04412841796875
В каждой такой таблице количество записей измеряется миллиардами.
Частота записей времени - примерно 1 секунда. Количество TagIndex - до 100-1000 на таблицу.
Т.е. в таблицу пишется до 1000 строк в секунду.
Размер таблицы порядка 50GB, индекса 20GB
Необходимо сделать множество выборок средних значений [Val] по заданному [TagIndex] и периоду времени.
Обычно диапазон времени примерно час. За это время в таблице порядка миллиона записей.
Запрос вида:
1.
2.
3.
4.
5.
6.
7.
declare @from datetime2 = '2020-03-02 00:00:00.0000000';
declare @till datetime2 = '2020-03-02 01:00:00.0000000';
SELECT avg([Val])
FROM [dbo].[Temperatures]
WHERE ([TagIndex] = 198)
AND ([DateAndTime] >= @from)
AND ([DateAndTime] < @till)
работает крайне медленно, причем по разному (может и минуту, а может и час-два по аналогичной выборке).
Просьба посоветовать, как ускорить такие запросы. Возможно перестроить или создать дополнительные индексы...
Попутно, подскажите:
1. Важен ли порядок условий в WHERE ([TagIndex] или диапазон [DateAndTime] первым)?
2. Насколько влияет то, что БД приаттачена на внешнем носителе HDD, подключенном по USB? В базу понятно ничего не пишется, только 1 подключение из студии для выполнения запросов.
Спасибо)
