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.