MSSQL natively compiled: когда они тормозят

В базах данных нет серебряной пули, универсального рецепта. Мне захотелось проверить экспериментально один граничный случай использования in memory tables и natively compiled — когда в тесте все было хорошо, а на реальных данных начались тормоза.

Начинаем за здравие…

Вначале покажем, как Natively compiled могут творить чудеса. Напишем наивную функцию проверки числа на простоту:

create function dbo.isPrime (@n bigint) returns int as   begin   if @n = 1 return 0   if @n = 2 return 1   if @n = 3 return 1   if @n % 2 = 0 return 0   declare @sq int   set @sq = sqrt(@n)+1 -- check odds up to sqrt   declare @dv int = 1   while @dv < @sq      begin set @dv=@dv+2 if @n % @dv = 0 return 0 end   return 1   end GO

Также создадим такую же natively compiled функцию. Далее часть кода я буду прятать, чтобы статья не вышла очень длинной. Код нужен для тех, кто захочет повторить эксперимент.

create function dbo.isPrimeN (@n bigint) returns int WITH NATIVE_COMPILATION, SCHEMABINDING as   BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')   if @n = 1 return 0   if @n = 2 return 1   if @n = 3 return 1   if @n % 2 = 0 return 0   declare @sq int   set @sq = sqrt(@n)+1 -- check odds up to sqrt   declare @dv int = 1   while @dv < @sq      begin set @dv=@dv+2 if @n % @dv = 0 return 0 end   return 1   end GO

Теперь сравним скорость выполнения:

declare @dt datetime set @dt=getdate() select dbo.isPrime(1000000000000037) select datediff(ms,@dt,getdate()) as ms --> 15390  declare @dt datetime set @dt=getdate() select dbo.isPrimeN(1000000000000037) select datediff(ms,@dt,getdate()) as ms --> 277

Получаем 277ms вместо 15390ms — укорение в 55 раз! Впечатляюще. Вам должно быть интересно, что именно тормозит в обычном коде? После каждого оператора SQL server вызывает scheduler, который определяет, а не пора ли уступить CPU другим тредам? Ведь мультизадачность у нас здесь кооперативная. Scheduler выполняет системный вызов GetTimePrecise, переключая контекст. Маленькие операторы, которые выполняются слишком часто, это кошмар для обычного кода MS SQL. Он для этого не предназначен. В некоторых случаях это может приводить к тому, что я описывал в своей давней статье.

Заканчиваем за упокой

А что, если данных будет слишком много? Давайте соберем тестовый макет. С помощью функции выше сохраним 5 миллионов простых чисел в таблицу primes:

create table Primes (p int) -- gen 10_000_000 primes starting with 3 truncate table Primes set nocount on declare @n int = 3, @gen int = 0 while @gen<5000000 begin   while dbo.IsPrimeN(@n) = 0 set @n=@n+2   insert into Primes select @n   set @gen=@gen+1   set @n=@n+2   end

Теперь создадим таблицы RelA и RelB, куда скопируем первые N простых чисел:

create table RelA (p int primary key, fl float, str1 nvarchar(100), str2 varchar(100)) create table RelB (p int primary key, fl float, str1 nvarchar(100), str2 varchar(100)) ...   insert into RelA select top (@n) p, p as p1,      'This a simple a filler field '+convert(nvarchar,p), 'This a simple a filler field '+convert(varchar,p) from Primes order by p   insert into RelB select top (@n) p+2, p as p1,      'This a simple a filler field '+convert(nvarchar,p), 'This a simple a filler field '+convert(varchar,p) from Primes order by p

Поля fl, str1 и str2 нужны, чтобы таблица не была предельно узкой — обычно в реальных таблицах помимо id есть ряд нужных полей. Во вторую таблицу мы записываем простое число плюс 2. Таким образом, inner join по p между этими двумя таблицами найдет… ну конечно, простые числа близнецы.

Теперь создадим еще in memory таблицы, аналогичные RelA и RelB:

CREATE TABLE dbo.MemA (       p INT PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=100000),    fl float, str1 nvarchar(100), str2 varchar(100)   )      WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_ONLY) CREATE TABLE dbo.MemB (       p INT PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=100000),    fl float, str1 nvarchar(100), str2 varchar(100)   )      WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_ONLY) CREATE TABLE dbo.MemA2 (       p INT PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=10000),    fl float, str1 nvarchar(100), str2 varchar(100)   )      WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_ONLY) CREATE TABLE dbo.MemB2 (       p INT PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=10000),    fl float, str1 nvarchar(100), str2 varchar(100)   )      WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_ONLY)

Они отличаются только bucket_count. Наконец, подошло время самого теста:

create function dbo.Relsum() returns float as    begin   declare @s float   select @s=sum(RelA.fl) from RelA inner join RelB on RelA.p=RelB.p   return @s   end GO create function dbo.Memsum()  returns float WITH NATIVE_COMPILATION, SCHEMABINDING   as    begin ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')   declare @s float   select @s=sum(A.fl) from dbo.MemA A inner join dbo.MemB B on A.p=B.p   return @s   end GO create function dbo.Memsum2()  returns float WITH NATIVE_COMPILATION, SCHEMABINDING   as    begin ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')   declare @s float   select @s=sum(A.fl) from dbo.MemA2 A inner join dbo.MemB2 B on A.p=B.p   return @s   end GO  create procedure test @n int as   truncate table RelA   truncate table RelB   delete from MemA   delete from MemB   delete from MemA2   delete from MemB2   insert into RelA select top (@n) p, p as p1,      'This a simple a filler field '+convert(nvarchar,p), 'This a simple a filler field '+convert(varchar,p) from Primes order by p   insert into RelB select top (@n) p+2, p as p1,      'This a simple a filler field '+convert(nvarchar,p), 'This a simple a filler field '+convert(varchar,p) from Primes order by p   insert into dbo.MemA select * from RelA   insert into dbo.MemB select * from RelB   insert into dbo.MemA2 select * from RelA   insert into dbo.MemB2 select * from RelB      declare @res bigint   declare @dt datetime set @dt=getdate()   select @res=dbo.Relsum()   select datediff(ms,@dt,getdate()) as ms   set @dt=getdate()   select @res=dbo.Memsum()   select datediff(ms,@dt,getdate()) as ms   set @dt=getdate()   select @res=dbo.Memsum2()   select datediff(ms,@dt,getdate()) as ms GO

Построим время выполнения для N первых простых чисел:

Как видно, для in memory tables время выполнения джойна — типично квадратичное. Я думаю, этот результат не вызывает удивления, он ожидаем. Меня интересовало, до какого числа записей in memory + natively compiled выигрывают? Увеличим первую часть графика:

Как видно, natively compiled показывают себя лучше до 150-300 тысяч записей, но это сильно зависит от bucket_count. Для небольшого количества записей все вообще замечательно:

Обращаю внимание, насколько сильно производительность зависит от bucket_count. А есть ли негативный эффект от использования большого bucket_count? Я подозревал, что это увеличенное потребление памяти, но эксперименты не подтверждают этого.