[SQL2014]Hekaton 初體驗
自從安裝 SQL 2014 CTP1 之後，還沒有好好的測試過，這幾天利用颱風天的週末測是一下，試試看是否真如傳說中的效能有爆炸性的突破。
首先我先預備兩個 SQL Server 2014 CTP 的環境，一個是安裝在 Windows 2008 R2 的 VM 上，而另外一個是使用在 Windows 2012 R2 的 Azure VM 上面
由於當使用 Hekaton 的 Memory-Optimized Table 的時候，他是使用不同的儲存架構，因此需要特別指定一個特定的 FileGroup，並且指定相關的存放目錄。
加上目前還在 CTP1 的版本，因此目前對多語系的支援還有待改善，因此在建立的時候我先指定 Latin1_General_100_Bin2 的定序，避免後續在建立 Native_Complied Stored Procedure 的時候出問題
而這個部分為了方便以後的測試，因此我利用 Script 的方式來做建立
USE [master]
GO
IF db_id('DEMO20130720') IS NOT NULL
DROP DATABASE [DEMO20130720]
CREATE DATABASE [DEMO20130720]
ON PRIMARY
( NAME = N'DEMO20130720', FILENAME = N'C:\DATA\DEMO20130720.mdf' ,
SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),
FILEGROUP [Hekaton_DB] CONTAINS MEMORY_OPTIMIZED_DATA DEFAULT
( NAME = N'DEMO20130720_Hekaton', FILENAME = N'C:\DATA\DEMO20130720_Hekaton' ,
MAXSIZE = UNLIMITED)
LOG ON
( NAME = N'DEMO20130720_log', FILENAME = N'C:\DATA\DEMO20130720_log.ldf' ,
SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
COLLATE Latin1_General_100_BIN2
建立好資料庫之後，接下來就可以建立一個測試用的 MEMORY_OPTIMIZED 的 Table，從目前所得知的相關資料上來看，由於 Hekaton 目前還在開發中，因此在建立 Table 的時候會有些限制，像是不能有 Constrain , Trigger 等，資料型態也沒有全部支援，因此在測試的時候要注意一下，我仿照 Northwind 的資料庫內的 Employees 的 Table，修改一些不相容的部分，來建立一個測試資料表。
CREATE TABLE dbo.[Employees](
[EmployeeID] INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 64),
[EmployeeName] [nvarchar](20) NOT NULL INDEX [IEmployeeName] HASH WITH (BUCKET_COUNT = 64),
[Title] [nvarchar](30) NULL,
[TitleOfCourtesy] [nvarchar](25) NULL,
[BirthDate] [datetime] NULL,
[HireDate] [datetime] NULL,
[Address] [nvarchar](60) NULL,
[HomePhone] [nvarchar](24) NULL,
[Extension] [nvarchar](4) NULL,
[PhotoPath] [nvarchar](255) NULL,
[Notes] [nvarchar](1024) NULL,
[ManagerID] [int] NULL,
[Salary] [int] NULL
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
GO
並且先塞入一些 Demo 資料來做測試
INSERT [dbo].[Employees] ([EmployeeID], [EmployeeName], [Title], [TitleOfCourtesy], [BirthDate], [HireDate], [Address], [HomePhone], [Extension], [PhotoPath], [Notes], [ManagerID], [Salary]) VALUES (1, N'張瑾雯', N'業務', N'小姐', CAST(N'1968-12-08 00:00:00.000' AS DateTime), CAST(N'1992-01-05 00:00:00.000' AS DateTime), N'北市仁愛路二段56號', N'(02) 2555-9857', N'5467', NULL, N'財力雄厚, 負責認真', 2, 54000)
INSERT [dbo].[Employees] ([EmployeeID], [EmployeeName], [Title], [TitleOfCourtesy], [BirthDate], [HireDate], [Address], [HomePhone], [Extension], [PhotoPath], [Notes], [ManagerID], [Salary]) VALUES (2, N'陳季暄', N'業務經理', N'先生', CAST(N'1952-02-19 00:00:00.000' AS DateTime), CAST(N'1992-08-14 00:00:00.000' AS DateTime), N'北市敦化南路一段1號', N'(02) 2555-9482', N'3457', NULL, N'工作態度認真', NULL, 72000)
INSERT [dbo].[Employees] ([EmployeeID], [EmployeeName], [Title], [TitleOfCourtesy], [BirthDate], [HireDate], [Address], [HomePhone], [Extension], [PhotoPath], [Notes], [ManagerID], [Salary]) VALUES (3, N'趙飛燕', N'業務', N'小姐', CAST(N'1963-08-30 00:00:00.000' AS DateTime), CAST(N'1992-04-01 00:00:00.000' AS DateTime), N'北市忠孝東路四段4 號', N'(02) 2555-3412', N'3355', NULL, N'工作有效率', 2, 51000)
INSERT [dbo].[Employees] ([EmployeeID], [EmployeeName], [Title], [TitleOfCourtesy], [BirthDate], [HireDate], [Address], [HomePhone], [Extension], [PhotoPath], [Notes], [ManagerID], [Salary]) VALUES (4, N'林美麗', N'業務', N'小姐', CAST(N'1958-09-19 00:00:00.000' AS DateTime), CAST(N'1993-05-03 00:00:00.000' AS DateTime), N'台北市南京東路三段3號', N'(02) 2555-8122', N'5176', NULL, N'積極向上', 1, 52000)
INSERT [dbo].[Employees] ([EmployeeID], [EmployeeName], [Title], [TitleOfCourtesy], [BirthDate], [HireDate], [Address], [HomePhone], [Extension], [PhotoPath], [Notes], [ManagerID], [Salary]) VALUES (5, N'劉天王', N'業務經理', N'先生', CAST(N'1955-03-04 00:00:00.000' AS DateTime), CAST(N'1993-10-17 00:00:00.000' AS DateTime), N'台北市北平東路24號', N'(02) 2555-4848', N'3453', NULL, N'個性隨和', NULL, 68000)
INSERT [dbo].[Employees] ([EmployeeID], [EmployeeName], [Title], [TitleOfCourtesy], [BirthDate], [HireDate], [Address], [HomePhone], [Extension], [PhotoPath], [Notes], [ManagerID], [Salary]) VALUES (6, N'黎國明', N'業務', N'先生', CAST(N'1963-07-02 00:00:00.000' AS DateTime), CAST(N'1993-10-17 00:00:00.000' AS DateTime), N'台北市中山北路六段88號', N'(02) 2555-7773', N'4281', NULL, N'有理想抱負', 5, 48000)
INSERT [dbo].[Employees] ([EmployeeID], [EmployeeName], [Title], [TitleOfCourtesy], [BirthDate], [HireDate], [Address], [HomePhone], [Extension], [PhotoPath], [Notes], [ManagerID], [Salary]) VALUES (7, N'郭國臹', N'業務', N'先生', CAST(N'1960-05-29 00:00:00.000' AS DateTime), CAST(N'1994-01-02 00:00:00.000' AS DateTime), N'台北市師大路67號', N'(02) 2555-5598', N'4651', NULL, N'曾在 Believe 當總裁', 5, 44000)
INSERT [dbo].[Employees] ([EmployeeID], [EmployeeName], [Title], [TitleOfCourtesy], [BirthDate], [HireDate], [Address], [HomePhone], [Extension], [PhotoPath], [Notes], [ManagerID], [Salary]) VALUES (8, N'蘇涵蘊', N'業務主管', N'小姐', CAST(N'1958-01-09 00:00:00.000' AS DateTime), CAST(N'1994-03-05 00:00:00.000' AS DateTime), N'台北市紹興南路99號', N'(02) 2555-1189', N'2344', NULL, N'曾經當選好人好事代表', 2, 62000)
INSERT [dbo].[Employees] ([EmployeeID], [EmployeeName], [Title], [TitleOfCourtesy], [BirthDate], [HireDate], [Address], [HomePhone], [Extension], [PhotoPath], [Notes], [ManagerID], [Salary]) VALUES (9, N'孟庭亭', N'業務', N'小姐', CAST(N'1969-07-02 00:00:00.000' AS DateTime), CAST(N'1994-11-15 00:00:00.000' AS DateTime), N'台北市信義路二段120號', N'(02) 2555-4444', N'4521', NULL, N'曾在事務所待過五年、當選環球小姐第一名', 5, 42000)
INSERT [dbo].[Employees] ([EmployeeID], [EmployeeName], [Title], [TitleOfCourtesy], [BirthDate], [HireDate], [Address], [HomePhone], [Extension], [PhotoPath], [Notes], [ManagerID], [Salary]) VALUES (12, N'賴俊良', N'資深工程師', N'先生', CAST(N'1972-12-06 00:00:00.000' AS DateTime), CAST(N'1995-12-06 00:00:00.000' AS DateTime), N'台北市北平東路24 號3 樓之一', N'3224932', N'221', NULL, N'英俊帥哥，智商 301.', 2, 52000)
INSERT [dbo].[Employees] ([EmployeeID], [EmployeeName], [Title], [TitleOfCourtesy], [BirthDate], [HireDate], [Address], [HomePhone], [Extension], [PhotoPath], [Notes], [ManagerID], [Salary]) VALUES (13, N'何大樓', N'助手', N'先生', CAST(N'1961-12-06 00:00:00.000' AS DateTime), CAST(N'1993-12-06 00:00:00.000' AS DateTime), N'台北市北平東路24 號3 樓之一', N'3224932', N'098', NULL, N'工作態度認真', 1, 38000)
INSERT [dbo].[Employees] ([EmployeeID], [EmployeeName], [Title], [TitleOfCourtesy], [BirthDate], [HireDate], [Address], [HomePhone], [Extension], [PhotoPath], [Notes], [ManagerID], [Salary]) VALUES (14, N'王大德', N'工程師', N'先生', CAST(N'1968-12-14 00:00:00.000' AS DateTime), CAST(N'1994-12-14 00:00:00.000' AS DateTime), N'台北市北平東路24 號3 樓之一', N'3224931', N'190', NULL, N'英俊帥哥，智商 300.', 2, 40000)
INSERT [dbo].[Employees] ([EmployeeID], [EmployeeName], [Title], [TitleOfCourtesy], [BirthDate], [HireDate], [Address], [HomePhone], [Extension], [PhotoPath], [Notes], [ManagerID], [Salary]) VALUES (19, N'林正明', N'業務', N'先生', CAST(N'1970-11-14 00:00:00.000' AS DateTime), CAST(N'2003-06-03 00:00:00.000' AS DateTime), N'高雄市文衡一路123號', NULL, N'3154', NULL, NULL, 23, 47400)
INSERT [dbo].[Employees] ([EmployeeID], [EmployeeName], [Title], [TitleOfCourtesy], [BirthDate], [HireDate], [Address], [HomePhone], [Extension], [PhotoPath], [Notes], [ManagerID], [Salary]) VALUES (20, N'劉文林', N'業務', N'先生', CAST(N'1969-12-16 00:00:00.000' AS DateTime), CAST(N'1999-09-20 00:00:00.000' AS DateTime), N'台南市中正二路3421號', NULL, N'1247', NULL, NULL, 23, 30500)
INSERT [dbo].[Employees] ([EmployeeID], [EmployeeName], [Title], [TitleOfCourtesy], [BirthDate], [HireDate], [Address], [HomePhone], [Extension], [PhotoPath], [Notes], [ManagerID], [Salary]) VALUES (21, N'李友吉', N'業務', N'先生', CAST(N'1979-03-21 00:00:00.000' AS DateTime), CAST(N'1999-05-27 00:00:00.000' AS DateTime), N'高雄縣崗山鎮光復路150巷5號3樓', NULL, N'454', NULL, NULL, 34, 33900)
INSERT [dbo].[Employees] ([EmployeeID], [EmployeeName], [Title], [TitleOfCourtesy], [BirthDate], [HireDate], [Address], [HomePhone], [Extension], [PhotoPath], [Notes], [ManagerID], [Salary]) VALUES (22, N'侯碧元', N'業務主管', N'先生', CAST(N'1982-07-10 00:00:00.000' AS DateTime), CAST(N'2001-10-20 00:00:00.000' AS DateTime), N'高雄縣鳳山市成功路35號', NULL, N'1237', NULL, NULL, 23, 66100)
INSERT [dbo].[Employees] ([EmployeeID], [EmployeeName], [Title], [TitleOfCourtesy], [BirthDate], [HireDate], [Address], [HomePhone], [Extension], [PhotoPath], [Notes], [ManagerID], [Salary]) VALUES (23, N'文芳芳', N'業務經理', N'小姐', CAST(N'1969-11-08 00:00:00.000' AS DateTime), CAST(N'2001-09-09 00:00:00.000' AS DateTime), N'高雄市一心二路675號', NULL, N'527', NULL, NULL, NULL, 69000)
INSERT [dbo].[Employees] ([EmployeeID], [EmployeeName], [Title], [TitleOfCourtesy], [BirthDate], [HireDate], [Address], [HomePhone], [Extension], [PhotoPath], [Notes], [ManagerID], [Salary]) VALUES (24, N'呂天生', N'業務', N'先生', CAST(N'1980-06-11 00:00:00.000' AS DateTime), CAST(N'2003-03-15 00:00:00.000' AS DateTime), N'台中市開元路55巷35號', NULL, N'5678', NULL, NULL, 23, 35100)
INSERT [dbo].[Employees] ([EmployeeID], [EmployeeName], [Title], [TitleOfCourtesy], [BirthDate], [HireDate], [Address], [HomePhone], [Extension], [PhotoPath], [Notes], [ManagerID], [Salary]) VALUES (25, N'陳 旻', N'工程師', N'先生', CAST(N'1967-06-15 00:00:00.000' AS DateTime), CAST(N'1999-07-07 00:00:00.000' AS DateTime), N'台中市復興一路333巷555號6樓', NULL, N'2348', NULL, NULL, 12, 37800)
INSERT [dbo].[Employees] ([EmployeeID], [EmployeeName], [Title], [TitleOfCourtesy], [BirthDate], [HireDate], [Address], [HomePhone], [Extension], [PhotoPath], [Notes], [ManagerID], [Salary]) VALUES (26, N'蔡一心', N'業務', N'先生', CAST(N'1969-08-03 00:00:00.000' AS DateTime), CAST(N'2001-06-16 00:00:00.000' AS DateTime), N'高雄縣鳳山市澄清路255巷532號', NULL, N'4321', NULL, NULL, 34, 55700)
INSERT [dbo].[Employees] ([EmployeeID], [EmployeeName], [Title], [TitleOfCourtesy], [BirthDate], [HireDate], [Address], [HomePhone], [Extension], [PhotoPath], [Notes], [ManagerID], [Salary]) VALUES (27, N'田登文', N'業務', N'先生', CAST(N'1981-01-29 00:00:00.000' AS DateTime), CAST(N'1998-01-05 00:00:00.000' AS DateTime), N'桃園市中山路1025巷1234號', NULL, N'5791', NULL, NULL, 34, 49400)
INSERT [dbo].[Employees] ([EmployeeID], [EmployeeName], [Title], [TitleOfCourtesy], [BirthDate], [HireDate], [Address], [HomePhone], [Extension], [PhotoPath], [Notes], [ManagerID], [Salary]) VALUES (28, N'嚴 正', N'業務', N'先生', CAST(N'1974-02-27 00:00:00.000' AS DateTime), CAST(N'2003-01-04 00:00:00.000' AS DateTime), N'高雄縣武廟路912號', NULL, N'3972', NULL, NULL, 2, 52000)
INSERT [dbo].[Employees] ([EmployeeID], [EmployeeName], [Title], [TitleOfCourtesy], [BirthDate], [HireDate], [Address], [HomePhone], [Extension], [PhotoPath], [Notes], [ManagerID], [Salary]) VALUES (29, N'王明田', N'業務', N'先生', CAST(N'1973-04-18 00:00:00.000' AS DateTime), CAST(N'2003-12-17 00:00:00.000' AS DateTime), N'桃園市中山路100號', NULL, N'1253', NULL, NULL, 2, 36000)
INSERT [dbo].[Employees] ([EmployeeID], [EmployeeName], [Title], [TitleOfCourtesy], [BirthDate], [HireDate], [Address], [HomePhone], [Extension], [PhotoPath], [Notes], [ManagerID], [Salary]) VALUES (30, N'林天生', N'助理', N'先生', CAST(N'1967-06-04 00:00:00.000' AS DateTime), CAST(N'2003-01-26 00:00:00.000' AS DateTime), N'新竹市三民路912號', NULL, N'4587', NULL, NULL, 23, 32300)
INSERT [dbo].[Employees] ([EmployeeID], [EmployeeName], [Title], [TitleOfCourtesy], [BirthDate], [HireDate], [Address], [HomePhone], [Extension], [PhotoPath], [Notes], [ManagerID], [Salary]) VALUES (31, N'湯玉婷', N'業務', N'小姐', CAST(N'1968-10-24 00:00:00.000' AS DateTime), CAST(N'2002-08-28 00:00:00.000' AS DateTime), N'新竹市文化路355號', NULL, N'4328', NULL, NULL, 5, 44800)
INSERT [dbo].[Employees] ([EmployeeID], [EmployeeName], [Title], [TitleOfCourtesy], [BirthDate], [HireDate], [Address], [HomePhone], [Extension], [PhotoPath], [Notes], [ManagerID], [Salary]) VALUES (32, N'林秀吉', N'業務', N'先生', CAST(N'1980-03-19 00:00:00.000' AS DateTime), CAST(N'2002-05-12 00:00:00.000' AS DateTime), N'桃園市中正路65巷55號', NULL, N'3574', NULL, NULL, 5, 58800)
INSERT [dbo].[Employees] ([EmployeeID], [EmployeeName], [Title], [TitleOfCourtesy], [BirthDate], [HireDate], [Address], [HomePhone], [Extension], [PhotoPath], [Notes], [ManagerID], [Salary]) VALUES (33, N'毛高雯', N'業務', N'小姐', CAST(N'1969-10-10 00:00:00.000' AS DateTime), CAST(N'1998-04-30 00:00:00.000' AS DateTime), N'台北市木柵三路100號5樓', NULL, N'2567', NULL, NULL, 5, 41500)
INSERT [dbo].[Employees] ([EmployeeID], [EmployeeName], [Title], [TitleOfCourtesy], [BirthDate], [HireDate], [Address], [HomePhone], [Extension], [PhotoPath], [Notes], [ManagerID], [Salary]) VALUES (34, N'蔡天林', N'業務經理', N'先生', CAST(N'1974-08-01 00:00:00.000' AS DateTime), CAST(N'1999-06-19 00:00:00.000' AS DateTime), N'台中市文化路700號', NULL, N'6782', NULL, NULL, NULL, 60200)
INSERT [dbo].[Employees] ([EmployeeID], [EmployeeName], [Title], [TitleOfCourtesy], [BirthDate], [HireDate], [Address], [HomePhone], [Extension], [PhotoPath], [Notes], [ManagerID], [Salary]) VALUES (35, N'傅文章', N'業務', N'先生', CAST(N'1970-07-16 00:00:00.000' AS DateTime), CAST(N'2000-01-31 00:00:00.000' AS DateTime), N'新竹市富明街72巷16號', NULL, N'1234', NULL, NULL, 8, 33400)
INSERT [dbo].[Employees] ([EmployeeID], [EmployeeName], [Title], [TitleOfCourtesy], [BirthDate], [HireDate], [Address], [HomePhone], [Extension], [PhotoPath], [Notes], [ManagerID], [Salary]) VALUES (36, N'劉富生', N'業務', N'先生', CAST(N'1981-03-23 00:00:00.000' AS DateTime), CAST(N'1999-04-21 00:00:00.000' AS DateTime), N'台南市仁里路128號', NULL, N'4682', NULL, NULL, 8, 46600)
INSERT [dbo].[Employees] ([EmployeeID], [EmployeeName], [Title], [TitleOfCourtesy], [BirthDate], [HireDate], [Address], [HomePhone], [Extension], [PhotoPath], [Notes], [ManagerID], [Salary]) VALUES (37, N'呂應宏', N'助理', N'先生', CAST(N'1979-03-14 00:00:00.000' AS DateTime), CAST(N'1999-01-12 00:00:00.000' AS DateTime), N'高雄縣鳳山市泰元街72巷12號', NULL, N'4679', NULL, NULL, 22, 31800)
INSERT [dbo].[Employees] ([EmployeeID], [EmployeeName], [Title], [TitleOfCourtesy], [BirthDate], [HireDate], [Address], [HomePhone], [Extension], [PhotoPath], [Notes], [ManagerID], [Salary]) VALUES (38, N'李石柱', N'工程師', N'先生', CAST(N'1971-01-16 00:00:00.000' AS DateTime), CAST(N'1998-03-07 00:00:00.000' AS DateTime), N'台北市仁愛路二段66巷12號20樓', NULL, N'4762', NULL, NULL, 12, 33900)
INSERT [dbo].[Employees] ([EmployeeID], [EmployeeName], [Title], [TitleOfCourtesy], [BirthDate], [HireDate], [Address], [HomePhone], [Extension], [PhotoPath], [Notes], [ManagerID], [Salary]) VALUES (39, N'黃智文', N'業務', N'先生', CAST(N'1978-11-22 00:00:00.000' AS DateTime), CAST(N'2003-04-25 00:00:00.000' AS DateTime), N'桃園市河北路107號', NULL, N'6874', NULL, NULL, 5, 54700)
INSERT [dbo].[Employees] ([EmployeeID], [EmployeeName], [Title], [TitleOfCourtesy], [BirthDate], [HireDate], [Address], [HomePhone], [Extension], [PhotoPath], [Notes], [ManagerID], [Salary]) VALUES (40, N'丁慧語', N'業務', N'小姐', CAST(N'1969-10-10 00:00:00.000' AS DateTime), CAST(N'2001-08-15 00:00:00.000' AS DateTime), N'台中市鎮北路96號', NULL, N'6847', NULL, NULL, 23, 37300)
為了有最佳的效能，我也參考的範例程式，寫了一個連續 Update 10,000 次的 Native_Complier 的 Stored Procedure
CREATE PROCEDURE [dbo].[UpdateSalary]
WITH
NATIVE_COMPILATION,
SCHEMABINDING,
EXECUTE AS OWNER
AS
BEGIN ATOMIC
WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = 'us_english')
-- insert T-SQL here
DECLARE @minRandomValue INT
DECLARE @maxRandomValue INT
DECLARE @randomNumber INT
DECLARE @MaxCount INT
SET @MaxCount = 0 ;
WHILE @MaxCount < 10000
BEGIN
SELECT @minRandomValue = MIN(EmployeeID),@maxRandomValue=MAX(EmployeeID) FROM [dbo].[Employees] ;
SET @randomNumber = Cast(((@maxRandomValue + 1) - @minRandomValue) * Rand() + @minRandomValue AS INT) ;
UPDATE [dbo].[Employees] SET [Salary] = [Salary] + 100 WHERE [EmployeeID] = @randomNumber ;
SET @MaxCount=@MaxCount+ 1 ;
END
END
連續測試 10 次下來，平均大約在 2s 內( 最長約 3s ) 可以執行完畢。但相對的如果我們是建立成為一般的 Table 的話，則平均約 4~5 秒，我想大概是因為我的測試資料只有 30 幾筆，因此沒有辦法凸顯出來這樣的效益。如果大家有興趣，可以參考上述的範例來進行測試，但有幾個地方個人建議要注意一下，一個是在建立 MEMORY_OPTIMIZED 的 Table 的時候，要注意 BUCKET_COUNT 的設定值，最好能接近資料筆數，如果落差太大的時候，可能會造成反效果，反而會比一般的 Table 的處理還慢上許多；另外一個就是 MEMORY_OPTIMIZED 的 Table 不能使用 TRUNCATE 的指令，只能使用 DELETE 的指令，但以往如果資料量很大的時候，DELETE 又沒有限制範圍，會造成很多的時間花在寫 Log 檔案，但因為 MEMORY_OPTIMIZED 的 Table 沒有 Log 的機制，因此速度上就會非常的快速，可以說真的是秒殺。
PS. 要是資料庫建立沒有特別指定定序的話，則可能會出現以下的錯誤訊息，或許等正式版本出來的時候就沒有這樣的限制了。
