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