Гудков К.С.

Государственный научно-исследовательский институт авиационных систем,

Московский физико-технический институт

Сравнение методов реализации агрегатной функции произведения чисел в СУБД MS SQL Server

В СУБД Microsoft SQL Server 2014 (и в более ранних версиях) отсутствует агрегатная функция для вычисления произведения чисел [1]. Она может оказаться полезной, например, для вычисления среднего геометрического чисел.

Существует несколько традиционных подходов для вычисления произведения чисел в СУБД Microsoft SQL Server:

· Реализация пользовательской агрегатной функции среды CLR.

· Математический метод, базирующийся на факте, что логарифм произведения равен сумме логарифмов.

· Метод, основанный на присваивании переменной внутри SELECT.

· Метод, основанный на использовании курсоров.

· Метод, основанный на использовании рекурсивных обобщённых табличных выражений (Common Table Expressions, CTE).

Для сравнения производительности была сгенерирована таблица из 100 тысяч записей, случайным образом разбитая на группы с небольшим числом перемножаемых чисел, среди которых встречаются и положительные, и отрицательные, и ноль. Задача состояла в вычислении произведения всех чисел для каждой из групп по отдельности. Сценарий по созданию таблицы выглядит следующим образом:

if (object_id('RandomIntNumbers') is not null)

drop table RandomIntNumbers

create table RandomIntNumbers(ID int primary key, RandGroupID int, RandInt int)

Для заполнения таблицы использовались обобщённые табличные выражения:

;with CTERandomIntNumbers(ID, RandGroupID, RandInt) as

(

select 1 as ID,

abs(cast(cast(newid() as Binary(4)) as int)) % 60000 as RandGroupID,

cast(cast(newid() as Binary(4)) as int) % 10 as RandInt

union all

select ID + 1 as ID,

abs(cast(cast(newid() as binary(4)) as int) % 60000) as RandGroupID,

cast(cast(newid() as binary(4)) as int) % 10 as RandInt

from CTERandomIntNumbers

where ID < 100000

)

insert into RandomIntNumbers(ID, RandGroupID, RandInt)

select ID, RandGroupID, RandInt

from CTERandomIntNumbers

option(maxrecursion 0)

Для замера времени работы сценария были созданы две переменные типа datetime: @BeginTime и @EndTime. Переменная @BeginTime инициализировались в начале выполнения запроса, а переменная @EndTime инициализировалась в конце выполнения запроса. Для их инициализации использовалась функция getdate(). Суммарное время работы вычислялось при помощи: select datediff(millisecond, @BeginTime, @EndTime).

Лучшие результаты с точки зрения скорости работы были показаны при помощи CLR. Среднее время работы данного метода составило 413 миллисекунд. Данный метод универсален, то есть пользовательская агрегатная функция среды CLR работает для любой таблицы. Для реализации агрегата необходимо реализовать 4 стандартных метода Init, Accumulate, Merge и Terminate. Кроме того, необходимо создать 2 объекта SQL-сервера: сборку (assembly) и непосредственно агрегатную функцию (aggregate). Примеры реализации агрегатных функций CLR показаны, например, здесь [2].

На втором месте с точки зрения скорости работы оказался математический метод:

select RandGroupID, round(exp(sum(log(case when RandInt <> 0 then abs(RandInt) else 1 end))) *

case when sum(case when RandInt < 0 then 1 else 0 end) % 2 = 1 then -1 else 1 end *

case when sum(case when RandInt = 0 then 1 else 0 end) > 0 then 0 else 1 end, 0) as Product

from RandomIntNumbers

group by RandGroupID

Среднее время работы данного метода составило 513 миллисекунд.

Конструкция "CASE WHEN THEN ELSE END" была необходима, чтобы решить проблему отрицательных чисел и нуля. Среди недостатков этого метода можно указать отсутствие гарантии корректной работы, вызванное присутствием ошибок округлений, способных привести к неправильному результату.

Третьи результаты показал метод, основанный на присваивании переменной внутри SELECT. Для его работы необходимо создать скалярную функцию SQL.

if (object_id('SQLProduct') is not null)

drop function SQLProduct

go

create function SQLProduct(@RandGroupID int)

returns int

begin

declare @ProdVar as int

set @ProdVar = 1

select @ProdVar = @ProdVar * RandInt

from RandomIntNumbers RIN

where RandGroupID = @RandGroupID

return @ProdVar

end

Кроме того, как видно из устройства функции SQLProduct, для быстрой работы необходимо также создать некластерный индекс по полю RandGroupID. Среднее время работы данного метода составило 1380 миллисекунд. Перечислим недостатки метода, основанного на присваивании переменной внутри SELECT:

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

· Необходимость создания некластерного индекса по идентификатору группы. Без него среднее время работы метода составило 8 минут.

· Отсутствие гарантии корректной работы, которое можно продемонстрировать на простейшем примере:

declare @TestTable as table(ID int)

insert into @TestTable(ID) values(2)

insert into @TestTable(ID) values(4)

declare @TestVar as int

set @TestVar = 1

select @TestVar = @TestVar * ID

from @TestTable

order by ID * ID

select @TestVar

Переменная @TestVar будет содержать 4 вместо ожидаемых 8.

Четвертые результаты показал метод, основанный на стандартном использовании курсоров. Среднее время его работы 4040 миллисекунд.

Последнее место с точки зрения скорости работы показал метод, основанный на использовании рекурсивных обобщённых табличных выражений. На описанных в рамках статьи данных метод не закончил работу в течении часа.

Таким образом, метод, основанный на присваивании переменной внутри SELECT, и математический метод не гарантируют корректность результата. Методы, основанные на обобщённых табличных выражениях и курсорах, работают слишком медленно по сравнению с методом, основанным на реализации пользовательской агрегатной функции среды CLR. Поэтому именно этот метод и является лучшим для вычисления произведения чисел в Microsoft SQL Server.

Литература:

1. https://msdn.microsoft.com/ru-ru/library/ms173454.aspx

2. https://msdn.microsoft.com/ru-ru/library/ms131056.aspx