[SQL Server]Fascinating Natively compiled stored procedures

2017-07-30

354
0
SQL Server 2016
2017-12-03

In recently, I tried to enhance performance of some operator api on our company system.

I found some operator api so fast on our system.

All queries using index seeks and no more than 3ms.

This post , I will show you how to improve its query performance.

We had known natively compiled stored procedures are TSQL stored procedures compiled to native code

that access memory table and produce DLLs.

Natively compiled stored procedures allow efficient query execution, faster data access

and business logic than the disk base(traditional) TSQL.

So I think maybe I can use natively compiled stored procedures to improve query performance.

Take a look at my performance test as below

Note: As I see it, the migration Natively compiled stored procedures wasn’t easy on the real world.

--Disk Base

CREATE TABLE myEmail_disk
(  
    c1 Uniqueidentifier DEFAULT  newsequentialid()  NOT NULL PRIMARY KEY NONCLUSTERED,
	c2 int NOT NULL index cidx clustered(c2)with(data_compression=page) ,
    c3 VARCHAR(20) NOT NULL,
    c4 nvarchar(100) NOT NULL	
)
GO
create index idx_c3 on dbo.myEmail_disk(c3)
include(c4)
with(data_compression=page)

set nocount on
declare @i int =0
while(@i<10000)
begin
insert into myEmail_disk
values(default,@i,'ricoisme'+cast(@i as varchar(10)),N'rico'+cast(@i as varchar(10))+N'@ricostudio.co');
set @i+=1;
end

create proc usp_getemail(@c3 VARCHAR(20))
as
begin
  set nocount on
  select c3,c4 from dbo.myEmail_disk where c3=@c3
end

exec usp_getemail 'ricoisme2'

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
Table 'myEmail_disk'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0

You saw numbers of logical reads because I accessed disk base.

Alright , let’s jump to In-Memory

--In-Memory

CREATE TABLE myEmail_mem
(  
    c1 Uniqueidentifier DEFAULT  newsequentialid()  NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 10000),
	c2 int NOT NULL ,
    c3 VARCHAR(20) NOT NULL INDEX ix_c3 NONCLUSTERED, 
    c4 nvarchar(100) NOT NULL	
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
GO
INSERT  INTO myEmail_mem
        ( c1 ,
          c2 ,
          c3 ,
          c4
        )
        SELECT  c1 ,
                c2 ,
                c3 ,
                c4
        FROM    myEmail_disk
CREATE PROCEDURE usp_getemail_nac (@c3 VARCHAR(20)='')
WITH NATIVE_COMPILATION, SCHEMABINDING,execute as owner
AS
BEGIN ATOMIC
WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'english')
       select c3,c4 from dbo.myEmail_mem where c3=@c3
END
exec usp_getemail_nac 'ricoisme2'

No any logical reads when I accessed memory table.