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

Откуда:
Сообщений: 826
Коллеги, приветствую!
Есть вот такой список:
ИД, номер событие, класс события (0,1), состояние ().
Нужно вывести событие с максимальным номером, у которого класс = 1, либо если такового события нет - то с максимальным номером класса 0, и номер максимального события для данного ИД вообще (ну и само состояние и класс).
Номера - монотонно возрастающие.

Вот - модель:
drop table if exists #t;

Create table #t
(ID bigint not NULL,
N int not NULL,
isChangeState bit,
isOnOff bit
Constraint PK_t primary key Clustered (ID, N)
)

; With N as ( 
Select Top 1000000
ROW_NUMBER() over (order by (select 1/0)) Num
from
	master..spt_values a 
		cross join master..spt_values b
		cross join master..spt_values c
)
insert into #t
Select 
Num % 220000 + 1 ID
,Row_Number() over (partition by Num % 220000 + 1 order by Num) N 
,(Cast(Rand(Num) * 100000000 as int) % 10) % 2 isChangeState
,(Cast(Rand(Num) * 10000000 as int) % 10) % 2 isOnOff
from N
-- очевидное решение
;With s as
(
Select
*,
ROW_NUMBER() over (partition by ID order by isChangeState DESC, N DESC) ord
,max(N) over (partition by ID) NN
from #t
)
Select ID, N, isChangeState, isOnOff, NN from s
	where ord = 1


Модель не очень хорошая, т.к. событий у одного id может быть от 1 до 100, примерно, но среднее - около 5, я просто не знаю, как нормально засеять модель.

Очевидное решение - у меня есть.
Но имеется проблема, оно конкретно подтормаживает.
Дело в том, что в списке порядка 10 млрд. записей, и порядка 100 млн. уникальных ID.
Сервер - 16SP2.

Посоветуйте, что можно сделать?
Если нужно, могу на список построить колумнстор или, теоретически, любой индекс.
20 июл 18, 10:20    [21587504]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать быстрый запрос  [new]
invm
Member

Откуда: Москва
Сообщений: 9344
uaggster
Посоветуйте, что можно сделать?
Завести служебную таблицу для хранения агрегированных данных и поддерживать ее триггером.

Если же хочется все время выковыривать требуемое запросом, то можно, например, так:
create unique index IX_#t__1 on #t (ID, N desc) include (isChangeState, IsOnOff) where isChangeState = 1;
create unique index IX_#t__0 on #t (ID, N desc) include (isChangeState, IsOnOff) where isChangeState = 0;
with a as
(
 select
  *, row_number() over (partition by ID order by N desc) as rn
 from
  #t
 where
  isChangeState = 1
),
b as
(
 select
  *, row_number() over (partition by ID order by N desc) as rn
 from
  #t
 where
  isChangeState = 0
)
select
 isnull(a.ID, b.ID), isnull(a.N, b.N), isnull(a.isChangeState, b.isChangeState), isnull(a.isOnOff, b.isOnOff),
 case when isnull(a.N, 0) > isnull(b.N, 0) then a.N else b.N end
from
 (select * from a where rn = 1) a full join
 (select * from b where rn = 1) b on b.ID = a.ID;

В конечном итоге, затраты на индексы и запрос с их участием окажутся выше затрат на поддержание таблицы с агрегатами.
20 июл 18, 11:53    [21588024]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать быстрый запрос  [new]
uaggster
Member

Откуда:
Сообщений: 826
invm,
from
 (select * from a where rn = 1) a full join
 (select * from b where rn = 1) b on b.ID = a.ID;

Что-то я сомневаюсь, что самосоединение на миллиарде то записей нормально будет работать.
Хотя я попробую, конечно.
20 июл 18, 15:10    [21589118]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать быстрый запрос  [new]
TaPaK
Member

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

автор
100 млн. уникальных ID.

т.е. вам в каждий момент времени(запрос) нужно выдать 100кк записей?
20 июл 18, 15:17    [21589155]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать быстрый запрос  [new]
uaggster
Member

Откуда:
Сообщений: 826
TaPaK, нет.
Просто запрос на формирование некой вторичной выборки из хранилища, где регистрируются события от приборов.
Это хранилище живет своей жизнью, секции обновляются, удаляются и т.д.
Время от времени мне нужно материализовать такую выборку в отдельную базу.
И с ней уже в дальнейшем работают... ну, дофига кто.

Я ее выгружаю в отдельную табличку, а потом переключением контекста убиваю предыдущую (не совсем так, там еще архивные таблицы есть, но не суть).
Проблема в том, что обрабатывается она долго.
20 июл 18, 16:04    [21589332]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать быстрый запрос  [new]
ATI.HeNRy
Member

Откуда:
Сообщений: 23
uaggster
Дело в том, что в списке порядка 10 млрд. записей, и порядка 100 млн. уникальных ID.
Проблема в том, что обрабатывается она долго.

Вы же говорите среднее 5, а если поделить одно на другое то 100 получается.
И долго - это сколько минут.
24 июл 18, 16:52    [21598888]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать быстрый запрос  [new]
982183
Member

Откуда: VL
Сообщений: 3352
uaggster
Просто запрос на формирование некой вторичной выборки из хранилища, где регистрируются события от приборов.

Довольно распространенная задача.
Только "Вторичню выборку" надо материализовать не "Время от времени" а постоянно/периодически.
25 июл 18, 02:29    [21600007]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать быстрый запрос  [new]
uaggster
Member

Откуда:
Сообщений: 826
ATI.HeNRy, около 20 минут.
26 июл 18, 08:54    [21603172]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать быстрый запрос  [new]
ATI.HeNRy
Member

Откуда:
Сообщений: 23
With s as (
		Select 
		ID
		,isChangeState
		,N=max(N)
		from #t
		GROUP BY ID,isChangeState
		)
,s2 as (
		Select 
		ID
		,ismax=max(cast (isChangeState as tinyint))
		,NN=max(N)
		from #t
		GROUP BY ID
		)
Select 
t.*
,nn 
--INTO #rz
FROM #t t
INNER JOIN s2
	on t.id=s2.id
INNER JOIN s
	on t.id=s.id
		and t.n = s.n
		and t.isChangeState= s2.ismax
26 июл 18, 13:15    [21604138]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить