SQL Server：局部变量是怎样降低查询性能的：报错

数据库开发人员会使用存储过程和脚本中的局部变量并在这些局部变量的基础上来放置过滤器，这是一个很常见的做法。是的，这些局部变量会减慢你的查询，让我们来证实它。

创建一张新的表并插入一些数据。

USEAdventureWorks
GO
CREATE TABLE TempTable
      (tempID UNIQUEIDENTIFIER,tempMonth INT,tempDateTime DATETIME )
GO

INSERT INTO TempTable (tempID, tempMonth, tempDateTime)
SELECT NEWID(),(CAST(100000*RAND() AS INT) % 12) + 1 ,GETDATE()
GO 100000 -- (EXECUTE THIS BATCH 100000 TIME) 

<pre>-- Create an index to support our query
CREATE NONCLUSTERED INDEX[IX_tempDateTime] ON [dbo].[TempTable]
([tempDateTime] ASC)
INCLUDE ( [tempID]) WITH ( ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
Now let’s execute a simple query with hard coded values in WHERE clause

SET STATISTICS IO ON
GO
SELECT * FROM TempTable
WHEREtempDateTime > '2012-07-10 03:18:01.640'

---------------------------------------------
表'TEMPTABLE'。扫描计数1，逻辑读取80次，物理读0次，预读0次，lob逻辑读取0次，lob物理读取0次，lob预读0次。

检查它的执行计划和索引查找属性。你可以发现估计的行数（Estimated Number of Rows）是实际行数的两倍，但那不是一个很大的区别来足以影响到执行计划，并且优化器最终会选择一个合适的计划去执行这个查询。

查询优化器已经从它的基本统计直方图中估计出了这个行数，如：EQROWS + AVGRANGE_ROWS (77 + 88.64286)

DBCC SHOW_STATISTICS ('dbo.TempTable', IX_tempDateTime)

现在，我们修改下我们的SELECT查询使用局部变量并执行它。你会发现这次查询优化器已经选择了一个不同的计划，一个开销更大的计划。这是为什么呢？

DECLARE@RequiredDate DATETIME
SET@RequiredDate = '2012-07-10 03:18:01.640'

SELECT * FROM TempTable
WHEREtempDateTime  >@RequiredDate

--------------------------------------------- 
表'TEMPTABLE'。扫描计数1，逻辑读取481次，物理读0次，预读0次，lob逻辑读取0次，lob物理读取0次，lob预读0次。

估计的行数（ Estimated Number of Rows ）和实际行数的巨大差别很明确的表明查询优化器不能正确的估算出行数了，并因此选择了一个代价更大的计划。从根本上说，查询优化器在优化的时候并不知道局部变量的值所以不能使用统计直方图。它有了不同的表现，使用了不等和相等的操作。

不等操作：

在我们查询中的不等操作，查询优化器使用了一个简单的公式（30%)来计算总行数。

Estimated Rows =(Total Rows * 30)/100 = (100000*30)/100 = 30000

相等操作：

DECLARE@RequiredDate DATETIME
SET@RequiredDate = '2012-07-10 03:18:01.640'

SELECT * FROM TempTable
WHEREtempDateTime  =@RequiredDate

如果一个运算符使用了局部变量，查询优化器使用一个不同的公式来得到估计的行数。如：比例 * 表的总行数。执行下面的查询得到比例的值。

DBCC SHOW_STATISTICS ('dbo.TempTable', IX_tempDateTime)

所有比例 = 0.0007358352
表的总行数 = 100000
估计行数 = Density * Total Number = 0.0007358352 * 100000 = 73.5835

删除不需要的表

DROP TABLE TempTable

OSCHINA编译，原文链接