В базах данных нет серебряной пули, универсального рецепта. Мне захотелось проверить экспериментально один граничный случай использования 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 - уcкорение в 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? Я подозревал, что это увеличенное потребление памяти, но эксперименты не подтверждают этого.