Добро пожаловать в форум, Guest >> Войти | Регистрация | Поиск | Правила | | В избранное | Подписаться | ||
Все форумы / Microsoft SQL Server |
![]() ![]() |
uaggster Member Откуда: Сообщений: 960 |
Коллеги приветствую! Помогите советом. Имеется большая таблица (до миллиарда записей не дотягивает, но плюс-минус поллаптя где то так), которая содержит последовательности записей в разрезе неких ID: ID, N (по порядку), Дата, + доп. критерии. Типичной является задача: Найти строки с максимальной N в разрезе ID, соответствующие определенным критериям. Например: CREATE TABLE #t ( GRP INT ,ID BIGINT ,N INT ,DTA DATE ,isAсtive BIT ,isInvalid BIT ,isOverlapped BIT ) insert into #t Values (1, 1, 1, '20180101', 0, 0, 1), (1, 1, 2, '20180102', 0, 1, 1), (1, 1, 3, '20180103', 0, 0, 1), (1, 1, 4, '20180110', 1, 0, 0), (1, 2, 1, '20180101', 0, 0, 1), (1, 2, 2, '20180102', 1, 1, 1), (1, 2, 3, '20180102', 0, 0, 1), (1, 3, 1, '20180105', 1, 0, 0), (2, 2, 1, '20180101', 0, 0, 1), (2, 2, 2, '20180102', 0, 1, 1), (2, 2, 3, '20180102', 0, 0, 1), (2, 3, 4, '20180103', 1, 0, 0) Select top (1) with ties * from #t Where DTA between '20180101' and '20180103' and isAсtive = 1 Order by ROW_NUMBER() over (partition by GRP, ID order by N DESC) ASC Как это можно ускорить? Какие индексы имеет смысл построить? Какая конструкция запроса будет оптимальна в таком случае (тот, который показан - иллюстрация, какие выборки обычно требуются). Типичное количество partition by GRP, ID ~ 10-100 записей, общее количество записей, как я сказал - сильно за 100 миллионов. Таблица секционирована по GRP, но секций не много, в пределах сотни. Имеется возможность строить любые индексы, в т.ч. колоночные. Можно строить индексы под конкретные запросы (т.к. обновление производится в отсоединенных секциях, большое количество индексов не влияет на скорость обновления, из таблицы только читают). Как тут можно ускориться? Концептуально, т.с. |
20 мар 18, 11:34 [21270747] Ответить | Цитировать Сообщить модератору |
aleksrov Member Откуда: Сообщений: 948 |
uaggster, Вы же должны понимать, что при запросе isAсtive = 1 SQL не будет использовать никакой индекс, если у вас 100 лямов записей и грубо говоря 50 это isActive = 0 и 50 isActive = 1, SQL в любом случае будет делать скан, более того вы выбираете * (пропишите нормально столбцы, не звездочкой), т.е. если вым нужны все столбы покрывающий индекс вы тоже не сделаете. Если бы у вас таблица была широкая, а нужно было вам всего несколько столбцов можно было создать индекс для них, один фиг это был бы скан, но гораздо меньшего кол-ва страниц, у нас к примеру таких индексов много. У вас единственное место где можно немного оптимизировать это order by, но это оптимизация будет настолько ничтожной по сравнению со сканом всей таблицы, что разницы вы не заметите. |
20 мар 18, 12:43 [21271053] Ответить | Цитировать Сообщить модератору |
uaggster Member Откуда: Сообщений: 960 |
Таблица, на самом деле - широкая, сотни три полей. И битовых критериев - больше трёх (с десяток - точно). Теоретически, комбинация нескольких критериев дает довольно большую селективность, но всё равно в такой выборке, в типичном случае, вылазит в пределах миллиона записей. Выборки используются потом для построения каких-то агрегатов, т.е. это такое окно, что-ли. Проблема в том, что комбинаций этих критериев, в общем, чертова куча используется. Но, опять же, теоретически, я готов проанализировать, какие комбинации использовать, и построить что-то типа (DTA, isAсtive, isInvalid, isOverlapped, ...), ну, хотя бы некоторые запросы. Но неужели это единственный вариант? (и будет ли толк вообще). |
||
20 мар 18, 12:55 [21271111] Ответить | Цитировать Сообщить модератору |
aleks222
Guest |
Есть выход! Фильтрованный индекс, точнее - два. create index A1 on #t (DTA) where isAсtive = 1; create index A0 on #t (DTA) where isAсtive = 0; |
||
20 мар 18, 12:57 [21271126] Ответить | Цитировать Сообщить модератору |
TaPaK Member Откуда: Kiev Сообщений: 6801 |
uaggster, у вас секциоирование по какому-то полю, а выборка совсем по другим, на выхде обход всех секций, и допустим фильтрованый индекс на DTA isAсtive = 1 c INCLUDE нужных вам полей, летать эта штука всё равно не будет |
20 мар 18, 12:59 [21271136] Ответить | Цитировать Сообщить модератору |
aleks222
Guest |
1. Можно вычисляемое поле забабахать и фсе биты туды сложить. 2. Теоретически сервер умеет делать index intersection. Т.е. два фильтрованных индекса на каждое битовое поле имеют право на существование. |
||||
20 мар 18, 13:01 [21271143] Ответить | Цитировать Сообщить модератору |
aleksrov Member Откуда: Сообщений: 948 |
aleks222, По сути нужно будет создвть с десяток фильтрованных индексов с include как я понимаю всей таблицы, как было сказано это все ровно не взлетит, да и по месту, по сути копия таблицы будет, хотя читать 1\10 таблицы конечно быстрее будет. |
20 мар 18, 13:07 [21271161] Ответить | Цитировать Сообщить модератору |
ScareCrow Member Откуда: Белый город Сообщений: 17220 |
wat? |
||
20 мар 18, 13:59 [21271343] Ответить | Цитировать Сообщить модератору |
ScareCrow Member Откуда: Белый город Сообщений: 17220 |
очевидно индекс по DTA. Возможно фильтрованный индекс по DTA и isAсtive = 1 |
||
20 мар 18, 14:00 [21271347] Ответить | Цитировать Сообщить модератору |
uaggster Member Откуда: Сообщений: 960 |
Да секционирование, тут, в общем, не причем. Табличка порезана на секции по числу подразделений = числу кусков из которых она состыковывается. Это витрина. Так для загрузки удобнее. |
||
20 мар 18, 14:01 [21271351] Ответить | Цитировать Сообщить модератору |
TaPaK Member Откуда: Kiev Сообщений: 6801 |
как это не при чём? посмотрите в план вам понравится |
||||
20 мар 18, 14:03 [21271362] Ответить | Цитировать Сообщить модератору |
AlanDenton Member [скрыт] Откуда: Сообщений: 1004 |
CREATE INDEX ix ON #t (GRP, ID, N DESC) --INCLUDE () WHERE isAсtive = 1 SELECT * FROM ( SELECT *, rn = ROW_NUMBER() OVER (PARTITION BY GRP, ID ORDER BY N DESC) FROM #t WHERE DTA BETWEEN '20180101' AND '20180103' AND isAсtive = 1 ) t WHERE t.rn = 1 Отказ от TIES убирает лишнюю сортировку. Покрывающий индекс создаете под свои нужны. |
20 мар 18, 14:07 [21271380] Ответить | Цитировать Сообщить модератору |
AlanDenton Member [скрыт] Откуда: Сообщений: 1004 |
Раз уж у вас данные секционированные, то есть вариант использовать predicate pushdown:CREATE INDEX ix1 ON #t (DTA) INCLUDE (GRP) WHERE isAсtive = 1 CREATE INDEX ix2 ON #t (GRP, ID, N DESC) INCLUDE (DTA) WHERE isAсtive = 1 DECLARE @GRP_Start INT , @GRP_End INT SELECT @GRP_Start = MIN(GRP) , @GRP_End = MAX(GRP) FROM #t WITH(INDEX(ix1)) WHERE DTA BETWEEN '20180101' AND '20180103' AND isAсtive = 1 SELECT GRP, ID, N, DTA FROM ( SELECT GRP, ID, N, DTA, rn = ROW_NUMBER() OVER (PARTITION BY GRP, ID ORDER BY N DESC) FROM #t WITH(INDEX(ix2)) WHERE DTA BETWEEN '20180101' AND '20180103' AND isAсtive = 1 AND GRP BETWEEN @GRP_Start AND @GRP_End ) t WHERE t.rn = 1 OPTION(RECOMPILE) это как вариант. Если данных очень много, то, возможно, секционированный колумнстор. Либо добавить фильтрованный некластерный колумнсторный индекс, чтобы батч режим мог применяться (от SQL Server 2016) |
20 мар 18, 14:18 [21271416] Ответить | Цитировать Сообщить модератору |
.Евгений Member Откуда: Сообщений: 654 |
Вам нужна транзакционность? Писатель один или много? Если нет, то не уйти ли вам от RDBMS (по крайней мере, в части хранения и фильтрации)? |
||
20 мар 18, 15:02 [21271676] Ответить | Цитировать Сообщить модератору |
TaPaK Member Откуда: Kiev Сообщений: 6801 |
какой-то набор слов не относящйся к ТС никак |
||||
20 мар 18, 15:04 [21271702] Ответить | Цитировать Сообщить модератору |
.Евгений Member Откуда: Сообщений: 654 |
Создается DLL с Dictionary и двумя функциями CLR (запись нового элемента и поиск по фильтру). Вам все равно непонятно, как это относится к ТС? |
||||
20 мар 18, 15:33 [21271876] Ответить | Цитировать Сообщить модератору |
aleks222
Guest |
Include, в данном случае, НЕ НУЖНО. После Index Intersection будет Lookup кластерного. |
||
20 мар 18, 15:36 [21271888] Ответить | Цитировать Сообщить модератору |
TaPaK Member Откуда: Kiev Сообщений: 6801 |
это даже не смешно, хотя нет - смешно |
||||
20 мар 18, 15:38 [21271894] Ответить | Цитировать Сообщить модератору |
Владислав Колосов Member Откуда: Сообщений: 8350 |
uaggster,
Считайте агрегаты заранее, больше никак. Так кубы работают. |
||
20 мар 18, 18:25 [21272475] Ответить | Цитировать Сообщить модератору |
uaggster Member Откуда: Сообщений: 960 |
К сожалению, не получится. Это витрина данных, которая оперативно обновляется. Некоторые куски - несколько раз в час, некоторые - несколько раз в сутки. Заливка ведется в отсоединенные секции, сама таблица доступна непрерывно, но только на чтение. Сервер 2016 sp1, к сожалению - стандарт, хотя, в принципе, начиная с sp1 - всё нужное есть. Сейчас на таблица представляет из себя rowstore, на котором куча выровненных по секциям некластерных индексов, и один секционированный же некластерный колумнстор. Проблемой является то, что по таблице считают не только (и не столько) агрегаты, а чаще ищут конкретные записи (фильтруют). Куб, как я понимаю, так оперативно пересчитывать не получится. |
||||
20 мар 18, 21:34 [21272819] Ответить | Цитировать Сообщить модератору |
alexeyvg Member Откуда: Moscow Сообщений: 31784 |
И, может, обновляются данные инкрементно по датам? А то, если концептуально, может, секционировать лучше по датам, в том числе рассмотреть вариант с присоединением секций? |
||
20 мар 18, 21:54 [21272840] Ответить | Цитировать Сообщить модератору |
Владислав Колосов Member Откуда: Сообщений: 8350 |
uaggster,
Почему не получится? У всех получается, а у Вас - нет :) Для перерасчета агрегатов используется Change Tracking. Куб именно так и перерасчитывается по расписанию. Там всё не перезаливается каждый раз. А фильтры разве выбирают миллионы записей? Фильтры имеют смысл при высокой селективности. Если операторы у вас сливают эксели-миллионники, то что-то идёт не так. Значит у них нет подходящих инструментов. |
||
21 мар 18, 12:05 [21273830] Ответить | Цитировать Сообщить модератору |
uaggster Member Откуда: Сообщений: 960 |
А вот, кстати, вопрос. А как в этом случае Change Tracking сработает? Я ж меняю секцию целиком. Т.е. создаю из скрипта таблицу, аналогичную секции, заливаю туда данные, а потом делаю свитч партишн. Понятно, что фактически измененных записей там дай бог, если 1%, но это же другие записи! (Я не знаю, ROWID другой и всё такое). |
||||
26 мар 18, 08:08 [21284973] Ответить | Цитировать Сообщить модератору |
Все форумы / Microsoft SQL Server | ![]() |