ReSQL.ru
     
powered by simpleCommunicator - 2.0.53     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Выборка данных из таблицы с миллиардами записей
20 сообщений из 45, страница 2 из 2
  2  все
Выборка данных из таблицы с миллиардами записей
    #39941851
aleks222
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
aleks222
Участник
kaktus1983
Кластерный индекс еще делается, уже 12 часов...
файл БД +60 GB
Процесс активен и файл БД растет периодически, так что не висит...
и это одна, не самая большая, таблица(

Растет потому, что пишется новое, прежде чем удалить старое + двойная запись в журнал (старого и нового).

ЗЫ. Только договоренность со шкафом может ускорить процесс.
...
Рейтинг: 0 / 0
28.03.2020, 11:49
| Ответить | Цитировать | Написать  
Выборка данных из таблицы с миллиардами записей
    #39941852
Гавриленко Сергей Алексеевич
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гавриленко Сергей Алексеевич
Участник
aleks222,

aleks222+ двойная запись в журнал (старого и нового).Старое-то для каких целей писать?
...
Рейтинг: 0 / 0
28.03.2020, 11:52
| Ответить | Цитировать | Написать  
Выборка данных из таблицы с миллиардами записей
    #39941853
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
kaktus1983,

Еще раз - явное указание индекса в запросе не может влиять на результат.
Если влияет, то либо это уже другой запрос, либо индекс поврежден. Проверить можно выполнив checkdb или checktable.
...
Рейтинг: 0 / 0
28.03.2020, 11:53
| Ответить | Цитировать | Написать  
Выборка данных из таблицы с миллиардами записей
    #39941856
kaktus1983
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
kaktus1983
Гость
aleks222,
Файл лога кстати не растет, так и остался 2,5GB
...
Рейтинг: 0 / 0
28.03.2020, 12:20
| Ответить | Цитировать | Написать  
Выборка данных из таблицы с миллиардами записей
    #39941862
Фотография полудух
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
полудух
Участник
kaktus1983
Кластерный индекс еще делается, уже 12 часов...

вы бы себе отпилили тестовую БД на 10 лямов, например
чтобы до старости то дожить, хотя бы
...
Рейтинг: 0 / 0
28.03.2020, 13:07
| Ответить | Цитировать | Написать  
Выборка данных из таблицы с миллиардами записей
    #39941884
Фотография alexeyvg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
alexeyvg
Участник
полудух
kaktus1983
Кластерный индекс еще делается, уже 12 часов...

вы бы себе отпилили тестовую БД на 10 лямов, например
чтобы до старости то дожить, хотя бы
У ТС же нет пользователей, кроме него самого.
...
Рейтинг: 0 / 0
28.03.2020, 16:27
| Ответить | Цитировать | Написать  
Выборка данных из таблицы с миллиардами записей
    #39941900
SERG1257
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SERG1257
Участник
kaktus1983Учитывая время построения индекса, а таблиц таких 20+, хочу спросить: что в моем случае будет правильней делать?Еще раз повторю
Создать некластерный колумнстор по всем полям партицированным по TagIndex (индекс будет раз в 10 меньше кучи)
Полагаю ваша куча почти отсортирована по дате, так что запросы указанные выше летать будут (лишнее будет отсекатся)

Можете начать пока без секционирования (партицирования)
...
Рейтинг: 0 / 0
28.03.2020, 18:21
| Ответить | Цитировать | Написать  
Выборка данных из таблицы с миллиардами записей
    #39941916
kaktus1983
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
kaktus1983
Гость
SERG1257
Еще раз повторю
Создать некластерный колумнстор по всем полям партицированным по TagIndex (индекс будет раз в 10 меньше кучи)
Полагаю ваша куча почти отсортирована по дате, так что запросы указанные выше летать будут (лишнее будет отсекатся)
Можете начать пока без секционирования (партицирования)

Да, куча идет по дате по порядку, БД - это журнал, почти без удаления.
Я пока только читаю о колумнсторе...
Честно говоря, не понимаю что Вы имеете ввиду под "партицированным по TagIndex"
Если не сложно, напишите по-подробней, какие поля надо перечислить в индексе.
Код: sql
1.
2.
3.
4.
5.
6.
CREATE NONCLUSTERED COLUMNSTORE INDEX [имя] ON [dbo].[Temperatures]
(
	[DateAndTime],  -- ??
	[TagIndex],  -- ??
	[Val]  -- ??
)
...
Рейтинг: 0 / 0
28.03.2020, 21:03
| Ответить | Цитировать | Написать  
Выборка данных из таблицы с миллиардами записей
    #39941927
SERG1257
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SERG1257
Участник
kaktus1983какие поля надо перечислить в индексеВсе.
kaktus1983Я пока только читаю о колумнстореОно еще называется Real-Time Operational Analytics
https://docs.microsoft.com/en-us/sql/relational-databases/indexes/get-started-with-columnstore-for-real-time-operational-analytics?view=sql-server-ver15

Суть колумнстора - все данные хранятся блоками (rowgroup) по миллиону записей. У каждого блока по каждому полю есть min/max то есть сервер заранее знает, что этот блок читать не надо если значения @from позже min или @till раньше max. Все данные в каждом поле для каждой rowgroup хорошо жмутся (степень сжатия легко достигает 10 и выше)
kaktus1983"партицированным по TagIndex"Если разбить ваш индекс еще и на подгруппы с разными TagIndex, то список rowgroup для чтения сократится еще больше. (как именно разбивать партиции на TagIndex по одной, по две или по десять это надо мерять). Для начала можете этого не делать. Создайте колумнстор и погоняйте запросы.
Партиции можно применить и для обычных индексов и кучи. Эта технология перпендикулярна колумнстору.

Плюсы сразу - вам не надо будет перестраивать кластерный индекс удваивая требования по хранению. Добавить придется только сам колумнстор индекс (сжатый) и надеюсь это будет быстрее и дешевле.

Далее запрос на чтение делается в т.н. Batch Mode. Что это такое я вам на пальцах не объясню.
https://docs.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-query-performance?view=sql-server-ver15

Минус - запись в колумнстор будет в один поток, ибо блокировки будут не на страницу, а на всю rowgroup. Как я понял это вам не важно.
...
Рейтинг: 0 / 0
28.03.2020, 23:12
| Ответить | Цитировать | Написать  
Выборка данных из таблицы с миллиардами записей
    #39941928
kaktus1983
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
kaktus1983
Гость
SERG1257,
Спасибо! Понятно и интересно описали суть.
...
Рейтинг: 0 / 0
28.03.2020, 23:24
| Ответить | Цитировать | Написать  
Выборка данных из таблицы с миллиардами записей
    #39941929
SERG1257
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SERG1257
Участник
Извиняюсь 'в лоб' не получится

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
CREATE PARTITION FUNCTION F_TagIndex (int)  
    AS RANGE LEFT FOR VALUES (100,200,300,400,500,600,700,800,900,1000); 

CREATE PARTITION SCHEME TagIndex AS PARTITION F_TagIndex all TO ([PRIMARY]) 

Create nonClustered Columnstore Index CC_Temperatures on dbo.test_table (id,val,d_entry) on TagIndex(id)
Msg 35316, Level 16, State 1, Line 6
The statement failed because a columnstore index must be partition-aligned with the base table. Create the columnstore index using the same partition function and same (or equivalent) partition scheme as the base table. If the base table is not partitioned, create a nonpartitioned columnstore index.



Надо будет сначала перестроить кучу для партицирования
...
Рейтинг: 0 / 0
28.03.2020, 23:29
| Ответить | Цитировать | Написать  
Выборка данных из таблицы с миллиардами записей
    #39941932
kaktus1983
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
kaktus1983
Гость
SERG1257,
Эх, а я уже в уме представил быстрое и изящное решение))
Буду пробовать. Спасибо!
...
Рейтинг: 0 / 0
29.03.2020, 00:28
| Ответить | Цитировать | Написать  
Выборка данных из таблицы с миллиардами записей
    #39942371
kaktus1983
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
kaktus1983
Гость
Попробовал разные варианты на разных таблицах. Индексы:
Код: sql
1.
create index [имя] on [dbo].[Temperatures] (TagIndex, DateAndTime) include (Val);

Код: sql
1.
create clustered index [имя] on [dbo].[Temperatures] (TagIndex, DateAndTime);

работают примерно одинаково.
CREATE занимает примерно 8 часов на таблицу с 500 000 000 записей (на внешний HDD по USB).
NONCLUSTERED с include быстрее, но не существенно.

Для таблицы с 1 500 000 000 записей решил попробовать COLUMNSTORE.
SERG1257
Еще раз повторю
Создать некластерный колумнстор по всем полям партицированным по TagIndex (индекс будет раз в 10 меньше кучи)
Полагаю ваша куча почти отсортирована по дате, так что запросы указанные выше летать будут (лишнее будет отсекатся)
Можете начать пока без секционирования (партицирования)

Да, все таблицы-кучи были записаны параллельно, в хронологическом порядке.
Решил начать с простого, без секционирования
SERG1257
kaktus1983какие поля надо перечислить в индексе
Все.
Создал
Код: sql
1.
CREATE NONCLUSTERED COLUMNSTORE INDEX [имя] ON [dbo].[Temperatures] (TagIndex, DateAndTime, Val);

Эффекта нет, как будто нет индекса вообще.
  • ? Видимо я что-то не понимаю и делаю не так...?
  • ? Важно ли, при создании COLUMNSTORE, что в таблице Temperatures есть еще несколько не нужных мне столбцов?
  • ? Стоит ли пробовать кластерный COLUMNSTORE?
  • ? Возможно нужны дополнительные (не колумнсторе) индексы?
p.s. CREATE NONCLUSTERED COLUMNSTORE выполнилось гораздо быстрее, чем я ожидал (до часа).
...
Рейтинг: 0 / 0
30.03.2020, 22:00
| Ответить | Цитировать | Написать  
Выборка данных из таблицы с миллиардами записей
    #39942388
SERG1257
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SERG1257
Участник
kaktus1983Эффекта нет, как будто нет индекса вообще. В смысле нет ускорения?
План в студию
>Видимо я что-то не понимаю и делаю не так...?
Это должно быть именно так. План должен показать использование индекса. На крайняк можно заставить использовать индекс как советовал invm
22107040

Версия у вас непропатченая
Накатите последний патч KB4535007 https://support.microsoft.com/en-ca/help/4535007/cumulative-update-19-for-sql-server-2017
Поможет или нет не знаю, но не повредит.

>Важно ли, при создании COLUMNSTORE, что в таблице Temperatures есть еще несколько не нужных мне столбцов?
Не важно. Но при создании COLUMNSTORE индекса добавляют все поля.

>Стоит ли пробовать кластерный COLUMNSTORE?
Не стоит. Создание кластерного индекса перетряхнет другой (обычный) индекс, это будет долго.

>Возможно нужны дополнительные (не колумнсторе) индексы?
Не нужны

>p.s. CREATE NONCLUSTERED COLUMNSTORE выполнилось гораздо быстрее, чем я ожидал (до часа).
Ради того все и затевалось. Размер индекса должен быть тоже очень маленкий (по сравнению с кучей). Он должен быстро и умно читатся.
...
Рейтинг: 0 / 0
30.03.2020, 23:46
| Ответить | Цитировать | Написать  
Выборка данных из таблицы с миллиардами записей
    #39942411
kaktus1983
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
kaktus1983
Гость
SERG1257, спасибо большое за ответы!
Даю план в студию. Вдруг чего упустил, продублирую: сама таблица
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
CREATE TABLE [dbo].[Temperatures_FloatTable](
	[DateAndTime] [datetime2](7) NULL,
	[Millitm] [smallint] NULL,
	[TagIndex] [smallint] NULL,
	[Val] [float] NULL,
	[Status] [nvarchar](1) NULL,
	[Marker] [nvarchar](1) NULL
) ON [PRIMARY]
GO

Размер 60GB, записей 1.5млрд. Индексов нет вообще, создаю
Код: sql
1.
CREATE NONCLUSTERED COLUMNSTORE INDEX [Temperatures_FloatTable_CSI_TagDT] ON [dbo].[Temperatures_FloatTable] (TagIndex, DateAndTime, Val);

только по трем нужным мне столбцам.
Код: sql
1.
with (index = [Temperatures_FloatTable_CSI_TagDT]

ничего не меняет
...
Рейтинг: 0 / 0
31.03.2020, 06:54
| Ответить | Цитировать | Написать  
Выборка данных из таблицы с миллиардами записей
    #39942412
SERG1257
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SERG1257
Участник
А почему план в студии от другого запроса?
Две с половиной минуты неприемлимо? Сколько времени занимает группировка (запрос без *)
...
Рейтинг: 0 / 0
31.03.2020, 07:12
| Ответить | Цитировать | Написать  
Выборка данных из таблицы с миллиардами записей
    #39942415
Фотография a_voronin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
a_voronin
Участник
kaktus1983,

Колумнстор партиционированный по полю фильтрации (дате) должен неплохо себя показать. Вообще таблицу надо отгонять в хранилище данных (в stage) и там заниматься такого рода анализом. И вообще такого рода данные рекомендую отогнать в вертику. Бесплано до 1 TB
...
Рейтинг: 0 / 0
31.03.2020, 08:00
| Ответить | Цитировать | Написать  
Выборка данных из таблицы с миллиардами записей
    #39942498
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
kaktus1983,

Секционируйте кучу по дате, если выбираете относительно небольшие диапазоны.
...
Рейтинг: 0 / 0
31.03.2020, 12:13
| Ответить | Цитировать | Написать  
Выборка данных из таблицы с миллиардами записей
    #39942527
kaktus1983
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
kaktus1983
Гость
SERG1257
А почему план в студии от другого запроса?
Две с половиной минуты неприемлимо? Сколько времени занимает группировка (запрос без *)
План именно от запроса на скрине... специально и его заскринил.
Две с половиной минуты - это для очень упрощенного варианта запроса: выборка top(10) и только за 5 секунд.
Для стандартного avg(Val) и время примерно за час - я отменил после 10 минут, а с индексом
Код: sql
1.
create index [имя] on [dbo].[Temperatures] (TagIndex, DateAndTime) include (Val);

выполняется до секунды.
В конечном итоге мне надо сформировать матрицу данных 12000 х 444, каждая ячейка которой - результат запроса avg(Val) за диапазон времени примерно час. Тут каждая милисекунда будет важна))
...
Рейтинг: 0 / 0
31.03.2020, 13:41
| Ответить | Цитировать | Написать  
Выборка данных из таблицы с миллиардами записей
    #39942529
kaktus1983
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
kaktus1983
Гость
Владислав Колосов, a_voronin, спасибо!
В целом задачу решил, SP отработала, 12000 х 444 значений просчитала за 4 часа.
Всем спасибо за помощь и идеи!
aleks222, SERG1257, вам отдельная благодарность))
...
Рейтинг: 0 / 0
31.03.2020, 13:49
| Ответить | Цитировать | Написать  
20 сообщений из 45, страница 2 из 2
  2  все
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Выборка данных из таблицы с миллиардами записей
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=46&msg=39942388&tid=1686279]:
 0ms
get settings:
 9ms
get forum list:
 11ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 28ms
get topic data:
 7ms
get forum data:
 2ms
get page messages:
 59ms
get tp. blocked users:
 2ms
others: 13ms
total:  137ms
созд. / загр.: 137ms
x
x
Закрыть


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