Archive
XTP (eXtreme Transaction Processing) with Hekaton Tables & Native Compiled Stored Procedures – SQL Server 2014
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: