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.