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

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

 

Рис.1

 

Рис.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 прогнозирует, что наиболее затратным будет способ с подзапросом, а наиболее легким - треугольный джойн таблицы самой на себя:

 

Рис.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 - общее время с учетом ожиданий блокированных ресурсов, стояния в очередях к планировщику, пока тот подхватит и кинет на конвейер и т.д.

 

Рис.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 учитывает суммарное время обработки на каждом станке.

 

Рис.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%

 

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

Рис.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 …), как и в случае курсора, не меняются. Упорядоченная сумма вновь оказывается наиболее оптимальным способом вычисления нарастающего итога.

 

 

Рис.7

 

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

 

 

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