Нарастающий итог - сравнение производительности

В посте "Нарастающий итог в Денали" были рассмотрены основные способы вычисления нарастающего итога средствами Transact-SQL: 1) курсор; 2) джойн таблицы самой на себя; 3) подзапрос; 4) упорядоченная оконная сумма. 4-й способ является новой функциональностью вышедшего две недели назад CTP3 следующей версии SQL Server под кодовым названием Denali. Было бы интересно сравнить перечисленные способы с точки зрения производительности на объемах данных, более-менее заслуживающих внимания. В данном посте я выполню это упражнение на самой большой по количеству записей таблице любимой базы данных AdventureWorks2008R2.

 

image

Рис.1

 

image

Рис.2

 

Самой большой по количеству записей является таблица Sales.SalesOrderDetail. Существенными для целей теста полями в ней будут: SalesOrderID (для группирования), SalesOrderDetailID (сквозной ключ записей), UnitPrice (поле, по которому будет считаться нарастающий итог). В нижеприведенном тестировочном скрипте эти колонки переносятся в отдельную таблицу. Создаются 4 временные хранимые процедуры, каждая из которых реализует подсчет накопительного итога одним из вышеперечисленных способов. Процедура #Sposob1_Cursor соответствует Скрипту 5 предыдущего поста, #Sposob2_SelfJoin - Скрипту 7, #Sposob3_Subquery - Скрипту 8, #Sposob4_SumOverOrderedWindow - Скрипту 11. Единственное идейное отличие состоит в том, что там нарастающий итог считался вдоль всей таблицы Customer, а здесь я добавил в порядке эксперимента промежуточное группирование по полю SalesOrderID, т.е. нарастающий итог нарастает внутри каждого отдельного заказа. Как только наступает новый заказ, он сбрасывается и начинает нарастать с нуля по-новой. Временная процедура #header для чистоты эксперимента перед каждым испытанием приводит боевые и учебные патроны в исходное состояние.

 

use tempdb

 

if OBJECT_ID('dbo.SalesOrderDetails', 'U') is not null drop table dbo.SalesOrderDetails

select SalesOrderID, SalesOrderDetailID, UnitPrice, cast(null as money) as RunningTotal into SalesOrderDetails from [Adventure Works 2008R2].Sales.SalesOrderDetail

alter table SalesOrderDetails add primary key clustered (SalesOrderDetailID)

go

if OBJECT_ID('#header', 'P') is not null drop proc #header

go

create proc #header as

update SalesOrderDetails set RunningTotal = NULL

dbcc dropcleanbuffers

dbcc freeproccache

go

if OBJECT_ID('#Sposob1_Cursor', 'P') is not null drop proc #Sposob1_Cursor

go

create proc #Sposob1_Cursor as

exec #header

declare @cur cursor, @SalesOrderID int, @SalesOrderID_Prev int = 0, @Price money, @RunningTotal money

set @cur = cursor local keyset for select SalesOrderID, UnitPrice from SalesOrderDetails order by SalesOrderDetailID for update of RunningTotal

open @cur

set nocount on

while 1 = 1 begin

 fetch next from @cur into @SalesOrderID, @Price

 if @@FETCH_STATUS <> 0 break

 if @SalesOrderID <> @SalesOrderID_Prev set @RunningTotal = 0

 update SalesOrderDetails set RunningTotal = @RunningTotal where current of @cur

 select @RunningTotal += @Price, @SalesOrderID_Prev = @SalesOrderID

end

set nocount off

close @cur

deallocate @cur

go

if OBJECT_ID('#Sposob2_SelfJoin', 'P') is not null drop proc #Sposob2_SelfJoin

go

create proc #Sposob2_SelfJoin as

exec #header;

with cte as (select t2.SalesOrderDetailID ID, sum(isnull(t1.UnitPrice, 0)) RunningTotal from SalesOrderDetails t1

             right join SalesOrderDetails t2 on t1.SalesOrderID = t2.SalesOrderID and t1.SalesOrderDetailID < t2.SalesOrderDetailID

             group by t2.SalesOrderID, t2.SalesOrderDetailID)

update SalesOrderDetails set RunningTotal = cte.RunningTotal from SalesOrderDetails t join cte on t.SalesOrderDetailID = cte.ID

go

if OBJECT_ID('#Sposob3_Subquery', 'P') is not null drop proc #Sposob3_Subquery

go

create proc #Sposob3_Subquery as

exec #header;

with cte as (select SalesOrderDetailID ID,

                    (select isnull(sum(UnitPrice), 0) from SalesOrderDetails t2

                              where t2.SalesOrderID = t1.SalesOrderID and t2.SalesOrderDetailID < t1.SalesOrderDetailID) as RunningTotal

             from SalesOrderDetails t1)

update SalesOrderDetails set RunningTotal = cte.RunningTotal from SalesOrderDetails t join cte on t.SalesOrderDetailID = cte.ID

go

if OBJECT_ID('#Sposob4_SumOverOrderedWindow', 'P') is not null drop proc #Sposob4_SumOverOrderedWindow

go

create proc #Sposob4_SumOverOrderedWindow as

exec #header;

with cte as (select SalesOrderDetailID ID,

                    isnull(SUM(UnitPrice) over (partition by SalesOrderID order by SalesOrderDetailID rows between unbounded preceding and 1 preceding), 0) as RunningTotal

             from SalesOrderDetails)

update SalesOrderDetails set RunningTotal = cte.RunningTotal from SalesOrderDetails t join cte on t.SalesOrderDetailID = cte.ID

go

Скрипт 1

 

Тесты производились на виртуальной машине Windows 7 Ultimate x64 SP1, которой были выделены 2 потока Intel Core i7 720QM (1.6GHz) и 2 гига оперативки. Установлен Microsoft SQL Server "Denali" (CTP3) - 11.0.1440.19 (X64) Enterprise Evaluation Edition. Каких-либо специальных настроек после установки не производилось.

 

exec #Sposob1_Cursor

go

exec #Sposob2_SelfJoin

go

exec #Sposob3_Subquery

go

exec #Sposob4_SumOverOrderedWindow

Скрипт 2

 

Estimated plan прогнозирует, что наиболее затратным будет способ с подзапросом, а наиболее легким - треугольный джойн таблицы самой на себя:

 

image

Рис.3

 

#Sposob1_Cursor (8%) + #Header (5%) = 12%

#Sposob2_SelfJoin (5%) + #Header (5%) = 10%

#Sposob3_Subquery (49%) + #Header (5%) = 54%

#Sposob4_SumOverOrderedWindow (19%) + #Header (5%) = 24%

 

Он ошибается. Вот фактические показания по количеству операций ввода/вывода и длительностям выполнения, снятые в профайлере. Длительность CPU означает непосредственно расход процессорного времени на выполнение процедуры, Duration - общее время с учетом ожиданий блокированных ресурсов, стояния в очередях к планировщику, пока тот подхватит и кинет на конвейер и т.д.

 

image

Рис.4

 

 

Reads

Writes

CPU

Duration

#Sposob1_Cursor

2308642

803

24250

25623

#Sposob2_SelfJoin

736181

447

4139

6303

#Sposob3_Subquery

981768

787

4438

4527

#Sposob4_SumOverOrderedWindow

492204

422

2844

2537

 

Мы видим, что самым дорогим вышел курсор. Таблица SalesOrderDetails содержит 121317записей, и их прямолинейный перебор влетает в копеечку. Наиболее оптимально проявил себя появившийся в СТР3 способ sum() over (partition by … order by …). То, что время CPU для него превышает общее время выполнения, не должно смущать. Мы видели (Рис.3), что оптимизатор построил параллельный план выполнения, а время CPU учитывает суммарное время обработки на каждом станке.

 

clip_image010clip_image012

clip_image014clip_image016

Рис.5

 

Любопытно пронаблюдать, как разительно изменится картина для традиционных способов, если нарастающий итог посчитать не внутри каждой группы SalesOrderID, а по всей таблице:

 

if OBJECT_ID('#Sposob1_Cursor', 'P') is not null drop proc #Sposob1_Cursor

go

create proc #Sposob1_Cursor as

exec #header

declare @cur cursor, @Price money, @RunningTotal money = 0

set @cur = cursor local keyset for select UnitPrice from SalesOrderDetails order by SalesOrderDetailID for update of RunningTotal

open @cur

set nocount on

while 1 = 1 begin

 fetch next from @cur into @Price

 if @@FETCH_STATUS <> 0 break

 update SalesOrderDetails set RunningTotal = @RunningTotal where current of @cur

 set @RunningTotal += @Price

end

set nocount off

close @cur

deallocate @cur

go

if OBJECT_ID('#Sposob2_SelfJoin', 'P') is not null drop proc #Sposob2_SelfJoin

go

create proc #Sposob2_SelfJoin as

exec #header;

with cte as (select t2.SalesOrderDetailID ID, sum(isnull(t1.UnitPrice, 0)) RunningTotal from SalesOrderDetails t1

             right join SalesOrderDetails t2 on t1.SalesOrderDetailID < t2.SalesOrderDetailID

             group by t2.SalesOrderDetailID)

update SalesOrderDetails set RunningTotal = cte.RunningTotal from SalesOrderDetails t join cte on t.SalesOrderDetailID = cte.ID

go

if OBJECT_ID('#Sposob3_Subquery', 'P') is not null drop proc #Sposob3_Subquery

go

create proc #Sposob3_Subquery as

exec #header;

with cte as (select SalesOrderDetailID ID,

                    (select isnull(sum(UnitPrice), 0) from SalesOrderDetails t2 where t2.SalesOrderDetailID < t1.SalesOrderDetailID) as RunningTotal

                    from SalesOrderDetails t1)

update SalesOrderDetails set RunningTotal = cte.RunningTotal from SalesOrderDetails t join cte on t.SalesOrderDetailID = cte.ID

go

if OBJECT_ID('#Sposob4_SumOverOrderedWindow', 'P') is not null drop proc #Sposob4_SumOverOrderedWindow

go

create proc #Sposob4_SumOverOrderedWindow as

exec #header;

with cte as (select SalesOrderDetailID ID,

                    isnull(SUM(UnitPrice) over (order by SalesOrderDetailID rows between unbounded preceding and 1 preceding), 0) RunningTotal

             from SalesOrderDetails)

update SalesOrderDetails set RunningTotal = cte.RunningTotal from SalesOrderDetails t join cte on t.SalesOrderDetailID = cte.ID

go

Скрипт 3

 

Снова берем Скрипт 2 и смотрим, что на этот раз обещает Estimated Plan:

 

#Sposob1_Cursor (1%) + #Header (0%) = 1%

#Sposob2_SelfJoin (77%) + #Header (0%) = 77%

#Sposob3_Subquery (22%) + #Header (0%) = 22%

#Sposob4_SumOverOrderedWindow (0%) + #Header (0%) = 0%

 

Он опять ошибается:

image

Рис.6

 

 

Reads

Writes

CPU

Duration

#Sposob1_Cursor

2308637

169

22093

23463

#Sposob2_SelfJoin

30856922

422

2948313

1484273

#Sposob3_Subquery

20031560

737

4231375

4259389

#Sposob4_SumOverOrderedWindow

491213

422

2875

2500

 

Вариант с курсором практически не изменился по сравнению с Рис.4. Оно и понятно. Что в том, что в этом случае курсор бредет себе вдоль таблицы, перебирая одну за другой все ее записи. Зато способ с джойном таблицы самой с собой резко перегнал его по дороговизне. В прошлом эксперименте, где нарастающий итог считался в пределах группы по SalesOrderID, он перемножал группу саму на себя и резалтсеты складывал. Средний размер группы составляет select avg(n) from (select SalesOrderID, count(*) over (partition by SalesOrderID) n from SalesOrderDetails) t = 17. Это фигня. Теперь размер окна - это вся таблица, приходится перемножать 100 тыс.записей на 100 тыс.записей. Хорошо, с учетом треугольного неравенства в where на 50 тыс. Все равно неудивительно, что ему поплохело. Аналогичный порядок величины получается в случае подзапроса. Для каждой i-й записи нужно просканировать i-1 предыдущих. Всего выходит n * в среднем n/2, то есть тоже квадрат числа записей. Затраты на sum() over (partition by … order by …), как и в случае курсора, не меняются. Упорядоченная сумма вновь оказывается наиболее оптимальным способом вычисления нарастающего итога.

 

 

clip_image020clip_image022

clip_image024clip_image026

Рис.7

 

Вывод. До недавнего времени наиболее разумным способом вычисления нарастающего итога в SQL Server при больших размерах окна (partition by по группам) оставался курсор. Реализованный в SQL Server 11 Denali CTP3 способ вычисления нарастающего итога также имеет линейную, а не квадратичную зависимость от числа записей таблицы и не зависит от размера окна. Однако будучи set based он оказывается менее затратным по операциям ввода/вывода и времени выполнения.

 

 

Алексей Шуленин