SQL with Manoj

In my previous posts [this & this] I talked about creating Memory Optimized Database, how to create In-Memory Tables & Native Compiled Stored Procedures and what happens when they are created.

Here in this post we will see how FAST actually In-Memory tables & Native Compiled Stored Procedures are, when compared with normal Disk based Tables & Simple Stored Procedures.

I’ll be using the same [ManTest] database used in my previous posts, you can refer to the DDL script [here].
 

–> We will create:

1. One Disk based Table & one simple Stored Procedure which will use this Disk based Table.

2. One In-Memory Table & one Native Compiled Stored Procedure which will use this In-Memory Table.
 

1. Let’s first create a Disk based Table and a normal Stored Procedure:

USE [ManTest]
GO

-- Create a Disk table (non-Memory Optimized):
CREATE TABLE dbo.DiskTable
(
    ID INT NOT NULL
        PRIMARY KEY,
    VarString VARCHAR(200) NOT NULL, 
    DateAdded DATETIME NOT NULL
)
GO

-- Create normal Stored Procedure to load data into above Table:
CREATE PROCEDURE dbo.spLoadDiskTable @maxRows INT, @VarString VARCHAR(200)
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @i INT = 1

	WHILE @i <= @maxRows
	BEGIN
		INSERT INTO dbo.DiskTable VALUES(@i, @VarString, GETDATE())
		SET @i = @i+1
	END
END
GO

2. Now create an In-Memory table & a Native Compiled Stored Procedure to load data:

-- Create an In-Memory table:
CREATE TABLE dbo.MemOptTable
(
    ID INT NOT NULL
        PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 10000),
    VarString VARCHAR(200) NOT NULL, 
    DateAdded DATETIME NOT NULL
) WITH (
	MEMORY_OPTIMIZED = ON, 
	DURABILITY = SCHEMA_AND_DATA
)
GO

-- Create Native Compiled Stored Procedure to load data into above Table:
CREATE PROCEDURE dbo.spLoadMemOptTable @maxRows INT, @VarString VARCHAR(200)
WITH 
	NATIVE_COMPILATION, 
	SCHEMABINDING, 
	EXECUTE AS OWNER
AS
BEGIN ATOMIC
WITH (TRANSACTION ISOLATION LEVEL=SNAPSHOT, LANGUAGE='us_english')

	DECLARE @i INT = 1

	WHILE @i <= @maxRows
	BEGIN
		INSERT INTO dbo.MemOptTable VALUES(@i, @VarString, GETDATE())
		SET @i = @i+1
	END
END
GO

–> Now we will try to Load 10k record in above 2 table in various ways, as follows:

1. Load Disk based Table by T-SQL script using a WHILE loop.

2. Load the same Disk based Table by Stored Procedure which internally uses a WHILE loop.

3. Load In-Memory Table by T-SQL script using a WHILE loop.

4. Load the same In-Memory Table by Native Compiled Stored Procedure which internally uses a WHILE loop.
 

–> Working with Disk based Tables:

SET NOCOUNT ON

DECLARE 
	@StartTime DATETIME2,
	@TotalTime INT

DECLARE 
	@i INT,
	@maxRows INT,
	@VarString VARCHAR(200)

SET @maxRows = 10000
SET @VarString = REPLICATE('a',200)

SET @StartTime = SYSDATETIME()
SET @i = 1

-- 1. Load Disk Table (without SP):
WHILE @i <= @maxRows
BEGIN
	INSERT INTO dbo.DiskTable VALUES(@i, @VarString, GETDATE())
	SET @i = @i+1
END

SET @TotalTime = DATEDIFF(ms,@StartTime,SYSDATETIME())

SELECT 'Disk Table Load: ' + CAST(@TotalTime AS VARCHAR) + ' ms (without SP)'

-- 2. Load Disk Table (with simple SP):
DELETE FROM dbo.DiskTable

SET @StartTime = SYSDATETIME()

EXEC spLoadDiskTable @maxRows, @VarString

SET @TotalTime = DATEDIFF(ms,@StartTime,SYSDATETIME())

SELECT 'Disk Table Load: ' + CAST(@TotalTime AS VARCHAR) + ' ms (with simple SP)'

–> Working with In-Memory Tables:

-- 3. Load Memory Optimized Table (without SP):
SET @StartTime = SYSDATETIME()
SET @i = 1

WHILE @i <= @maxRows
BEGIN
	INSERT INTO dbo.MemOptTable VALUES(@i, @VarString, GETDATE())
	SET @i = @i+1
END

SET @TotalTime = DATEDIFF(ms,@StartTime,SYSDATETIME())

SELECT 'Memory Table Load: ' + CAST(@TotalTime AS VARCHAR) + ' ms (without SP)'

-- 4. Load Memory Optimized Table (with Native Compiled SP):
DELETE FROM dbo.MemOptTable

SET @StartTime = SYSDATETIME()

EXEC spLoadMemOptTable @maxRows, @VarString

SET @TotalTime = DATEDIFF(ms,@StartTime,SYSDATETIME())

SELECT 'Disk Table Load: ' + CAST(@TotalTime AS VARCHAR) + ' ms (with Native Compiled SP)'
GO

–> Output (Loaded 10k records):

Disk based Table Load	 : 28382 ms (without SP)
Disk based Table SP Load : 8297 ms  (with simple SP)
In-Memory Table Load     : 5176 ms  (without SP)
In-Memory Table SP Load  : 174 ms   (with Native Compiled SP)

So, you can clearly see the benefit and multifold increase in performance by using In-Memory Tables & Native Compiled Stored Procedures. The graph below shows performance in visual bar charts, impressive, isn’t it?

–> Final Cleanup

DROP PROCEDURE dbo.spLoadDiskTable
DROP TABLE dbo.DiskTable
DROP PROCEDURE dbo.spLoadMemOptTable
DROP TABLE dbo.MemOptTable
GO

Update: Know more about In-Memory tables: