SQL SERVER分区表探寻（二）

最新推荐文章于 2024-11-16 00:08:21 发布

zhiming2076

最新推荐文章于 2024-11-16 00:08:21 发布

阅读量444

点赞数

分类专栏： SQL SERVER 文章标签： sql server 大数据数据库性能

本文链接：https://blog.csdn.net/zhiming2076/article/details/31757673

版权

SQL SERVER 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

接上篇文章的疑问。

新建一个多达120个分区的新表：pt_PatitionTable120。最终各表情况如下：

pt_PatitionTable24：60W条

pt_PatitionTable60：288W条

pt_PatitionTable120：778W条

在SQL Batch中同时执行如下语句：

select * from pt_PatitionTable24 where enterDate >= '2013-05-01' and enterDate < '2013-06-01'
select * from pt_PatitionTable60 where enterdate >= '2013-05-01' and enterdate < '2013-06-01'
select * from pt_PatitionTable120 where enterDate >= '2013-05-01' and enterDate < '2013-06-01'

查询分析结果如下：

分析“Query Cost”百分比，各个表之间的差异均不是很大。由此是否可以证明，分区的多少，并不会影响到性能呢？

再调用命令“SET STATISTICS IO ON”、“SET STATISTICS TIME ON”结果如下：

(59411 row(s) affected)
Table 'pt_PatitionTable24'. Scan count 1, logical reads 2901, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


 SQL Server Execution Times:
   CPU time = 358 ms,  elapsed time = 6409 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.


(59411 row(s) affected)
Table 'pt_PatitionTable60'. Scan count 1, logical reads 2776, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


 SQL Server Execution Times:
   CPU time = 375 ms,  elapsed time = 7201 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.


(59411 row(s) affected)
Table 'pt_PatitionTable120'. Scan count 1, logical reads 2775, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


 SQL Server Execution Times:
   CPU time = 437 ms,  elapsed time = 6548 ms.

无论是CPU执行时间，还是“Logical Reads”指标都差异不大。这是否更加预示着分区的多少不会影响到效率呢？

当然，我们有一个前提就是，我们服务器没有做RAID。