先贴1个例子，后续补充完整的测试例子....
1、用MSDN例子测试一下
use master
go
--1、先创建包含内存优化文件组的数据库
CREATE DATABASE imoltp2
ON
PRIMARY(NAME = [imoltp2_data],
FILENAME = 'd:dataimoltp2_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:dataimoltp2_dir') -- physical path to the container
LOG ON (name = [imoltp2_log], Filename='d:dataimoltp2_log.ldf', size=500MB)
GO
--2、创建表和本机编译存储过程
use imoltp2
go
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'xx')
DROP PROCEDURE xx
GO
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'sql')
DROP TABLE sql
GO
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'hash')
DROP TABLE hash
GO
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'hash1')
DROP TABLE hash1
GO
create table [sql]
(
c1 int not null primary key,
c2 nchar(48) not null
)
go
create 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)
go
create 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)
go
CREATE 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 = 1
while @i <= @rowcount
begin
INSERT INTO [dbo].[hash1] values (@i, @c)
set @i += 1
end
END
GO
--3、演示内存优化表的性能
set statistics time off
set nocount on
-- inserts - 1 at a time
declare @starttime datetime2 = sysdatetime(),
@timems int
declare @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
begin
insert into [sql] values (@i, @c)
set @i += 1
end
commit
set @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
begin
insert into [hash] values (@i, @c)
set @i += 1
end
commit
set @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, @c
set @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
*/