数据库开发者在存储过程和脚本中使用局部变量是很常见的事情,但是,局部变量会影响查询的性能,接下来我们来证实这一点。
首先让我们创建一个表并插入一些测试数据:
03 |
CREATE TABLE TempTable |
04 |
(tempID UNIQUEIDENTIFIER,tempMonth INT , tempDateTime DATETIME ) |
07 |
INSERT INTO TempTable (tempID, tempMonth, tempDateTime) |
08 |
SELECT NEWID(),( CAST (100000*RAND() AS INT ) % 12) + 1 ,GETDATE() |
12 |
CREATE NONCLUSTERED INDEX [IX_tempDateTime] ON [dbo].[TempTable] |
14 |
INCLUDE ( [tempID]) WITH ( ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [ PRIMARY ] |
然后我们做一个简单的查询:
3 |
SELECT * FROM TempTable |
4 |
WHERE tempDateTime > '2012-07-10 03:18:01.640' |
------------------------------------------------------------------------------------------
Table 'TempTable'. Scan count 1, logical reads 80, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
检查这个执行计划以及索引检索的属性,你会发现预估行数是实际行数的两倍,但并不会太影响执行计划,因为优化器选择了最合适的查询方法:
![](http://static.oschina.net/uploads/space/2012/0711/084712_zIqv_12.png)
![](http://static.oschina.net/uploads/space/2012/0711/084720_WEOF_12.png)
查询优化器根据基本统计直方图来预估数据行数,即:EQ_ROWS + AVG_RANGE_ROWS (77 + 88.64286) DBCC SHOW_STATISTICS ('dbo.TempTable', IX_tempDateTime)
![](http://static.oschina.net/uploads/space/2012/0711/084745_PL1M_12.png)
现在我们修改 SELECT 语句以使用局部变量,你会发现查询优化器使用了一个不同的查询计划,这是一个更耗时的计划,为什么?
1 |
DECLARE @RequiredDate DATETIME |
2 |
SET @RequiredDate = '2012-07-10 03:18:01.640' |
4 |
SELECT * FROM TempTable |
5 |
WHERE tempDateTime > @RequiredDate |
------------------------------------------------------------------------------------------
Table 'TempTable'. Scan count 1, logical reads 481, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
![](http://static.oschina.net/uploads/space/2012/0711/084810_xTt4_12.gif)
![](http://static.oschina.net/uploads/space/2012/0711/084820_neaD_12.png)
预估值和实际值差别更大,相当于查询优化器无法选择最适合的查询计划,因为错误的预估值。因为查询优化在执行时并不清楚局部变量值,导致无法使用统计直方图。
不等式运算符的情况
在我们的查询中使用的不等式运算符,因此查询优化器使用了一个简单的 30% 的算式来预估。
Estimated Rows =(Total Rows * 30)/100 = (100000*30)/100 = 30000
等式运算符的情况
1 |
DECLARE @RequiredDate DATETIME |
2 |
SET @RequiredDate = '2012-07-10 03:18:01.640' |
4 |
SELECT * FROM TempTable |
5 |
WHERE tempDateTime = @RequiredDate |
如果在局部变量中使用等式运算符,那么查询优化器又会选择不同的公式,即 精确度 * 表记录总数. 执行下面查询可获取精确的值
DBCC SHOW_STATISTICS('dbo.TempTable', IX_tempDateTime)
All Density = 0.0007358352 Total Number of Rows in Table = 100000
Estimated Rows = Density * Total Number = 0.0007358352 * 100000 = 73.5835
原文地址:http://www.oschina.net/question/12_60607?from=20120715