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

Откуда: Волгоград
Сообщений: 15
Простой пример.
Имеется 2 таблицы, 1 ко многим
T1
Id1 int primary key
N1 varchar(500)

T2
Id2 int primary key
Id1 int
Data datetime
Rekv1, Rekv2 и т.д.

На момент времени @Data1 необходимо иметь значения реквизитов T2 для всех ID по таблице T1

В лоб, без денормализации, это решается UDF с 3 Join T2 самой на себя

Select * from T2 T JOIN
(Select Id1, Max(Id2) as Id2
from T2 A JOIN
( Select Id1, Max(Data) as Data from T2 Where Data < @Data1 Group by id1) B On A.Id1 = B.Id1 and A.Data = B.Data)
group by A.Id2
)M On T.Id1 = M.Id1 and T.Id2 = M.Id2

с индексом по T2: Id1 + Data

Пример - договора и доп. соглашения.

При большом количестве данных нагрузка может быть значительна.
Может, у кого-нибудь есть альтернативные реализации структуры, пусть и ненормализованные?
24 ноя 17, 10:52    [20980138]     Ответить | Цитировать Сообщить модератору
 Re: Ускоряющая денормализация  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2288
Itille,

Для какой версии сиквела?
24 ноя 17, 11:02    [20980163]     Ответить | Цитировать Сообщить модератору
 Re: Ускоряющая денормализация  [new]
Itille
Member

Откуда: Волгоград
Сообщений: 15
MS SQL 2012. Но если есть в 2016, то расскажите или киньте ссылку, лишним не будет.
24 ноя 17, 11:07    [20980175]     Ответить | Цитировать Сообщить модератору
 Re: Ускоряющая денормализация  [new]
buser
Member

Откуда: Санкт-Петербург
Сообщений: 4075
Itille,
select top(1) with ties * from T2 where Data < @Data1 order by ROW_NUMBER() over(partition by Id1 order by Data desc) asc
24 ноя 17, 11:08    [20980179]     Ответить | Цитировать Сообщить модератору
 Re: Ускоряющая денормализация  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2288
Itille
MS SQL 2012. Но если есть в 2016, то расскажите или киньте ссылку, лишним не будет.


тогда изайте LAST_VALUE
24 ноя 17, 11:11    [20980189]     Ответить | Цитировать Сообщить модератору
 Re: Ускоряющая денормализация  [new]
Itille
Member

Откуда: Волгоград
Сообщений: 15
buser, спасибо!
24 ноя 17, 11:33    [20980277]     Ответить | Цитировать Сообщить модератору
 Re: Ускоряющая денормализация  [new]
Владислав Колосов
Member

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

без сортировки, похоже, не обойтись. Если селективность запроса низкая, т.е. T1 содержит большой объем данных, то Вам поможет только прокачка "железа".
Кластерный индекс по t2.Data тоже бы помог, но это не подходит, судя по структуре.

Попробуйте составить календарь заранее просчитать суммы на каждый день (та самая "денормализация"). Отчет летать будет.
24 ноя 17, 14:26    [20981067]     Ответить | Цитировать Сообщить модератору
 Re: Ускоряющая денормализация  [new]
Itille
Member

Откуда: Волгоград
Сообщений: 15
buser, посмотрел, немного не так, правильно:

select top(1) with ties * from T2 where Data < @Data1 order by ROW_NUMBER() over(partition by Id1 order by Data desc, Id2 desc) asc

в Вашем запросе не учтена возможность 2 записей с одинаковой датой.
Всё равно, спасибо!
6 дек 17, 13:52    [21010835]     Ответить | Цитировать Сообщить модератору
 Re: Ускоряющая денормализация  [new]
iap
Member

Откуда: Москва
Сообщений: 44962
Itille
buser, посмотрел, немного не так, правильно:

select top(1) with ties * from T2 where Data < @Data1 order by ROW_NUMBER() over(partition by Id1 order by Data desc, Id2 desc) asc

в Вашем запросе не учтена возможность 2 записей с одинаковой датой.
Всё равно, спасибо!
А в вашем учтена, да?
Думаю, вам надо заменить ROW_NUMBER() на RANK()
6 дек 17, 14:17    [21011001]     Ответить | Цитировать Сообщить модератору
 Re: Ускоряющая денормализация  [new]
Itille
Member

Откуда: Волгоград
Сообщений: 15
iap, согласен,что не указал, что ID2 identity(1б1)
6 дек 17, 14:59    [21011238]     Ответить | Цитировать Сообщить модератору
 Re: Ускоряющая денормализация  [new]
Itille
Member

Откуда: Волгоград
Сообщений: 15
iap,p, согласен,что не указал, что ID2 identity(1,1)

Кстати, поигрался на реальных данных, есть ощущение, что конструкция из 3 join работает несколько быстрее...
6 дек 17, 15:03    [21011258]     Ответить | Цитировать Сообщить модератору
 Re: Ускоряющая денормализация  [new]
buser
Member

Откуда: Санкт-Петербург
Сообщений: 4075
Itille, да, очень даже может быть... а вот еще вопрос - назачем Max(Id2) в купе с Max(Data)? БардачЁк в данных или бизнеспроцессах? Или просто перестраховка?
6 дек 17, 22:54    [21013031]     Ответить | Цитировать Сообщить модератору
 Re: Ускоряющая денормализация  [new]
982183
Member

Откуда:
Сообщений: 1358
Itille
На момент времени @Data1 необходимо иметь значения реквизитов T2 для всех ID по таблице T1

А зачем нам в данном случае T1?
Там нет интересующих вас данных.
Почему нельзя взять ID из T2.Id1
7 дек 17, 04:23    [21013355]     Ответить | Цитировать Сообщить модератору
 Re: Ускоряющая денормализация  [new]
982183
Member

Откуда:
Сообщений: 1358
Дошло. "Доп соглашений" может и не быть...
Но в Т1 нет и даты...
7 дек 17, 04:25    [21013356]     Ответить | Цитировать Сообщить модератору
 Re: Ускоряющая денормализация  [new]
982183
Member

Откуда:
Сообщений: 1358
Всё. Совсем дошло.
Вывести все допсоглашения по всем договорам в которых есть допсоглашения до @Data1
7 дек 17, 08:13    [21013443]     Ответить | Цитировать Сообщить модератору
 Re: Ускоряющая денормализация  [new]
982183
Member

Откуда:
Сообщений: 1358
В качестве ненормализованного бреда.
Заведи в T1 дополнительное поле "Дата последнего допсоглашения"
И получится один Update и один Select c одним join
7 дек 17, 08:17    [21013451]     Ответить | Цитировать Сообщить модератору
 Re: Ускоряющая денормализация  [new]
Itille
Member

Откуда: Волгоград
Сообщений: 15
buser, данные ведутся от царя гороха, плюс пользователи бывают замечательные, есть ограничения на редактирование записей ( в течение суток от создания), после чего вменяется для корректировки создавать новую запись.
7 дек 17, 08:50    [21013528]     Ответить | Цитировать Сообщить модератору
 Re: Ускоряющая денормализация  [new]
aleks222
Guest
Select * 
   from T2 T 
   JOIN (Select Id1, Max(Id2) as Id2 
           from T2 A 
                JOIN ( Select Id1, Max(Data) as Data from T2 Where Data < @Data1 Group by id1) B On A.Id1 = B.Id1 and A.Data = B.Data)
           group by A.Id2 ) M On T.Id1 = M.Id1 and T.Id2 = M.Id2

-- эквивалент
Select m.* 
   from ( select distinct Id1, Id2 from T2 ) as T 
   cross apply ( select top(1) * from T2 Where Data < @Data1 and Id1 = T.Id1 and Id2 = T.Id2 order by Data desc, Id2 desc ) as m

-- из которого видно, насколько идиотичен исходный запрос (обработка ВСЕЙ таблицы, когда реально надо только часть )
-- для ускорения 
-- select distinct Id1, Id2 from T2 
-- можно материализовать (Indexed View)
7 дек 17, 09:23    [21013591]     Ответить | Цитировать Сообщить модератору
 Re: Ускоряющая денормализация  [new]
Itille
Member

Откуда: Волгоград
Сообщений: 15
aleks222, во - первых, Ваш запрос неправильный.

Правильно -

Select m.*
from ( select distinct Id1 from T2 ) as T
cross apply ( select top(1) * from T2 Where Data < @Data1 and Id1 = T.Id1 order by Data desc, Id2 desc ) as m

А во-вторых, мне не очень нравится order by внутри функции.

Вариант buser больше по душе
7 дек 17, 15:46    [21015514]     Ответить | Цитировать Сообщить модератору
 Re: Ускоряющая денормализация  [new]
iap
Member

Откуда: Москва
Сообщений: 44962
Itille
aleks222, во - первых, Ваш запрос неправильный.

Правильно -

Select m.*
from ( select distinct Id1 from T2 ) as T
cross apply ( select top(1) * from T2 Where Data < @Data1 and Id1 = T.Id1 order by Data desc, Id2 desc ) as m

А во-вторых, мне не очень нравится order by внутри функции.

Вариант buser больше по душе
Внутри какой-такой функции?

Вижу не ORDER BY, а TOP(1) ... ORDER BY.
7 дек 17, 16:06    [21015612]     Ответить | Цитировать Сообщить модератору
 Re: Ускоряющая денормализация  [new]
Cammomile
Member

Откуда:
Сообщений: 1025
WarAnt
Itille
MS SQL 2012. Но если есть в 2016, то расскажите или киньте ссылку, лишним не будет.


тогда изайте LAST_VALUE


А оно быстрее аплая с TOP (1) ORDER... DESC ?

Ни разу не пользовался такой штукой.
7 дек 17, 16:14    [21015646]     Ответить | Цитировать Сообщить модератору
 Re: Ускоряющая денормализация  [new]
Itille
Member

Откуда: Волгоград
Сообщений: 15
iap, я имел в виду подзапрос.

Сейчас поэксперементировал.
Могу сказать, что на реальных данных время выполнения хуже, чем даже у запроса в первом посте.
Где-то в 2-3 раза на 1 млн. записей.
Но это на конкретном массиве записей с конкретными значениями.
В принципе, сам вариант имеет право на существование.
7 дек 17, 16:21    [21015683]     Ответить | Цитировать Сообщить модератору
 Re: Ускоряющая денормализация  [new]
Itille
Member

Откуда: Волгоград
Сообщений: 15
На самом деле, есть подозрение, что все вышеприведенные варианты по эффективности зависят от среднего количества строк в таблице t2 на каждое значение t1.
Вышенаписанное (в предыдущем моем топике) верно для среднего значения около 2.
7 дек 17, 16:30    [21015734]     Ответить | Цитировать Сообщить модератору
 Re: Ускоряющая денормализация  [new]
aleks222
Guest
Itille
aleks222, во - первых, Ваш запрос неправильный.

Правильно -

Select m.*
from ( select distinct Id1 from T2 ) as T
cross apply ( select top(1) * from T2 Where Data < @Data1 and Id1 = T.Id1 order by Data desc, Id2 desc ) as m

А во-вторых, мне не очень нравится order by внутри функции.

Вариант buser больше по душе


Дык, страдалец, ты меня пугаешь своей замороченностью.

Select m.* 
   from T1 as T 
   cross apply ( select top(1) * from T2 Where Data < @Data1 and Id1 = T.Id1 order by Data desc, Id2 desc ) as m

+ индекс T2(Id1, Data, Id2)
7 дек 17, 18:22    [21016149]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить