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

В ходе работы образовалась временная таблица
#Table(id int PRIMARY KEY NONCLUSTERED, priznak_id int, cur_sum float, total_sum float)
CREATE CLUSTERED INDEX #IX_Table ON #Table(id, priznak_id)

В этой таблице порядка 300 тыс. записей. Заполнены все поля, кроме последнего. Последнее поле для строки с id=ID должно содержать сумму всех cur_sum где id<=ID при соответствующем priznak_id.

Решение, которое я все же получил, работает 70-80 минут... Можно найти что-то поинтереснее?
5 апр 16, 05:47    [19016940]     Ответить | Цитировать Сообщить модератору
 Re: Суммарный столбец  [new]
Glory
Member

Откуда:
Сообщений: 104760
А
Последнее поле для строки с id=ID должно содержать сумму всех cur_sum где id<=ID при соответствующем priznak_id.

Нарастающий итог что ли ?
5 апр 16, 08:17    [19017049]     Ответить | Цитировать Сообщить модератору
 Re: Суммарный столбец  [new]
А
Guest
Да, но для каждого priznak_id
5 апр 16, 08:31    [19017072]     Ответить | Цитировать Сообщить модератору
 Re: Суммарный столбец  [new]
Glory
Member

Откуда:
Сообщений: 104760
А
Да, но для каждого priznak_id

И что это меняет ?
5 апр 16, 08:35    [19017085]     Ответить | Цитировать Сообщить модератору
 Re: Суммарный столбец  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31432
А
Решение, которое я все же получил, работает 70-80 минут...
Вы его опубликуйте хотя бы.
А то трудно ответить на вопрос "есть ли решение быстрее неизвестного".
5 апр 16, 09:01    [19017189]     Ответить | Цитировать Сообщить модератору
 Re: Суммарный столбец  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31432
А
В ходе работы образовалась временная таблица
#Table(id int PRIMARY KEY NONCLUSTERED, priznak_id int, cur_sum float, total_sum float)
CREATE CLUSTERED INDEX #IX_Table ON #Table(id, priznak_id)
Индексы - жесть :-)
Поле id уникально, но зачем то ещё сделан кластерный неуникальный индекс по id, priznak_id
Лучше сделать кластерный ПК по id, будет то же самое, но без лишних накладных расходов.
5 апр 16, 09:05    [19017195]     Ответить | Цитировать Сообщить модератору
 Re: Суммарный столбец  [new]
А
Guest
Сделано было так:

update #Table set total_sum=
(select SUM(t.cur_sum)
from #Table as t
where t.priznak_id = #Table.priznak_id
and t.id <= #Table.id)

И вот это работает больше часа при 300 тыс. записей
5 апр 16, 09:22    [19017282]     Ответить | Цитировать Сообщить модератору
 Re: Суммарный столбец  [new]
iljy
Member

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

версия сервера какая? Если 2012+, то смотрите на оконную функцию SUM, если ниже - то быстрее всего будет SQLCLR-функция, в ней читаете отсортированные данные и считаете накопительный итог.
5 апр 16, 10:27    [19017531]     Ответить | Цитировать Сообщить модератору
 Re: Суммарный столбец  [new]
TaPaK
Member

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

версия сервера какая? Если 2012+, то смотрите на оконную функцию SUM
а чё с 2012+?
5 апр 16, 10:31    [19017552]     Ответить | Цитировать Сообщить модератору
 Re: Суммарный столбец  [new]
_djХомяГ
Guest
TaPaK
iljy
А,

версия сервера какая? Если 2012+, то смотрите на оконную функцию SUM
а чё с 2012+?

Очевидно потому что с 2012+ поддерживается фрейминг и в суммировании можно ставить order by
5 апр 16, 10:34    [19017563]     Ответить | Цитировать Сообщить модератору
 Re: Суммарный столбец  [new]
А
Guest
MSSQL 2005, обновиться дальше не получается, не все программы поддерживают новые версии MSSQL. А функцию сейчас посмотрю.
5 апр 16, 10:35    [19017569]     Ответить | Цитировать Сообщить модератору
 Re: Суммарный столбец  [new]
Glory
Member

Откуда:
Сообщений: 104760
А
MSSQL 2005, обновиться дальше не получается, не все программы поддерживают новые версии MSSQL. А функцию сейчас посмотрю.

Попробуйте обыкновенный курсор с апдейтом по месту
5 апр 16, 10:36    [19017573]     Ответить | Цитировать Сообщить модератору
 Re: Суммарный столбец  [new]
iljy
Member

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

курсор тоже можно, но сильно дольше получится. Как ни странно, CLR они реализовали эффективнее, чем курсоры или обычные UDF.
5 апр 16, 10:39    [19017597]     Ответить | Цитировать Сообщить модератору
 Re: Суммарный столбец  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31432
А
Сделано было так:

update #Table set total_sum=
(select SUM(t.cur_sum)
from #Table as t
where t.priznak_id = #Table.priznak_id
and t.id <= #Table.id)

И вот это работает больше часа при 300 тыс. записей
А создать индекс не поможет? Неужели будет медленнее курсора или CLR?
CREATE NONCLUSTERED INDEX #IX_Table ON #Table
(
    priznak_id,
    id
)
INCLUDE (cur_sum)
с одновременной заменой
#Table(id int PRIMARY KEY NONCLUSTERED, priznak_id int, cur_sum float, total_sum float)
CREATE CLUSTERED INDEX #IX_Table ON #Table(id, priznak_id)
на
#Table(id int PRIMARY KEY CLUSTERED, priznak_id int, cur_sum float, total_sum float)
5 апр 16, 11:12    [19017784]     Ответить | Цитировать Сообщить модератору
 Re: Суммарный столбец  [new]
iljy
Member

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

сейчас заморачиваться экспериментами лень, но когда-то с такой задачей сталкивался и проверял быстродействие. Штука в том, что реализация нарастающего итога обычной группировкой имеет квадратичную сложность, и при большом количестве записей (а 300т - это уже прилично) этот квадрат кроет накладные расходы от CLR и даже курсора (а у них сложность при грамотных индексах четко линейная) как бык овцу.
5 апр 16, 11:29    [19017903]     Ответить | Цитировать Сообщить модератору
 Re: Суммарный столбец  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31432
А
И вот это работает больше часа при 300 тыс. записей
Интересно, почему. У меня работает 44 секунды с вашими индексами, 22 сек с моими.
Слабенький у вас сервер :-(
5 апр 16, 11:30    [19017910]     Ответить | Цитировать Сообщить модератору
 Re: Суммарный столбец  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31432
iljy
Штука в том, что реализация нарастающего итога обычной группировкой имеет квадратичную сложность, и при большом количестве записей (а 300т - это уже прилично) этот квадрат кроет накладные расходы от CLR и даже курсора (а у них сложность при грамотных индексах четко линейная) как бык овцу.
В принципе да, но пока не проверишь, уверенным быть нельзя, может, сиквел правильно строит план именно как нарастающий итог?

Теоретически да, в принципе по затратам задача линейная, затраты должны быть равны простой выборке с сортировкой.
5 апр 16, 11:33    [19017931]     Ответить | Цитировать Сообщить модератору
 Re: Суммарный столбец  [new]
iljy
Member

Откуда:
Сообщений: 8711
alexeyvg
iljy
Штука в том, что реализация нарастающего итога обычной группировкой имеет квадратичную сложность, и при большом количестве записей (а 300т - это уже прилично) этот квадрат кроет накладные расходы от CLR и даже курсора (а у них сложность при грамотных индексах четко линейная) как бык овцу.
В принципе да, но пока не проверишь, уверенным быть нельзя, может, сиквел правильно строит план именно как нарастающий итог?

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


не-а, не строит, до 2008R2 включительно. А дальше уже неинтересно :)
5 апр 16, 11:42    [19017993]     Ответить | Цитировать Сообщить модератору
 Re: Суммарный столбец  [new]
o-o
Guest
alexeyvg
iljy
Штука в том, что реализация нарастающего итога обычной группировкой имеет квадратичную сложность, и при большом количестве записей (а 300т - это уже прилично) этот квадрат кроет накладные расходы от CLR и даже курсора (а у них сложность при грамотных индексах четко линейная) как бык овцу.
В принципе да, но пока не проверишь, уверенным быть нельзя, может, сиквел правильно строит план именно как нарастающий итог?

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

можно еще верить тому, кому можно верить.
либо один раз запустить предложенный автором код,
убедиться и согласиться
Itzik Be-Gan
As I mentioned, set-based solutions tend to be much faster, but in some cases the cursor solution is faster.
Those cases tend to be calculations that, if done by processing one row at a time in a certain order,
involve much less data access compared to the way the version of SQL Server you’re working
with optimizes corresponding set-based solutions.
One such example is computing running aggregates in versions of SQL prior to SQL Server 2012.
I provided a very efficient set-based solution to running aggregates in Chapter 7, “Beyond the Fundamentals of Querying,”
using enhanced window aggregate functions in SQL Server 2012.
However, if you’re using an earlier version of SQL Server, set-based solutions to running aggregates
don’t get optimized very well;
they involve multiple scans of the data.

Optimization is outside the scope of this book, so I won’t go into detail here; all you need to know here is that
cursor solutions to running aggregates involve only one scan of the data, and therefore can be faster
than set-based solutions on pre-2012 versions of SQL Server.

Microsoft SQL Server 2012 T-SQL Fundamentals By Itzik Ben-Gan
5 апр 16, 12:11    [19018194]     Ответить | Цитировать Сообщить модератору
 Re: Суммарный столбец  [new]
А
Guest
Помните, в начале дискуссии была фраза что меняет то, что нужен расчет внутри каждой группы?
Для тех, кому интересно, на что это может влиять посмотрите это [url=]https://blogs.technet.microsoft.com/isv_team/2011/07/26/1080/[/url]
5 апр 16, 13:56    [19018922]     Ответить | Цитировать Сообщить модератору
 Re: Суммарный столбец  [new]
iljy
Member

Откуда:
Сообщений: 8711
А
Помните, в начале дискуссии была фраза что меняет то, что нужен расчет внутри каждой группы?
Для тех, кому интересно, на что это может влиять посмотрите это [url=]https://blogs.technet.microsoft.com/isv_team/2011/07/26/1080/[/url]


ну да, если в групп много, то записей в каждой из них мало и стоимость селфджойна тоже приближается к линейной, и тут накладные расходы на курсор начинают перекрывать затраты на цикл.
5 апр 16, 14:02    [19018965]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить