Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Как поддерживать сортировку в колоночном индексе при загрузке новых данных?  [new]
User2155
Member

Откуда:
Сообщений: 150
Всем привет,

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

Давайте пример. Делаем выборку из таблицы, где 15,5 миллионов записей:
SELECT count(*) as Quantity
  FROM [tbl_test]
where (DATE1 between '20170401' and '20170430')

/*
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 2 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

(1 row(s) affected)
Table 'tbl_test'. Scan count 1, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 432, lob physical reads 0, lob read-ahead reads 727.
Table 'tbl_test'. Segment reads 6, segment skipped 2.

 SQL Server Execution Times:
   CPU time = 15 ms,  elapsed time = 43 ms.
*/


Видно что CPU time = 15 ms. При этом он прочитал 6 сегментов и пропустил 2.
Чтобы отсортировать таблицу по дате надо создать обычный кластеризованный индекс, а затем колоночный.
create clustered index CCI_tbl_test_table
	on dbo.tbl_test (DATE1)
	with (DROP_EXISTING = ON);

create clustered columnstore index CCI_tbl_test_table
	on dbo.tbl_test
	with (MAXDOP = 1, DROP_EXISTING = ON);


Результат выборки:
SELECT count(*) as Quantity
  FROM [tbl_test]
where (DATE1 between '20170401' and '20170430')

/*
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 2 ms, elapsed time = 2 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

(1 row(s) affected)
Table 'tbl_test'. Scan count 1, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 17, lob physical reads 0, lob read-ahead reads 27.
Table 'tbl_test'. Segment reads 1, segment skipped 7.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 4 ms.
*/


Видно, что CPU time = 0, и он пропустил 7 сегментов, а прочитал всего 1. Вот это меня и интересует.
Как можно поддерживать сортировку в колоночном индексе, чтобы при загрузке новых данных такое выполнение и дальше продолжалось?

Спасибо.
14 июн 17, 10:16    [20562595]     Ответить | Цитировать Сообщить модератору
 Re: Как поддерживать сортировку в колоночном индексе при загрузке новых данных?  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
Как вариант можете сделать секционированный колумнстор по дате
14 июн 17, 10:25    [20562641]     Ответить | Цитировать Сообщить модератору
 Re: Как поддерживать сортировку в колоночном индексе при загрузке новых данных?  [new]
User2155
Member

Откуда:
Сообщений: 150
AlanDenton,

партиции?
14 июн 17, 10:28    [20562657]     Ответить | Цитировать Сообщить модератору
 Re: Как поддерживать сортировку в колоночном индексе при загрузке новых данных?  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
User2155, да. Я кластерный колумнстор у себя по месяцам секционирую и получается весьма бодно
14 июн 17, 10:33    [20562680]     Ответить | Цитировать Сообщить модератору
 Re: Как поддерживать сортировку в колоночном индексе при загрузке новых данных?  [new]
User2155
Member

Откуда:
Сообщений: 150
AlanDenton,

идея здравая, но это "create partition function.... create partition schema ". Не хочется с этим заморачиваться :-)
14 июн 17, 10:37    [20562698]     Ответить | Цитировать Сообщить модератору
 Re: Как поддерживать сортировку в колоночном индексе при загрузке новых данных?  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
User2155, больше скажу. Когда колумстор сеционирован, то новые секции создать уже нельзя. Во всяком случае я точно помню с этим у меня были заморочки и прошлось на пару лет секции резервировать. Заморачиваться или нет - тут дело житейское. Мне лично помогло когда начал юзать 2016SP1 Exp и там были жесткие тормоза из-за нехватки ресурсов
14 июн 17, 10:40    [20562707]     Ответить | Цитировать Сообщить модератору
 Re: Как поддерживать сортировку в колоночном индексе при загрузке новых данных?  [new]
Mr. X
Guest
User2155,

А не проще использовать сжание на секционированной таблице и не колоночные индексы? Будет вам и сортировка и статистика.
14 июн 17, 10:41    [20562710]     Ответить | Цитировать Сообщить модератору
 Re: Как поддерживать сортировку в колоночном индексе при загрузке новых данных?  [new]
aleksrov
Member

Откуда:
Сообщений: 948
User2155,

Цикл статей по ColumnStore
http://www.sqlservercentral.com/stairway/121631/
Там же в 7 и 8 главе в конце есть упоминание об этом, но метод конечно спорный. Создаем Row Store индекс, а потом создаем новый кластерный с DROP_EXISTING = ON, т.е. по сути тоже что сделали вы.
14 июн 17, 10:46    [20562732]     Ответить | Цитировать Сообщить модератору
 Re: Как поддерживать сортировку в колоночном индексе при загрузке новых данных?  [new]
User2155
Member

Откуда:
Сообщений: 150
aleksrov,

у меня таблицы большие и они растут, т.е. добавляются миллионы записей регулярно.
Получается придется каждый раз после добавления записей удалять колоночный индекс, создавать обычный кластеризованный, чтобы отсортировать таблицу, и потом снова кластеризованный. Кошмаркокойто ))
14 июн 17, 10:49    [20562742]     Ответить | Цитировать Сообщить модератору
 Re: Как поддерживать сортировку в колоночном индексе при загрузке новых данных?  [new]
aleksrov
Member

Откуда:
Сообщений: 948
User2155
aleksrov,

у меня таблицы большие и они растут, т.е. добавляются миллионы записей регулярно.
Получается придется каждый раз после добавления записей удалять колоночный индекс, создавать обычный кластеризованный, чтобы отсортировать таблицу, и потом снова кластеризованный. Кошмаркокойто ))


Я ж говорю метод спорный. Скорее для каких нибудь архивных данных. Но на эту тему больше ничего дельного не находил.
14 июн 17, 10:54    [20562761]     Ответить | Цитировать Сообщить модератору
 Re: Как поддерживать сортировку в колоночном индексе при загрузке новых данных?  [new]
User2155
Member

Откуда:
Сообщений: 150
aleksrov,

а тут ничего интересного нет? ALTER INDEX (Transact-SQL). Там есть опция "SORT_IN_TEMPDB = { ON | OFF }".
Сейчас поиграюсь.
14 июн 17, 10:58    [20562776]     Ответить | Цитировать Сообщить модератору
 Re: Как поддерживать сортировку в колоночном индексе при загрузке новых данных?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
User2155,

SORT_IN_TEMPDB это не туда :)
14 июн 17, 11:03    [20562805]     Ответить | Цитировать Сообщить модератору
 Re: Как поддерживать сортировку в колоночном индексе при загрузке новых данных?  [new]
User2155
Member

Откуда:
Сообщений: 150
TaPaK,

мда, так и есть.
Кажется сортировку для колоночного индекса пока не придумали. Вот тут тоже парень мучался:
Code creating clustered columnstore index while maintaining row order

Получается по всему интернету трубят о "segment elimination", т.е. пропуске ненужных сегментов, а то, что данные отсортировать нереально тихо умалчивают.
Может в следующей версии...
14 июн 17, 11:14    [20562880]     Ответить | Цитировать Сообщить модератору
 Re: Как поддерживать сортировку в колоночном индексе при загрузке новых данных?  [new]
Ничто не мешает
Guest
Ничто не мешает создать кластерный rowstore индекс, в том числе и секционированный, а потом создать колоночный, который также можно секционировать в той же схеме что и rowstore. Правда без фокусов с индекс view такое можно реализовать только с 2016. Плюс этого гибрида в том, что там где надо делать сканирования будет использоваться колоночный индекс, где нужна будет селективная выборка будет использоваться rowstore; можно относительно быстра перестраивать колоночный индекс по rowstore в том числе и по отдельным секциям. Но конечно затраты будут на поддержку двух индексов.
14 июн 17, 12:26    [20563258]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить