RiCo技術農場

我在撰寫native compiled SP，目前我還是覺得有很多不符合真實世界期待，

導致我之前在改寫上利用一大堆的Update和Delete來處理，

例如常用的case和like都沒有支援，看完下面範例，我想你應該會知道我在說什麼

1 case

--original query
alter proc usp_GetName(@topcount int)
as
begin
set nocount on
select top(@topcount) [Name],
case when [Type]=3 then 'rico'
when [Type]=8 then 'Sherry'
else 'FiFi'
end as 'ExtType'
from dbo.myitemtbl where [Name]>''
order by 1
end
exec dbo.usp_GetName 10

-- natively compiled stored procedure
drop TYPE dbo.MyItemResult
CREATE TYPE dbo.MyItemResult AS TABLE  
(  
   [Name] nvarchar(50) not null
   ,[Type] tinyint index ix_type  not null
   ,ExtType varchar(30) not null 
) with (memory_optimized=on)  
go  

create proc dbo.usp_GetNameNat(@topcount int)
with native_compilation,schemabinding,execute as owner
as
begin atomic with(transaction isolation level=snapshot,language=N'english')
  declare @result dbo.MyItemResult;
 
  INSERT @result   
  SELECT top(@topcount) isnull([Name],''),[Type],''
  FROM dbo.myitemtbl  
  WHERE [Name]>''
  order by 1

   --case type=3
  update @result set ExtType='rico'
  where [Type]=3

  --case type=8
   update @result set ExtType='Sherry'
   where [Type]=8

  --else
   update @result set ExtType='FiFi'
   where [Type]<>3 and [Type]<>8 
 
  -- return single resultset
  select [Name],ExtType from @result
end

exec dbo.usp_GetNameNat 10

 

2 like

--original query
create proc usp_GetInfo(@perfix varchar(30))
as
set nocount on
select t.c1,t.c2,t.c3,t.c4
from dbo.myEmail_mem t
where exists (select 1 from myorder_hot_memy t2 where t2.id=t.c2)
and c3 like @perfix+'%'

exec dbo.usp_GetInfo 'rico'

-- natively compiled stored procedure
alter proc usp_GetInfoNat(@perfix varchar(30))
with native_compilation,schemabinding,execute as owner
as
begin atomic with(transaction isolation level=snapshot, language='english')

select t.c1,t.c2,t.c3,t.c4
from dbo.myEmail_mem t
where exists (select 1 from dbo.myorder_hot_memy t2 where t2.id=t.c2)
and substring( c3,1,len(@perfix))= @perfix
end

exec dbo.usp_GetInfoNat 'rico'

 

參考

Survey of Initial Areas in In-Memory OLTP

Plan your adoption of In-Memory OLTP Features in SQL Server

Implementing a CASE Expression in a Natively Compiled Stored Procedure

Simulating an IF-WHILE EXISTS Statement in a Natively Compiled Module

How and Why to Enable Instant File Initialization

Implementing the OR Operator in Natively Compiled Stored Procedures

Implementing MERGE Functionality in a Natively Compiled Stored Procedure