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

Откуда:
Сообщений: 765
Коллеги приветствую!
Помогите советом.

Имеется большая таблица (до миллиарда записей не дотягивает, но плюс-минус поллаптя где то так), которая содержит последовательности записей в разрезе неких 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]     Ответить | Цитировать Сообщить модератору
 Re: Помогите ускорить выборки из последовательного списка  [new]
aleksrov
Member

Откуда:
Сообщений: 948
uaggster,
Вы же должны понимать, что при запросе isAсtive = 1 SQL не будет использовать никакой индекс, если у вас 100 лямов записей и грубо говоря 50 это isActive = 0 и 50 isActive = 1, SQL в любом случае будет делать скан, более того вы выбираете * (пропишите нормально столбцы, не звездочкой), т.е. если вым нужны все столбы покрывающий индекс вы тоже не сделаете.
Если бы у вас таблица была широкая, а нужно было вам всего несколько столбцов можно было создать индекс для них, один фиг это был бы скан, но гораздо меньшего кол-ва страниц, у нас к примеру таких индексов много.
У вас единственное место где можно немного оптимизировать это order by, но это оптимизация будет настолько ничтожной по сравнению со сканом всей таблицы, что разницы вы не заметите.
20 мар 18, 12:43    [21271053]     Ответить | Цитировать Сообщить модератору
 Re: Помогите ускорить выборки из последовательного списка  [new]
uaggster
Member

Откуда:
Сообщений: 765
aleksrov
uaggster,
Вы же должны понимать, что при запросе isAсtive = 1 SQL не будет использовать никакой индекс, если у вас 100 лямов записей и грубо говоря 50 это isActive = 0 и 50 isActive = 1, SQL в любом случае будет делать скан, более того вы выбираете * (пропишите нормально столбцы, не звездочкой), т.е. если вым нужны все столбы покрывающий индекс вы тоже не сделаете.
Если бы у вас таблица была широкая, а нужно было вам всего несколько столбцов можно было создать индекс для них, один фиг это был бы скан, но гораздо меньшего кол-ва страниц, у нас к примеру таких индексов много.
У вас единственное место где можно немного оптимизировать это order by, но это оптимизация будет настолько ничтожной по сравнению со сканом всей таблицы, что разницы вы не заметите.

Таблица, на самом деле - широкая, сотни три полей.
И битовых критериев - больше трёх (с десяток - точно).
Теоретически, комбинация нескольких критериев дает довольно большую селективность, но всё равно в такой выборке, в типичном случае, вылазит в пределах миллиона записей.
Выборки используются потом для построения каких-то агрегатов, т.е. это такое окно, что-ли.
Проблема в том, что комбинаций этих критериев, в общем, чертова куча используется.
Но, опять же, теоретически, я готов проанализировать, какие комбинации использовать, и построить что-то типа (DTA, isAсtive, isInvalid, isOverlapped, ...), ну, хотя бы некоторые запросы. Но неужели это единственный вариант?
(и будет ли толк вообще).
20 мар 18, 12:55    [21271111]     Ответить | Цитировать Сообщить модератору
 Re: Помогите ускорить выборки из последовательного списка  [new]
aleks222
Guest
aleksrov
uaggster,
Вы же должны понимать, что при запросе isAсtive = 1 SQL не будет использовать никакой индекс, если у вас 100 лямов записей и грубо говоря 50 это isActive = 0 и 50 isActive = 1, SQL в любом случае будет делать скан, более того вы выбираете * (пропишите нормально столбцы, не звездочкой), т.е. если вым нужны все столбы покрывающий индекс вы тоже не сделаете.
Если бы у вас таблица была широкая, а нужно было вам всего несколько столбцов можно было создать индекс для них, один фиг это был бы скан, но гораздо меньшего кол-ва страниц, у нас к примеру таких индексов много.
У вас единственное место где можно немного оптимизировать это order by, но это оптимизация будет настолько ничтожной по сравнению со сканом всей таблицы, что разницы вы не заметите.


Есть выход! Фильтрованный индекс, точнее - два.

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]     Ответить | Цитировать Сообщить модератору
 Re: Помогите ускорить выборки из последовательного списка  [new]
TaPaK
Member

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

у вас секциоирование по какому-то полю, а выборка совсем по другим, на выхде обход всех секций, и допустим фильтрованый индекс на DTA isAсtive = 1 c INCLUDE нужных вам полей, летать эта штука всё равно не будет
20 мар 18, 12:59    [21271136]     Ответить | Цитировать Сообщить модератору
 Re: Помогите ускорить выборки из последовательного списка  [new]
aleks222
Guest
uaggster
aleksrov
uaggster,
Вы же должны понимать, что при запросе isAсtive = 1 SQL не будет использовать никакой индекс, если у вас 100 лямов записей и грубо говоря 50 это isActive = 0 и 50 isActive = 1, SQL в любом случае будет делать скан, более того вы выбираете * (пропишите нормально столбцы, не звездочкой), т.е. если вым нужны все столбы покрывающий индекс вы тоже не сделаете.
Если бы у вас таблица была широкая, а нужно было вам всего несколько столбцов можно было создать индекс для них, один фиг это был бы скан, но гораздо меньшего кол-ва страниц, у нас к примеру таких индексов много.
У вас единственное место где можно немного оптимизировать это order by, но это оптимизация будет настолько ничтожной по сравнению со сканом всей таблицы, что разницы вы не заметите.

Таблица, на самом деле - широкая, сотни три полей.
И битовых критериев - больше трёх (с десяток - точно).
Теоретически, комбинация нескольких критериев дает довольно большую селективность, но всё равно в такой выборке, в типичном случае, вылазит в пределах миллиона записей.
Выборки используются потом для построения каких-то агрегатов, т.е. это такое окно, что-ли.
Проблема в том, что комбинаций этих критериев, в общем, чертова куча используется.
Но, опять же, теоретически, я готов проанализировать, какие комбинации использовать, и построить что-то типа (DTA, isAсtive, isInvalid, isOverlapped, ...), ну, хотя бы некоторые запросы. Но неужели это единственный вариант?
(и будет ли толк вообще).


1. Можно вычисляемое поле забабахать и фсе биты туды сложить.
2. Теоретически сервер умеет делать index intersection. Т.е. два фильтрованных индекса на каждое битовое поле имеют право на существование.
20 мар 18, 13:01    [21271143]     Ответить | Цитировать Сообщить модератору
 Re: Помогите ускорить выборки из последовательного списка  [new]
aleksrov
Member

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

По сути нужно будет создвть с десяток фильтрованных индексов с include как я понимаю всей таблицы, как было сказано это все ровно не взлетит, да и по месту, по сути копия таблицы будет, хотя читать 1\10 таблицы конечно быстрее будет.
20 мар 18, 13:07    [21271161]     Ответить | Цитировать Сообщить модератору
 Re: Помогите ускорить выборки из последовательного списка  [new]
ScareCrow
Member

Откуда: Белый город
Сообщений: 16816
автор
Вы же должны понимать, что при запросе isAсtive = 1 SQL не будет использовать никакой индекс

wat?
20 мар 18, 13:59    [21271343]     Ответить | Цитировать Сообщить модератору
 Re: Помогите ускорить выборки из последовательного списка  [new]
ScareCrow
Member

Откуда: Белый город
Сообщений: 16816
автор
Where DTA between '20180101' and '20180103' and isAсtive = 1

очевидно индекс по DTA. Возможно фильтрованный индекс по DTA и isAсtive = 1
20 мар 18, 14:00    [21271347]     Ответить | Цитировать Сообщить модератору
 Re: Помогите ускорить выборки из последовательного списка  [new]
uaggster
Member

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

у вас секциоирование по какому-то полю, а выборка совсем по другим, на выхде обход всех секций, и допустим фильтрованый индекс на DTA isAсtive = 1 c INCLUDE нужных вам полей, летать эта штука всё равно не будет

Да секционирование, тут, в общем, не причем. Табличка порезана на секции по числу подразделений = числу кусков из которых она состыковывается.
Это витрина.
Так для загрузки удобнее.
20 мар 18, 14:01    [21271351]     Ответить | Цитировать Сообщить модератору
 Re: Помогите ускорить выборки из последовательного списка  [new]
TaPaK
Member

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

у вас секциоирование по какому-то полю, а выборка совсем по другим, на выхде обход всех секций, и допустим фильтрованый индекс на DTA isAсtive = 1 c INCLUDE нужных вам полей, летать эта штука всё равно не будет

Да секционирование, тут, в общем, не причем. Табличка порезана на секции по числу подразделений = числу кусков из которых она состыковывается.
Это витрина.
Так для загрузки удобнее.

как это не при чём? посмотрите в план вам понравится
20 мар 18, 14:03    [21271362]     Ответить | Цитировать Сообщить модератору
 Re: Помогите ускорить выборки из последовательного списка  [new]
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]     Ответить | Цитировать Сообщить модератору
 Re: Помогите ускорить выборки из последовательного списка  [new]
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]     Ответить | Цитировать Сообщить модератору
 Re: Помогите ускорить выборки из последовательного списка  [new]
.Евгений
Member

Откуда:
Сообщений: 493
uaggster

Вам нужна транзакционность? Писатель один или много?
Если нет, то не уйти ли вам от RDBMS (по крайней мере, в части хранения и фильтрации)?
20 мар 18, 15:02    [21271676]     Ответить | Цитировать Сообщить модератору
 Re: Помогите ускорить выборки из последовательного списка  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
.Евгений
uaggster

Вам нужна транзакционность? Писатель один или много?
Если нет, то не уйти ли вам от RDBMS (по крайней мере, в части хранения и фильтрации)?

какой-то набор слов не относящйся к ТС никак
20 мар 18, 15:04    [21271702]     Ответить | Цитировать Сообщить модератору
 Re: Помогите ускорить выборки из последовательного списка  [new]
.Евгений
Member

Откуда:
Сообщений: 493
TaPaK
.Евгений
пропущено...

Вам нужна транзакционность? Писатель один или много?
Если нет, то не уйти ли вам от RDBMS (по крайней мере, в части хранения и фильтрации)?

какой-то набор слов не относящйся к ТС никак

Создается DLL с Dictionary и двумя функциями CLR (запись нового элемента и поиск по фильтру). Вам все равно непонятно, как это относится к ТС?
20 мар 18, 15:33    [21271876]     Ответить | Цитировать Сообщить модератору
 Re: Помогите ускорить выборки из последовательного списка  [new]
aleks222
Guest
aleksrov
aleks222,

По сути нужно будет создвть с десяток фильтрованных индексов с include как я понимаю всей таблицы, как было сказано это все ровно не взлетит, да и по месту, по сути копия таблицы будет, хотя читать 1\10 таблицы конечно быстрее будет.


Include, в данном случае, НЕ НУЖНО.
После Index Intersection будет Lookup кластерного.
20 мар 18, 15:36    [21271888]     Ответить | Цитировать Сообщить модератору
 Re: Помогите ускорить выборки из последовательного списка  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
.Евгений
TaPaK
пропущено...

какой-то набор слов не относящйся к ТС никак

Создается DLL с Dictionary и двумя функциями CLR (запись нового элемента и поиск по фильтру). Вам все равно непонятно, как это относится к ТС?

это даже не смешно, хотя нет - смешно
20 мар 18, 15:38    [21271894]     Ответить | Цитировать Сообщить модератору
 Re: Помогите ускорить выборки из последовательного списка  [new]
Владислав Колосов
Member

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

автор
Как тут можно ускориться?


Считайте агрегаты заранее, больше никак. Так кубы работают.
20 мар 18, 18:25    [21272475]     Ответить | Цитировать Сообщить модератору
 Re: Помогите ускорить выборки из последовательного списка  [new]
uaggster
Member

Откуда:
Сообщений: 765
Владислав Колосов
uaggster,

автор
Как тут можно ускориться?


Считайте агрегаты заранее, больше никак. Так кубы работают.

К сожалению, не получится.
Это витрина данных, которая оперативно обновляется. Некоторые куски - несколько раз в час, некоторые - несколько раз в сутки. Заливка ведется в отсоединенные секции, сама таблица доступна непрерывно, но только на чтение.
Сервер 2016 sp1, к сожалению - стандарт, хотя, в принципе, начиная с sp1 - всё нужное есть.
Сейчас на таблица представляет из себя rowstore, на котором куча выровненных по секциям некластерных индексов, и один секционированный же некластерный колумнстор.
Проблемой является то, что по таблице считают не только (и не столько) агрегаты, а чаще ищут конкретные записи (фильтруют).
Куб, как я понимаю, так оперативно пересчитывать не получится.
20 мар 18, 21:34    [21272819]     Ответить | Цитировать Сообщить модератору
 Re: Помогите ускорить выборки из последовательного списка  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 30766
uaggster
Как это можно ускорить? Какие индексы имеет смысл построить? Какая конструкция запроса будет оптимальна в таком случае (тот, который показан - иллюстрация, какие выборки обычно требуются).
А фильтр по датам показан типичный? То есть фильтр по датам достаточно селективный, может, просто кластерного индекса по датам будет достаточно?
И, может, обновляются данные инкрементно по датам? А то, если концептуально, может, секционировать лучше по датам, в том числе рассмотреть вариант с присоединением секций?
20 мар 18, 21:54    [21272840]     Ответить | Цитировать Сообщить модератору
 Re: Помогите ускорить выборки из последовательного списка  [new]
Владислав Колосов
Member

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

автор
Это витрина данных, которая оперативно обновляется

Почему не получится? У всех получается, а у Вас - нет :) Для перерасчета агрегатов используется Change Tracking. Куб именно так и перерасчитывается по расписанию. Там всё не перезаливается каждый раз.

А фильтры разве выбирают миллионы записей? Фильтры имеют смысл при высокой селективности. Если операторы у вас сливают эксели-миллионники, то что-то идёт не так. Значит у них нет подходящих инструментов.
21 мар 18, 12:05    [21273830]     Ответить | Цитировать Сообщить модератору
 Re: Помогите ускорить выборки из последовательного списка  [new]
uaggster
Member

Откуда:
Сообщений: 765
Владислав Колосов
uaggster,

автор
Это витрина данных, которая оперативно обновляется

Почему не получится? У всех получается, а у Вас - нет :) Для перерасчета агрегатов используется Change Tracking. Куб именно так и перерасчитывается по расписанию. Там всё не перезаливается каждый раз.

А фильтры разве выбирают миллионы записей? Фильтры имеют смысл при высокой селективности. Если операторы у вас сливают эксели-миллионники, то что-то идёт не так. Значит у них нет подходящих инструментов.

А вот, кстати, вопрос.
А как в этом случае Change Tracking сработает?
Я ж меняю секцию целиком.
Т.е. создаю из скрипта таблицу, аналогичную секции, заливаю туда данные, а потом делаю свитч партишн.
Понятно, что фактически измененных записей там дай бог, если 1%, но это же другие записи!
(Я не знаю, ROWID другой и всё такое).
26 мар 18, 08:08    [21284973]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить