标签归档:内存表

mssql sqlserver 内存表简介(二)


摘要:
下面将演示内存表中select的检索速度,如下所示:

1.select count速度统计

 SET STATISTICS IO
 SET TIME ON
SELECT COUNT(*) FROM Sales.SalesOrderDetail_inmem
SELECT COUNT(*) FROM Sales.SalesOrderDetail_inmemcs
SELECT COUNT(*) FROM Sales.SalesOrderDetail_disk
 
inmem: CPU time = 989 ms, elapsed time = 140 ms. -- query cost 38%	--(表扫描)
inmemCS: CPU time = 21 ms, elapsed time = 120 ms. -- query cost 6%	--(列扫描) 
disk: CPU time = 1088 ms, elapsed time = 178 ms. -- query cost 48%	--(非聚集索引扫描)

2.采用in关键字,统计几行数据的情况分析

SELECT * FROM Sales.SalesOrderDetail_inmem  
WHERE SalesOrderID IN (12801,12802,12806)
SELECT * FROM Sales.SalesOrderDetail_inmemcs
WHERE SalesOrderID IN (12801,12802,12806)
SELECT * FROM Sales.SalesOrderDetail_disk
WHERE SalesOrderID IN (12801,12802,12806)

inmem:	CPU time = 0 ms, elapsed time = 89 ms.	-- query cost 18% (非聚集索引检索)
inmemCS:	CPU time = 0 ms, elapsed time = 78 ms.	-- query cost 18% (非聚集索引检索)
disk:	CPU time = 0 ms, elapsed time = 88 ms.	-- query cost 68% (聚集索引检索)

3.采用where关键字,检索指定ID的数据

 ---由于存在数据缓存,所以内存表和硬盘表的性能没有多大差异 
 
SELECT * FROM Sales.SalesOrderDetail_inmem
WHERE ProductID = 717
SELECT *  FROM Sales.SalesOrderDetail_inmemcs
WHERE ProductID = 717
SELECT *   FROM Sales.SalesOrderDetail_disk
WHERE ProductID = 717

inmem: CPU time = 16ms, elapsed time = 181ms.	-- query cost 1% (nonclustered index seek)
inmemCS: CPU time = 16ms, elapsed time = 268ms.	-- query cost 1% (nonclustered index seek)
disk: CPU time = 62 ms, elapsed time = 150ms.	-- query cost 99% (nonclustered index seek
with key lookup)

3.采用where between关键字

 

SELECT * FROM Sales.SalesOrderDetail_inmem
WHERE ModifiedDate BETWEEN '2018-5-1' AND '2018-5-30'
SELECT * FROM Sales.SalesOrderDetail_inmemcs
WHERE ModifiedDate BETWEEN '2018-5-1' AND '2018-5-30'
SELECT * FROM Sales.SalesOrderDetail_disk
WHERE ModifiedDate BETWEEN '2018-5-1' AND '2018-5-30'

inmem:	CPU time=130 ms, elapsed time = 980 ms. -- query cost 6%; 非聚集索引查询
inmemCS: CPU time=98 ms, elapsed time = 975 ms.	-- query cost 2%; 索引扫描
disk: CPU time=499 ms, elapsed time = 960 ms.	-- query cost 89%; 聚集索引查询

mssql sqlserver 内存表简介(一)


摘要:
sqlserver内存表,是sqlserver2014版本开始拥有的功能,下文对内存表进行简要的说明,和采用sql对内存表的性能进行测试
测试环境:
内存64GB
硬盘 1TB SSD
处理器 i7



内存表-写在前面的话

在计算机出现的初期,内存非常的昂贵,随着科学技术的发展,目前内存的价格越来越便宜,成本已经被的可以承受,
CPU的处理性能也得到了质的飞跃,在数据库的处理能力提升方面,我们通常从并行任务和减少数据的访问路径和访问速度上下手优化,
那么sqlserver内存表就产生了,它的产生:
可以使用sqlserver的性能提升十倍
将更多的数据缓存至内存中,加大数据的处理速度
同样的操作,我们需要的计算机指令更少(内存表展现了一种新的数据存储和访问方式)
———————————————————————————–

内存表-创建方法

例1:创建一个内存表 SalesOrderDetail_inmem:

CREATE TABLE [Sales].[SalesOrderDetail_inmem](
[SalesOrderID] [int] NOT NULL INDEX IXSalesOrderDetail_inmem__SalesOrderID NONCLUSTERED,
[SalesOrderDetailID] [int] NOT NULL IDENTITY(1,1),
[CarrierTrackingNumber] [nvarchar](25) NULL,
[OrderQty] [smallint] NOT NULL,
[ProductID] [int] NOT NULL INDEX IX_SalesOrderDetail_inmem_ProductID NONCLUSTERED,
[SpecialOfferID] [int] NOT NULL,
[UnitPrice] [money] NOT NULL,
[UnitPriceDiscount] [money] NOT NULL ,
[ModifiedDate] [datetime] NOT NULL INDEX IX_SalesOrderDetail_inmem_ModifiedDate
NONCLUSTERED,
CONSTRAINT [imPK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID] PRIMARY KEY NONCLUSTERED
HASH ([SalesOrderID],[SalesOrderDetailID]) WITH (BUCKET_COUNT=8388608)	)
WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_AND_DATA);

创建基于磁盘的表时,拥有相似的语法。非聚集单列索引的定义与往常一样。
主要区别是带有HASH和主键定义,以及最后一条语句(WITH)打开优化的内存并指定了DURABILITY,
使用schema_and_data关键字,数据在SQL Server关闭和启动之间保留。
如果需要空表,则会指定SCHEMA_ONLY。仅限于模式的表格也可以用来代替临时表格。
————————————————————————————-
例2:
创建一个带有列存储聚簇索引的重复内存表 SalesOrderDetail_inmemcs,如下所示:

CREATE TABLE [Sales].[SalesOrderDetail_inmemcs](
[SalesOrderID] [int] NOT NULL INDEX IXSalesOrderDetail_inmem__SalesOrderID NONCLUSTERED,
[SalesOrderDetailID] [int] NOT NULL IDENTITY(1,1),
[CarrierTrackingNumber] [nvarchar](25) NULL,
[OrderQty] [smallint] NOT NULL,
[ProductID] [int] NOT NULL INDEX IX_SalesOrderDetail_inmem_ProductID NONCLUSTERED,
[SpecialOfferID] [int] NOT NULL,
[UnitPrice] [money] NOT NULL,
[UnitPriceDiscount] [money] NOT NULL ,
[ModifiedDate] [datetime] NOT NULL INDEX IX_SalesOrderDetail_inmem_ModifiedDate
NONCLUSTERED,
CONSTRAINT [imPK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID] PRIMARY KEY NONCLUSTERED
HASH ([SalesOrderID],[SalesOrderDetailID]) WITH (BUCKET_COUNT=6000000)	)
WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_AND_DATA);
 
ALTER TABLE [Sales].[SalesOrderDetail_inmemcs] ADD INDEX CIX_SalesOrderDetail_inmemscs CLUSTERED COLUMNSTORE; --add ColumnStore index

-----建立一个类似的磁盘表,进行相关测试和对比:

CREATE TABLE [Sales].[SalesOrderDetail_disk](
[SalesOrderID] [int] NOT NULL,
[SalesOrderDetailID] [int] NOT NULL IDENTITY(1,1),
[CarrierTrackingNumber] [nvarchar](25) NULL,
[OrderQty] [smallint] NOT NULL,
[ProductID] [int] NOT NULL,
[SpecialOfferID] [int] NOT NULL,
[UnitPrice] [money] NOT NULL,
[UnitPriceDiscount] [money] NOT NULL ,
[ModifiedDate] [datetime] NOT NULL ,
CONSTRAINT [PK_tTemp] PRIMARY KEY CLUSTERED
([SalesOrderID] ASC,[SalesOrderDetailID] ASC));

CREATE NONCLUSTERED INDEX IDX_SalesOrder_SalesOrderID
ON [Sales].[SalesOrderDetail_disk] (SalesOrderID)
CREATE NONCLUSTERED INDEX IDX_SalesOrder_Product
ON [Sales].[SalesOrderDetail_disk] (ProductID)
INCLUDE (SalesOrderID,UnitPrice)
CREATE NONCLUSTERED INDEX IDX_SalesOrder_ModifiedDate
ON [Sales].[SalesOrderDetail_disk] (ModifiedDate)

 

——————————–内存表输入插入耗时
内存数据插入(和删除)
对于以下所有演示,我使用SET STATISTICS IO,TIME ON,并打开包含实际执行计划。对于第一个演示,我们将看看一个BULK INSERT:

BULK INSERT Sales.SalesOrderDetail_inmem	--364,152 行
FROM 'E:\TestData\SalesOrderDetail.txt'
WITH (FIELDTERMINATOR = '\t',ROWTERMINATOR = '\n' );
BULK INSERT Sales.SalesOrderDetail_inmemcs	--364,152 行
FROM 'E:\TestData\SalesOrderDetail.txt'
WITH (FIELDTERMINATOR = '\t',ROWTERMINATOR = '\n' );
BULK INSERT Sales.SalesOrderDetail_disk	--364,152 行
FROM 'E:\TestData\SalesOrderDetail.txt'
WITH (FIELDTERMINATOR = '\t',ROWTERMINATOR = '\n' );

Table 'SalesOrderDetail_disk'. Scan count 0, logical reads 39099, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 3, logical reads 1299927, physical reads 0, read-ahead reads 7012, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

inmem: CPU time = 1,438 ms, elapsed time = 1,533 ms. -- Query cost 24%
inmemCS: CPU time = 1,578 ms, elapsed time = 1,631 ms. -- Query cost 24%
disk: CPU time = 3,996 ms, elapsed time = 3,989 ms. -- Query cost 50%

———–内存表数据delete删除效率分析

DELETE Sales.SalesOrderDetail_inmem; –364,152 行
DELETE Sales.SalesOrderDetail_inmemcs; –364,152 行
DELETE Sales.SalesOrderDetail_disk; –364,152 行

Table ‘SalesOrderDetail_disk’. Scan count 1, logical reads 6258380, physical reads 0, read-ahead reads 7,…
/*
inmem: CPU time = 98 ms, elapsed time = 182 ms. — query cost 6%
inmemCS: CPU time = 97 ms, elapsed time =196 ms. — query cost 6%
disk: CPU time = 4,986 ms, elapsed time = 4,989 ms. — query cost 79%

———–内存表数据update更新效率分析
UPDATE Sales.SalesOrderDetail_inmem
SET UnitPrice = UnitPrice * 1.05;
UPDATE Sales.SalesOrderDetail_inmemcs
SET UnitPrice = UnitPrice * 1.05;
UPDATE Sales.SalesOrderDetail_Disk
SET UnitPrice = UnitPrice * 1.05;

—–输出信息

inmem: CPU time = 9,898 ms, elapsed time = 9,865 ms. — Query cost: 4%
inmemCS: CPU time = 18,875 ms, elapsed time = 19,389 ms. — Query cost: 3%
–硬盘表更新耗时会更长
disk: CPU time = 98,635 ms, elapsed time = 105,217 ms. — Query cost: 89%


注意事项:
如果电脑的内存不是足够大,那么使用内存表将得不到最优的效果