SQLServer2014内存优化表评测

------------------------- 1. 建库 -------------------------
USE [master]
GO
if exists(select * from sysdatabases where name='DB_TEST_MEMTB')  
    DROP DATABASE DB_TEST_MEMTB  
go  
CREATE DATABASE [DB_TEST_MEMTB]
 ON  PRIMARY
(
    NAME = N'DB_TEST_MEMTB_DATA',
    FILENAME = N'e:\db\test\DB_TEST_MEMTB_DATA.mdf',
    SIZE = 512000KB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 1024KB
), 
--下面的文件就是数据流文件了
FILEGROUP [MEM_DIR] CONTAINS MEMORY_OPTIMIZED_DATA  DEFAULT
(
    NAME = N'DB_TEST_MEMTB_DIR',
    FILENAME =N'e:\db\test\DB_TEST_MEMTB_DIR',
    MAXSIZE = UNLIMITED
)
LOG ON 
(
    NAME = N'DB_TEST_MEMTB_LOG',
    FILENAME = N'e:\db\test\DB_TEST_MEMTB_LOG.ldf',
    SIZE = 512000KB,
    MAXSIZE = 2048GB,
    FILEGROWTH = 1024KB
)
GO
------------------------- 2. 建表和本地编译存储过程 -------------------------
USE DB_TEST_MEMTB
GO
-- 1. 建立普通磁盘表
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[t_disk]') AND type in (N'U'))
	DROP TABLE [dbo].[t_disk]
GO
create table [t_disk]
(
	c1 int not null primary key,
	c2 nchar(48) not null
)
go
-- 2. 建立内存优化表 (后面的测试不使用本地编译存储过程)
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[t_mem]') AND type in (N'U'))
	DROP TABLE [dbo].[t_mem]
GO
create table [t_mem]
(
	c1 int not null primary key nonclustered hash with (bucket_count=10000000),
	c2 nchar(48) not null
) with (memory_optimized=on, durability = schema_and_data)
GO
-- 3.0 建立内存优化表 (后面的测试使用本地编译存储过程 NATIVE_COMPILATION)
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[t_mem_nc]') AND type in (N'U'))
	DROP TABLE [dbo].t_mem_nc
GO
create table t_mem_nc
(
	c1 int not null primary key nonclustered hash with (bucket_count=10000000),
	c2 nchar(48) not null
) with (memory_optimized=on, durability = schema_and_data)
GO
-- 3.1 本地编译存储过程_insert
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Proc_t_mem_nc_Insert]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[Proc_t_mem_nc_Insert]
GO
CREATE PROCEDURE [Proc_t_mem_nc_Insert] 
       @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].t_mem_nc values (@i, @c)
                 set @i += 1
       end
END
GO
-- 3.2 本地编译存储过程_delete
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Proc_t_mem_nc_delete]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[Proc_t_mem_nc_delete]
GO
CREATE PROCEDURE [Proc_t_mem_nc_delete]
	@rowcount int
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
                 DELETE FROM dbo.t_mem_nc WHERE c1=@i
                 set @i += 1
       end
END
GO
-- 3.3 本地编译存储过程_update
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Proc_t_mem_nc_update]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[Proc_t_mem_nc_update]
GO
CREATE PROCEDURE [Proc_t_mem_nc_update]
	@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
                 UPDATE dbo.t_mem_nc SET c2=@c WHERE c1=@i
                 set @i += 1
       end
END
GO
-- 3.4 本地编译存储过程_select
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Proc_t_mem_nc_select]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[Proc_t_mem_nc_select]
GO
CREATE PROCEDURE [Proc_t_mem_nc_select]
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS 
 BEGIN ATOMIC 
 WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
	SELECT c1,c2 FROM dbo.t_mem_nc
END
GO
------------------------- 3. 效率评测 -------------------------
DECLARE @i INT=1,@iMax INT = 1000000      --最大一百万条记录
DECLARE @v NCHAR(48)='123456789012345678901234567890123456789012345678'
DECLARE @t DATETIME2 = sysdatetime()
--3.1 insert 
--
set nocount on
while @i<=@iMax
begin
	insert into t_disk (c1,c2) values(@i, @v)
	set @i+=1
end
select 'insert (t_disk): '+ convert(varchar(10),  datediff(ms, @t, sysdatetime()))
--
set @i=1
set @t=SYSDATETIME()
while @i<=@iMax
begin
	insert into t_mem (c1,c2) values(@i, @v)
	set @i+=1
end
select 'insert (t_mem): '+ convert(varchar(10),  datediff(ms, @t, sysdatetime()))
--
set @t=SYSDATETIME()
exec [Proc_t_mem_nc_Insert] 
	   @rowcount=@iMax,
       @c=@v
select 'insert (t_mem_nc): '+ convert(varchar(10),  datediff(ms, @t, sysdatetime()))
--结果：
--insert (t_disk):   242111
--insert (t_mem):    221358
--insert (t_mem_nc):   2147

--insert (t_disk):   243174
--insert (t_mem):    223465
--insert (t_mem_nc):   2214

--3.2 update
--时间较长，故分段执行另设变量
DECLARE @u INT=1,@uMax INT = 1000000      --最大一百万条记录
DECLARE @uv NCHAR(48)='1234567890123456789012345678901234567890abcdefgh'
DECLARE @ut DATETIME2 = sysdatetime()
set nocount on
while @u<=@uMax
begin
	update t_disk set c2=@uv where c1=@u
	set @u+=1
end
select 'update (t_disk): '+ convert(varchar(10),  datediff(ms, @ut, sysdatetime()))
--
set @u=1
set @ut=SYSDATETIME()
while @u<=@uMax
begin
	update t_mem set c2=@uv where c1=@u
	set @u+=1
end
select 'update (t_mem): '+ convert(varchar(10),  datediff(ms, @ut, sysdatetime()))
--
set @ut=SYSDATETIME()
exec [Proc_t_mem_nc_Update] 
	   @rowcount=@uMax,
       @c=@uv
select 'update (t_mem_nc): '+ convert(varchar(10),  datediff(ms, @ut, sysdatetime()))
--update (t_disk):    199369
--update (t_mem):     368297
--update (t_mem_nc):    3715

--update (t_disk):    203251
--update (t_mem):     355356
--update (t_mem_nc):    3732

--3.3 select 
DECLARE @st DATETIME2 = sysdatetime()
set nocount on
--
select c1,c2 from t_disk
select 'select (t_disk): '+ convert(varchar(10),  datediff(ms, @st, sysdatetime()))
set @st=SYSDATETIME()
select c1,c2 from t_mem
select 'select (t_mem): '+ convert(varchar(10),  datediff(ms, @st, sysdatetime()))
set @st=SYSDATETIME()
exec Proc_t_mem_nc_select
select 'select (t_mem_nc): '+ convert(varchar(10),  datediff(ms, @st, sysdatetime()))
--select (t_disk):   8934
--select (t_mem):    9278
--select (t_mem_nc): 8889

--select (t_disk):   8861
--select (t_mem):    9978
--select (t_mem_nc): 9108

--3.4 delete
--时间较长，故分段执行另设变量
DECLARE @d INT=1,@dMax INT = 1000000      --最大一百万条记录
DECLARE @dt DATETIME2 = sysdatetime()
set nocount on
while @d<=@dMax
begin
	delete from t_disk where c1=@d
	set @d+=1
end
select 'delete (t_disk): '+ convert(varchar(10),  datediff(ms, @dt, sysdatetime()))
--
set @d=1
set @dt=SYSDATETIME()
while @d<=@dMax
begin
	delete from t_mem where c1=@d
	set @d+=1
end
select 'delete (t_mem): '+ convert(varchar(10),  datediff(ms, @dt, sysdatetime()))
--
set @dt=SYSDATETIME()
exec [dbo].[Proc_t_mem_nc_delete] @rowcount=@dMax
select 'delete (t_mem_nc): '+ convert(varchar(10),  datediff(ms, @dt, sysdatetime()))

--delete (t_disk): 199438
--delete (t_mem):  342959
--delete (t_mem_nc):  928

--delete (t_disk): 199637
--delete (t_mem):  341771
--delete (t_mem_nc):  803