When we issue T-SQL commands to SQL Server, the commands are parsed and compiled into machine code, which is then executed. This parsing and compiling becomes a major bottleneck when the locking and latching caused by pessimistic concurrency is removed. This is where natively compiled stored procedures come into play. They are effectively T-SQL code that is compiled into machine code once, and then instead of the parse and compile of a standard T-SQL command, the compiled DLL for the stored procedure is called. The improvements in execution time can be phenomenal.

Our next possibility of improvement is to reduce our parse and compile time of the insert command (the Query Interop mentioned earlier in this chapter). This can be achieved by natively compiling the insert stored procedure. Let's do just that, and run the same test with our memory-optimized table also using a natively compiled stored procedure:

USE InMemoryTest 
GO  
CREATE PROCEDURE dbo.InMemoryInsertOptimized 
    @UserId INT, 
    @UserName VARCHAR(255), 
    @LoginTime DATETIME2, 
    @LoginCount INT 
WITH NATIVE_COMPILATION, SCHEMABINDING 
AS 
BEGIN ATOMIC WITH 
( 
   TRANSACTION ISOLATION LEVEL = SNAPSHOT, 
   LANGUAGE = N'English' 
) 
 
   INSERT dbo.InMemoryTable 
   (UserId, UserName, LoginTime, LoginCount) 
    VALUES 
    (@UserId, @UserName, @LoginTime, @LoginCount); 
   RETURN 0; 
END; 
GO 
 
 
USE InMemoryTest 
GO  
DELETE FROM dbo.InMemoryTable 
GO 
 
DECLARE @start DATETIME2; 
SET @start = SYSDATETIME(); 
 
DECLARE @Counter int = 0, 
         @_LoginTime DATETIME2 = SYSDATETIME(), 
         @_UserName VARCHAR(255); 
   WHILE @Counter < 50000 
   BEGIN 
         SET @_UserName = 'UserName ' + CAST(@Counter AS varchar(6)) 
 
         EXECUTE dbo.InMemoryInsertOptimized  
               @UserId = @Counter, 
               @UserName = @_UserName, 
               @LoginTime = @_LoginTime,  
               @LoginCount = @Counter 
                
         SET @Counter += 1; 
   END; 
 
SELECT DATEDIFF(ms, @start, SYSDATETIME()) AS 'insert into memory-optimized table & native stored procedure (in ms)'; 
GO 
 
insert into memory-optimized table & native stored procedure (in ms)
----------- ------------- --------------------------- -----------
631
    
  

We can see from the results that the execution time has been reduced from ~1.4 seconds down to ~600 milliseconds. This is an impressive improvement, considering we have again only made minimal changes.

However, there is still room for improvement here. At present, we have created a native compiled stored procedure, which allows us to save on compile time for the insert statement itself. With this solution, we are still using non-compiled T-SQL for the loop. This optimization is easily achieved and we can run the final test to see how fast we can get:

USE InMemoryTest 
GOCREATE PROCEDURE dbo.FullyNativeInMemoryInsertOptimized 
WITH NATIVE_COMPILATION, SCHEMABINDING 
AS 
BEGIN ATOMIC WITH 
( 
   TRANSACTION ISOLATION LEVEL = SNAPSHOT, 
   LANGUAGE = N'English' 
) 
 
   DECLARE @Counter int = 0, 
   @_LoginTime DATETIME2 = SYSDATETIME(), 
   @_UserName VARCHAR(255) 
   ; 
   WHILE @Counter < 50000 
   BEGIN 
               SET  @_UserName = 'UserName ' + CAST(@Counter AS varchar(6)) 
 
               INSERT dbo.InMemoryTable 
               (UserId, UserName, LoginTime, LoginCount) 
               VALUES 
               (@Counter, @_UserName, @_LoginTime, @Counter); 
 
         SET @Counter += 1; 
   END; 
   RETURN 0; 
END; 
GO 
 
USE InMemoryTest 
GO  
DELETE FROM dbo.InMemoryTable 
GO 
 
DECLARE @start DATETIME2; 
SET @start = SYSDATETIME(); 
 
EXEC dbo.FullyNativeInMemoryInsertOptimized 
 
SELECT DATEDIFF(ms, @start, SYSDATETIME()) AS 'insert into fully native memory-optimized table (in ms)'; 
GO 

insert into fully native memory-optimized table (in ms)
----------- ------------- --------------------------- -----------
155
  

With a fully optimized workflow, taking advantage of both memory-optimized tables as well as natively compiled stored procedures, the execution time has dropped from 6.2 seconds to 155 milliseconds. With just a few simple steps, the execution time of the example insert has been reduced by 40 times. While not quite the 100-fold improvement that the codename suggests, this is a serious improvement by any measure.