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%; 聚集索引查询