Creating In Memory OLTP Tables


This is second article on In Memory table.
Lets see how to create In Memory tables.

Lets create a new database.

InMemory_Test


First we need to add filegroup for memory optimized data








After adding filegroup we will add file to this filegroup





Now the database is ready


Before creating table we have to note few points
There are some more points also but we will lookinto it later


1.Index must be created at time creating table
2.Hash bucket size should be 2 times or more than count distinct values in the coumn
If bucket size is larger it will not be issue but if its smalled it will degrade performance
3.We have to decide its durability
    1.schema_only
    Only table structure will be stored no data will be stored.
    Once server will crash or restart the data will lost
    2.schema_and_data
    Both scema and data will be stored in server.
    After crash or restarting data will be read from disk.


Now lets create tables also we will check its performance


1.Diskbased table



CREATE TABLE t1
  (
     id   INT PRIMARY KEY,
     data VARCHAR(100)
  )




2.Table with schema_only as durability




CREATE TABLE t2
  (
     id   INT  NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1000000),
     data VARCHAR(100)
  )
  WITH(MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_ONLY)



If we want durability we have to set DURABILITY to SCHEMA_AND_DATA



Now we will insert data into tables and check performance





SET NOCOUNT ON

DECLARE @date1 DATETIME
DECLARE @date2 DATETIME
DECLARE @i INT

SET @i = 1

SELECT @date1 = Getdate()

WHILE @i <= 100000
  BEGIN
      INSERT INTO t1
      SELECT @I,
             'data' + CONVERT(VARCHAR(10), @i)

      SET @i = @i + 1
  END

SELECT @date2 = Getdate()

SELECT 'Disk Based Table' ,Datediff(ss, @date1, @date2)

go

DECLARE @date1 DATETIME
DECLARE @date2 DATETIME
DECLARE @i INT

SET @i = 1

SELECT @date1 = Getdate()

WHILE @i <= 100000
  BEGIN
      INSERT INTO t2
      SELECT @I,
             'data' + CONVERT(VARCHAR(10), @i)

      SET @i = @i + 1
  END

SELECT @date2 = Getdate()



SELECT 'InMemory Schema Only',Datediff(ss, @date1, @date2)









Here we can see that Diskbased table took 23 seconds while InMemory Table took only  4 seconds.
The difference is clearly visible and tempts us to use it ASAP :-)




No comments:

Post a Comment

Amazon Contextual Product Ads

Popular Posts