51Testing软件测试网3C
K8R"b2pW{2B#X 数据库开发者在存储过程和脚本中使用局部变量是很常见的事情,但是,局部变量会影响查询的性能,接下来我们来证实这一点。51Testing软件测试网 y N
zrGw$A
:m]%zNmL0 首先让我们创建一个表并插入一些测试数据:51Testing软件测试网Y9g(t9Yf'M
- USE AdventureWorks
- GO
- CREATE TABLE TempTable
- (tempID UNIQUEIDENTIFIER,tempMonth INT, tempDateTime DATETIME )
- GO
- 51Testing软件测试网 yH J9tVn
- INSERT INTO TempTable (tempID, tempMonth, tempDateTime)
- SELECT NEWID(),(CAST(100000*RAND() AS INT) % 12) + 1 ,GETDATE()
- GO 100000
- 51Testing软件测试网aRx6q^5y"jY5]
-
- CREATE NONCLUSTERED INDEX [IX_tempDateTime] ON [dbo].[TempTable]
- ([tempDateTime] ASC)
- INCLUDE ( [tempID]) WITH ( ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
- GO
|
51Testing软件测试网.a4f/H7|9[*_4Y 然后我们做一个简单的查询:
YM
^c iG;S6C0- SET STATISTICS IO ON
- GO
- SELECT * FROM TempTable
- WHERE tempDateTime > '2012-07-10 03:18:01.640'
|
51Testing软件测试网\Z4F*jK*P\UP
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.51Testing软件测试网&f
^ wq,FgYXA5\{
:c
k'?:xn.xyC0 检查这个执行计划以及索引检索的属性,你会发现预估行数是实际行数的两倍,但并不会太影响执行计划,因为优化器选择了最合适的查询方法:
yeJ*zAD2A051Testing软件测试网j7u&f
B9o
P.R`~
51Testing软件测试网|`WX5h8@Y
查询优化器根据基本统计直方图来预估数据行数,即:EQ_ROWS + AVG_RANGE_ROWS (77 + 88.64286) DBCC SHOW_STATISTICS ('dbo.TempTable', IX_tempDateTime)
-Nf [*J J/h m-z_051Testing软件测试网{:s$I*b|y
#|fX$H7P cy4I)[.r[0 现在我们修改 SELECT 语句以使用局部变量,你会发现查询优化器使用了一个不同的查询计划,这是一个更耗时的计划,为什么?
qZ+N H.LwJ0Oo"c(r/ZF9`qnu`Q051Testing软件测试网b]P \}
n
- DECLARE @RequiredDate DATETIME
- SET @RequiredDate = '2012-07-10 03:18:01.640'
#Y2A4o/o%Di;I6vU0- SELECT * FROM TempTable
- WHERE tempDateTime > @RequiredDate
|
51Testing软件测试网9Y8I{6B*L3y(j:vT ------------------------------------------------------------------------------------------51Testing软件测试网^'h gP[nz&Ey%[
51Testing软件测试网2Gz_vk
y:\'j7nC 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.
b/EU\ r*e;w\y051Testing软件测试网l V5ew1x pa)@!M
-m"cy0_br
cB V0 预估值和实际值差别更大,相当于查询优化器无法选择最适合的查询计划,因为错误的预估值。因为查询优化在执行时并不清楚局部变量值,导致无法使用统计直方图。51Testing软件测试网:n%jsq:k0l1?+j#xB
不等式运算符的情况
lf NBh!x5~+TF2?6uC0 在我们的查询中使用的不等式运算符,因此查询优化器使用了一个简单的 30% 的算式来预估。
-S"Ur
x:Qa-EB0 Estimated Rows =(Total Rows * 30)/100 = (100000*30)/100 = 3000051Testing软件测试网T
PFy:j#y!c2VI
等式运算符的情况51Testing软件测试网#s |Rp9aa.d9q,_"c
j#`qq'F$i0- DECLARE @RequiredDate DATETIME
- SET @RequiredDate = '2012-07-10 03:18:01.640'
-
- SELECT * FROM TempTable
- WHERE tempDateTime = @RequiredDate
|
如果在局部变量中使用等式运算符,那么查询优化器又会选择不同的公式,即 精确度 * 表记录总数. 执行下面查询可获取精确的值51Testing软件测试网]2w4u3oQ`)C
DBCC SHOW_STATISTICS('dbo.TempTable', IX_tempDateTime)
/]2A4ek9z2J2z{j0 All Density = 0.0007358352 Total Number of Rows in Table = 100000
o8_$y'kyb0 Estimated Rows = Density * Total Number = 0.0007358352 * 100000 = 73.583551Testing软件测试网#a0x a(?fH(yi0x%@