Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Выборка данных из таблицы с миллиардами записей / 25 сообщений из 45, страница 1 из 2
  1  все
27.03.2020, 15:48
    #39941698
kaktus1983
kaktus1983
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Выборка данных из таблицы с миллиардами записей
Приветствую!

Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64)

В БД имеем таблицы следующего вида, с индексами:

Код: sql
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] и периоду времени.
Обычно диапазон времени примерно час. За это время в таблице порядка миллиона записей.

Запрос вида:
Код: sql
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 подключение из студии для выполнения запросов.

Спасибо)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.03.2020, 15:54
    #39941701
aleks222
aleks222
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Выборка данных из таблицы с миллиардами записей
Ну, страдалец, если у тя ВСЕГДА интервал кратен часу, то индексированное представление спасет.

ЗЫ.
"1." Порядок в where - пофиг. Вот порядок в кластерном индексе - это ваше ФСЕ. Плохой у тя кластерный индекс.
"2." Сильно влияет, страдалец. Весьма.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.03.2020, 15:56
    #39941703
msLex
msLex
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Выборка данных из таблицы с миллиардами записей
aleks222
Плохой у тя кластерный индекс.

у автора вообще куча с некластерным индексом сверху
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.03.2020, 15:59
    #39941704
kaktus1983
kaktus1983
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Выборка данных из таблицы с миллиардами записей
База не моя, работаю с тем что есть и как есть
индекс только один NONCLUSTERED
интервал не всегда кратен часу
Вопрос как раз в том, может надо создать доп индекс или т.п.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.03.2020, 16:01
    #39941705
kaktus1983
kaktus1983
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Выборка данных из таблицы с миллиардами записей
msLex
aleks222
Плохой у тя кластерный индекс.

у автора вообще куча с некластерным индексом сверху

Как вот с такой кучей работать правильно?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.03.2020, 16:08
    #39941710
kaktus1983
kaktus1983
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Выборка данных из таблицы с миллиардами записей
aleks222
Ну, страдалец, если у тя ВСЕГДА интервал кратен часу, то индексированное представление спасет.

Как вариант, я рассматривал строго почасово.
Если Вам не сложно, напишите как выглядеть будет такое View
сам не могу сообразить
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.03.2020, 16:08
    #39941711
aleks222
aleks222
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Выборка данных из таблицы с миллиардами записей
kaktus1983

Вопрос как раз в том, может надо создать доп индекс или т.п.


Код: sql
1.
create index [доп индекс] on [dbo].[Temperatures] (TagIndex, DateAndTime) include (Val);



Только, страдалец, это копия таблицы.
Нафига это тебе нужно?
Может с хозяином таблицы договориться?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.03.2020, 16:14
    #39941714
kaktus1983
kaktus1983
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Выборка данных из таблицы с миллиардами записей
aleks222

Может с хозяином таблицы договориться?

Не получится, хозяин - контроллер в шкафу, он ничего менять не захочет, а хозяин шкафа так и тем более.
Мне надо произвести разовый анализ данных (технологических показателей работы агрегата за период времени)
Эти запросы нужны только мне, как промежуточный этап для подготовки данных
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.03.2020, 16:16
    #39941716
SERG1257
SERG1257
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Выборка данных из таблицы с миллиардами записей
kaktus1983 Вопрос как раз в том, может надо создать доп индекс или т.п.
https://docs.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-overview?view=sql-server-2017
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.03.2020, 16:45
    #39941721
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Выборка данных из таблицы с миллиардами записей
kaktus1983
Обычно диапазон времени примерно час. За это время в таблице порядка миллиона записей.
Тогда для начала попробуйте тупо
Код: sql
1.
2.
3.
4.
5.
SELECT avg([Val])
  FROM [dbo].[Temperatures] with (index = [TemperaturesIndex])
  WHERE ([TagIndex] = 198) 
    AND ([DateAndTime] >= @from)
    AND ([DateAndTime] < @till)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.03.2020, 16:48
    #39941722
ROLpogo
ROLpogo
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Выборка данных из таблицы с миллиардами записей
kaktus1983,
Если данные из базы читаются только в режиме оффлайн, то при заливке данных индекс будет только мешать. Индекс понадобится для чтения данных, вот тогда его и создавайте.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.03.2020, 16:55
    #39941724
kaktus1983
kaktus1983
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Выборка данных из таблицы с миллиардами записей
ROLpogo,
Спасибо, я так и делаю
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.03.2020, 18:39
    #39941765
kaktus1983
kaktus1983
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Выборка данных из таблицы с миллиардами записей
invm,
добавка
Код: sql
1.
 with (index = [TemperaturesIndex])

приводит к тому, что запрос выполняется мгновенно, но результат NULL
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.03.2020, 18:40
    #39941766
kaktus1983
kaktus1983
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Выборка данных из таблицы с миллиардами записей
SERG1257,
Спасибо, почитаю, не знал про такой функционал
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.03.2020, 18:45
    #39941767
kaktus1983
kaktus1983
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Выборка данных из таблицы с миллиардами записей
aleks222

Код: sql
1.
create index [доп индекс] on [dbo].[Temperatures] (TagIndex, DateAndTime) include (Val);


дает результат, спасибо !
Индекс на одну таблицу (не самую большую, 600 млн записей) делался 2,5 часа и + 15GB в БД
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.03.2020, 18:49
    #39941769
aleks222
aleks222
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Выборка данных из таблицы с миллиардами записей
kaktus1983
aleks222

Код: sql
1.
create index [доп индекс] on [dbo].[Temperatures] (TagIndex, DateAndTime) include (Val);


дает результат, спасибо !
Индекс на одну таблицу (не самую большую, 600 млн записей) делался 2,5 часа и + 15GB в БД


Договорись со шкафом и сделай кластерный индекс

Код: sql
1.
create clustered index [кл.индекс] on [dbo].[Temperatures] (TagIndex, DateAndTime);



Шкафу будет пофиг, а тебе приятно.

ЗЫ. Ну а если не договоришься
Код: sql
1.
2.
DROP INDEX [TemperaturesIndex];
create clustered index [кл.индекс] on [dbo].[Temperatures] (TagIndex, DateAndTime);
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.03.2020, 19:03
    #39941771
kaktus1983
kaktus1983
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Выборка данных из таблицы с миллиардами записей
aleks222,
полностью согласен!
еще раз спасибо)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.03.2020, 09:04
    #39941828
kaktus1983
kaktus1983
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Выборка данных из таблицы с миллиардами записей
aleks222
Код: sql
1.
2.
3.

DROP INDEX [TemperaturesIndex];
create clustered index [кл.индекс] on [dbo].[Temperatures] (TagIndex, DateAndTime)
    • Вариант для 1,2 миллиарда записей выполняется уже 9 часов. Посмотрим чем закончится. Учитывая время построения индекса, а таблиц таких 20+, хочу спросить: что в моем случае будет правильней делать?
    • Код: sql
      1.
      2.
    • create index [доп индекс] on [dbo].[Temperatures] (TagIndex, DateAndTime) include (Val);
  • Код: sql
    1.
    2.
    • create clustered index [кл.индекс] on [dbo].[Temperatures] (TagIndex, DateAndTime)
    • или возможно даже просто
  • Код: sql
    1.
    2.
    • create index [индекс] on [dbo].[Temperatures] (TagIndex, DateAndTime)
    • хватит?
  • Код: sql
    1.
    2.
    • DROP INDEX [TemperaturesIndex];
    • Спасибо)
    ...
    Рейтинг: 0 / 0
    | Ответить | Цитировать | Написать  
    28.03.2020, 09:23
        #39941833
    aleks222
    aleks222
    Участник
    Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
    Выборка данных из таблицы с миллиардами записей
    kaktus1983
    aleks222
    Код: sql
    1.
    2.
    3.
    
DROP INDEX [TemperaturesIndex];
create clustered index [кл.индекс] on [dbo].[Temperatures] (TagIndex, DateAndTime)
    • Вариант для 1,2 миллиарда записей выполняется уже 9 часов. Посмотрим чем закончится. Учитывая время построения индекса, а таблиц таких 20+, хочу спросить: что в моем случае будет правильней делать?
    • Код: sql
      1.
      2.
    • Так быстрее create index [доп индекс] on [dbo].[Temperatures] (TagIndex, DateAndTime) include (Val);
  • Код: sql
    1.
    2.
    • Так место больше экономится create clustered index [кл.индекс] on [dbo].[Temperatures] (TagIndex, DateAndTime)
    • или возможно даже просто
  • Код: sql
    1.
    2.
    • Это не пойдет create index [индекс] on [dbo].[Temperatures] (TagIndex, DateAndTime)
    • хватит?
  • Код: sql
    1.
    2.
    • Место сэкономишь DROP INDEX [TemperaturesIndex];
    • Спасибо) Как-то так. Эффективнее всего уломать шкаф.
    ...
    Рейтинг: 0 / 0
    | Ответить | Цитировать | Написать  
    28.03.2020, 09:31
        #39941836
    kaktus1983
    kaktus1983
    Гость
    Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
    Выборка данных из таблицы с миллиардами записей
    aleks222

    Как-то так.
    Эффективнее всего уломать шкаф.

    Так все же, какой из этих индексов более рационально делать?
    Плюсы и минусы кластерного и с include можете показать?
    ...
    Рейтинг: 0 / 0
    | Ответить | Цитировать | Написать  
    28.03.2020, 10:25
        #39941841
    invm
    invm
    Участник
    Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
    Выборка данных из таблицы с миллиардами записей
    kaktus1983
    invm,
    добавка
    Код: sql
    1.
     with (index = [TemperaturesIndex])


    приводит к тому, что запрос выполняется мгновенно, но результат NULL
    Чудес не бывает.
    Либо неправильно написан запрос, либо неверны критерии отбора, либо индекс битый.
    kaktus1983
    Так все же, какой из этих индексов более рационально делать?
    Кластерный. Место сэкономите.
    А с точки зрения решаемой задачи, кластерный и некластерный с include одинаковы.
    ...
    Рейтинг: 0 / 0
    | Ответить | Цитировать | Написать  
    28.03.2020, 10:34
        #39941842
    kaktus1983
    kaktus1983
    Гость
    Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
    Выборка данных из таблицы с миллиардами записей
    invm
    Чудес не бывает.
    Либо неправильно написан запрос, либо неверны критерии отбора, либо индекс битый.
    в запросе сложно сделать ошибку, насчет битости индекса - не знаю.
    invm
    Кластерный. Место сэкономите.
    А с точки зрения решаемой задачи, кластерный и некластерный с include одинаковы.
    Понял, спасибо.
    ...
    Рейтинг: 0 / 0
    | Ответить | Цитировать | Написать  
    28.03.2020, 11:14
        #39941847
    invm
    invm
    Участник
    Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
    Выборка данных из таблицы с миллиардами записей
    kaktus1983
    в запросе сложно сделать ошибку
    Это вам так кажется.
    Например, '2020-03-02 00:00:00.0000000', в зависимости от языка сеанса, может быть как 2-м марта, так и 3-м февраля.
    ...
    Рейтинг: 0 / 0
    | Ответить | Цитировать | Написать  
    28.03.2020, 11:31
        #39941848
    kaktus1983
    kaktus1983
    Гость
    Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
    Выборка данных из таблицы с миллиардами записей
    invm,
    я ж к рабочему запросу только добавляю
    Код: sql
    1.
    with (index = [TemperaturesIndex])

    и все - и вместо результата вижу одну строку с NULL
    ...
    Рейтинг: 0 / 0
    | Ответить | Цитировать | Написать  
    28.03.2020, 11:35
        #39941849
    kaktus1983
    kaktus1983
    Гость
    Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
    Выборка данных из таблицы с миллиардами записей
    Кластерный индекс еще делается, уже 12 часов...
    файл БД +60 GB
    Процесс активен и файл БД растет периодически, так что не висит...
    и это одна, не самая большая, таблица(
    ...
    Рейтинг: 0 / 0
    | Ответить | Цитировать | Написать  
      1  все
    Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Выборка данных из таблицы с миллиардами записей / 25 сообщений из 45, страница 1 из 2
    Найденые пользователи ...
    Разблокировать пользователей ...
    Читали тему (0):
    Читали форум (0):
    Пользователи онлайн (0):
    x
    x
    Закрыть


    Просмотр
    0 / 0
    Close
    Debug Console [Select Text]