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