SQL2014内存表性能之内存中 OLTP 的性能改进测试

先贴1个例子，后续补充完整的测试例子....

1、用MSDN例子测试一下

use master
go
--1、先创建包含内存优化文件组的数据库
CREATE DATABASE imoltp2
ON
PRIMARY(NAME = [imoltp2_data],
FILENAME = 'd:\data\imoltp2_mod1.mdf', size=500MB)
, FILEGROUP [imoltp2_mod] CONTAINS MEMORY_OPTIMIZED_DATA( -- name of the memory-optimized filegroup
NAME = [imoltp2_dir],  -- logical name of a memory-optimized filegroup container
FILENAME = 'd:\data\imoltp2_dir') -- physical path to the container
LOG ON (name = [imoltp2_log], Filename='d:\data\imoltp2_log.ldf', size=500MB)
GO--2、创建表和本机编译存储过程
use imoltp2
goIF EXISTS (SELECT name FROM sysobjects WHERE name = 'xx')DROP PROCEDURE xx
GOIF EXISTS (SELECT name FROM sysobjects WHERE name = 'sql')DROP TABLE sql
GOIF EXISTS (SELECT name FROM sysobjects WHERE name = 'hash')DROP TABLE hash
GOIF EXISTS (SELECT name FROM sysobjects WHERE name = 'hash1')DROP TABLE hash1
GOcreate table [sql]
(
c1 int not null primary key,
c2 nchar(48) not null
)
gocreate table [hash]
(
c1 int not null primary key nonclustered hash with (bucket_count=1000000),
c2 nchar(48) not null
) with (memory_optimized=on, durability = schema_and_data)
gocreate table [hash1]
(
c1 int not null primary key nonclustered hash with (bucket_count=1000000),
c2 nchar(48) not null
) with (memory_optimized=on, durability = schema_and_data)
goCREATE PROCEDURE xx @rowcount int,@c nchar(48)
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')declare @i int = 1while @i <= @rowcountbeginINSERT INTO [dbo].[hash1] values (@i, @c)set @i += 1end
END
GO
--3、演示内存优化表的性能
set statistics time off
set nocount on-- inserts - 1 at a timedeclare @starttime datetime2 = sysdatetime(),@timems intdeclare @i int = 1
declare @rowcount int = 100000
declare @c nchar(48) = N'12345678901234567890123456789012345678'-----------------------------
--- disk-based table and interpreted Transact-SQL
-----------------------------begin tran
while @i <= @rowcount
begininsert into [sql] values (@i, @c)set @i += 1
end
commitset @timems = datediff(ms, @starttime, sysdatetime())
select 'Disk-based table and interpreted Transact-SQL: ' + cast(@timems as varchar(10)) + ' ms'/*
Disk-based table and interpreted Transact-SQL: 1996 ms
*/
-----------------------------
--- Interop Hash
-----------------------------set @i = 1
set @starttime = sysdatetime()begin tran
while @i <= @rowcount
begininsert into [hash] values (@i, @c)set @i += 1
end
commitset @timems = datediff(ms, @starttime, sysdatetime())
select ' memory-optimized table w/ hash index and interpreted Transact-SQL: ' + cast(@timems as varchar(10)) + ' ms'
/*memory-optimized table w/ hash index and interpreted Transact-SQL: 1478 ms*/
-----------------------------
--- Compiled Hash
-----------------------------
set @starttime = sysdatetime()exec xx @rowcount, @cset @timems = datediff(ms, @starttime, sysdatetime())
select 'memory-optimized table w/hash index and native SP:' + cast(@timems as varchar(10)) + ' ms'
/*
memory-optimized table w/hash index and native SP:268 ms
*/