Nearing Nirvana with Hekaton

Rarely does a piece of technology make a difference to a system without someone (developer, DBA, architect etc) sitting down and thinking about how to apply the technology to their problems.

For me one of the big pain points in TSQL development is that arrays/in memory storage are missing. If I wish to hold some data temporarily for processing, I have to use either a temporary table or a table variable. Reading and writing to these tables involves a massive load of logging/locking/latching, hmm isn’t that a use case for hekaton ?. Can we use a hekaton table in place of a temp table ?

Well, there are some restrictions in CTP1 but, Yes we can have a temporary hekaton table J

Firstly we need to define a table type:

1
2
3
4
5
6
7
8
9
Create type HekatonTest as TABLE 
(
    [IdCol] [int] NOT NULL,
    [guid] [uniqueidentifier] NOT NULL
    PRIMARY KEY NONCLUSTERED HASH 
    (
    [IdCol]
)WITH ( BUCKET_COUNT = 131072)
)WITH ( MEMORY_OPTIMIZED = ON )

You may be thinking here can the DURABILITY option of SCHEMA_ONLY be used here? Afraid not ( at least not yet ), the durability option is explicitly not supported when declaring a user defined type, it throws an error.

typedur

Lets attempt to use this type :

1
2
declare @Table HekatonTest
select tbl.IdCol,tbl.guid from @Table tbl

This errors with :

That’s a shame L We need to have a natively compiled sproc to use this type, oh well!

UPDATE 2013-10-28 : In CTP2 you can now do this.  Though, as with regular table variables row estimates are 1 so careful with those spills.

1
2
3
4
5
6
7
8
9
10
11
12
CREATE PROCEDURE HekatonTableVar
WITH NATIVE_COMPILATION, EXECUTE AS OWNER, SCHEMABINDING
AS
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
declare @Table dbo.HekatonTest
insert into @Table values(1,'267E1137-4EE7-4DA0-A63F-FC68F718403B')
insert into @Table values(2,'FCF669A3-2E39-4609-B64A-A5A200A62B80')
insert into @Table values(3,'CB06D311-B92C-4C7B-9C82-407C9276627C')
insert into @Table values(4,'DF468BE9-5B28-4874-9935-59057C4E29BF')
select tbl.IdCol,tbl.guid from @Table tbl
end
go

Now this is obviously a pretty trivial (and stupid) example but does show that we can at least use an in-memory table as temporary storage, a massive win.

Does this make a big difference? Lets compare against a table variable:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE PROCEDURE TableVar
AS
declare @Table table
(
    [IdCol] [int] NOT NULL,
    [guid] [uniqueidentifier] NOT NULL
)
 
insert into @Table values(1,'267E1137-4EE7-4DA0-A63F-FC68F718403B')
insert into @Table values(2,'FCF669A3-2E39-4609-B64A-A5A200A62B80')
insert into @Table values(3,'CB06D311-B92C-4C7B-9C82-407C9276627C')
insert into @Table values(4,'DF468BE9-5B28-4874-9935-59057C4E29BF')
select tbl.IdCol,tbl.guid from @Table tbl
go

Running that over 100 iterations in 100 threads (10,000 individual calls) we get an average execution time of 0.38 seconds. With the Hekaton table : 0.0014 seconds, 200 times faster!! All very un-scientific, but this alone would be enough to justify a jump to 2014 on release IMO.

UPDATE 2013-10-28 : With CTP2 allowing the use of hekaton table typed table variables outside of natively compiled stored procedures this is a very compelling reason to consider upgrading.

 

2 thoughts on “Nearing Nirvana with Hekaton

  1. Hi Dave-

    Well, I think 200x improvement is dohekaton, or something, I don’t know, it’s all Greek to me. 🙂

    I’ve got a SCHEMA_ONLY table on my test instance and it works as advertised. What error did you get?

    • Ah, you can create SCHEMA_ONLY tables, but you cant declare a table type to use in a table variable as schema only.
      Ill clarify that…

Leave a Reply

Your email address will not be published. Required fields are marked *