We have seen the lists of limitations at both the table structure level and at the T-SQL language level, it is not all doom and gloom, but these restrictions may be causing some readers to re-think their enthusiasm for memory-optimized objects.

In this section, we will take a look at how traditional disk-based tables compare to their younger brothers, the memory-optimized tables. According to the codename of the In-Memory OLTP feature, Hekaton (Greek for a 100-fold), the new feature should be in the order of 100x faster.

We begin our test by creating a comparable disk-based table and inserting one row into it:

USE InMemoryTest 
GO  
CREATE TABLE DiskBasedTable 
( 
   UserId INT NOT NULL PRIMARY KEY NONCLUSTERED, 
   UserName VARCHAR(255) NOT NULL, 
   LoginTime DATETIME2 NOT NULL, 
   LoginCount INT NOT NULL, 
 
   INDEX NCL_IDX NONCLUSTERED (UserName) 
) 
GO 
INSERT INTO dbo.DiskBasedTable 
        ( UserId , 
          UserName , 
          LoginTime , 
          LoginCount 
        ) 
VALUES  ( 1 , 
          'John Smith' , 
          SYSDATETIME() , 
          1 
        ) 
; 

We have an identical table structure, with the only difference being that the disk-based table doesn't have a hash index, but rather a normal non-clustered index.

We will now run a test, inserting 500,000 rows of data into each table, measuring solely the execution time of each run. We begin by creating a stored procedure that will insert one row into the disk-based table:

CREATE PROCEDURE dbo.DiskBasedInsert 
    @UserId INT, 
    @UserName VARCHAR(255), 
    @LoginTime DATETIME2, 
    @LoginCount INT 
AS 
BEGIN 
 
    INSERT dbo.DiskBasedTable 
    (UserId, UserName, LoginTime, LoginCount) 
    VALUES 
    (@UserId, @UserName, @LoginTime, @LoginCount); 
 
END; 

This stored procedure is then called 500,000 times in a simple loop and the time difference between start and finish is recorded:

USE InMemoryTest 
GO  
TRUNCATE TABLE dbo.DiskBasedTable 
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.DiskBasedInsert  
               @UserId = @Counter, 
               @UserName = @_UserName, 
               @LoginTime = @_LoginTime,  
               @LoginCount = @Counter 
                
         SET @Counter += 1; 
   END; 
 
SELECT DATEDIFF(ms, @start, SYSDATETIME()) AS 'insert into disk-based table (in ms)'; 
GO 
 
insert into disk-based table (in ms)
----------- ------------- --------------------------- -----------
6230
  

The execution on my machine was repeatable with an average execution time between 6 and 7 seconds for 50,000 rows with a disk-based table.

The first step of optimizing this insert is to move from a disk-based table to a memory-optimized table:

USE InMemoryTest 
GO 
SET NOCOUNT ON 
GO 
CREATE PROCEDURE dbo.InMemoryInsert 
    @UserId INT, 
    @UserName VARCHAR(255), 
    @LoginTime DATETIME2, 
    @LoginCount INT 
AS 
BEGIN 
 
    INSERT dbo.InMemoryTable 
    (UserId, UserName, LoginTime, LoginCount) 
    VALUES 
    (@UserId, @UserName, @LoginTime, @LoginCount); 
 
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.InMemoryInsert  
               @UserId = @Counter, 
               @UserName = @_UserName, 
               @LoginTime = @_LoginTime,  
               @LoginCount = @Counter 
                
         SET @Counter += 1; 
   END; 
 
SELECT DATEDIFF(ms, @start, SYSDATETIME()) AS 'insert into memory-optimized table (in ms)'; 
GO 
 
insert into memory-optimized table (in ms)
----------- ------------- --------------------------- -----------
1399
 

OK, we have made a massive leap in terms of execution time. We are down from ~6 seconds to a quite respectable ~1.4 seconds. That improvement is purely down to the difference in locking and latching of our two tables. We swapped the disk-based table for a memory-optimized table.