SQL Server: 局部变量是如何影响查询性能的

数据库 SQL Server
数据库开发者在存储过程和脚本中使用局部变量是很常见的事情,但是,局部变量会影响查询的性能,接下来我们来证实这一点。

数据库开发者在存储过程和脚本中使用局部变量是很常见的事情,但是,局部变量会影响查询的性能,接下来我们来证实这一点。

首先让我们创建一个表并插入一些测试数据:

  1. USE AdventureWorks  
  2. GO  
  3. CREATE TABLE TempTable  
  4.       (tempID UNIQUEIDENTIFIER,tempMonth INT, tempDateTime DATETIME )  
  5. GO  
  6.  
  7. INSERT INTO TempTable (tempID, tempMonth, tempDateTime)  
  8. SELECT NEWID(),(CAST(100000*RAND() AS INT) % 12) + 1 ,GETDATE()  
  9. GO 100000 -- (EXECUTE THIS BATCH 100000 TIME)  
  10.  
  11. -- Create an index to support our query  
  12. CREATE NONCLUSTERED INDEX [IX_tempDateTime] ON [dbo].[TempTable]  
  13. ([tempDateTime] ASC)  
  14. INCLUDE ( [tempID]) WITH ( ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ONON [PRIMARY]  
  15. GO 

然后我们做一个简单的查询:

  1. SET STATISTICS IO ON 
  2. GO  
  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.

检查这个执行计划以及索引检索的属性,你会发现预估行数是实际行数的两倍,但并不会太影响执行计划,因为优化器选择了最合适的查询方法:

 

 

 

 

查询优化器根据基本统计直方图来预估数据行数,即:EQ_ROWS + AVG_RANGE_ROWS (77 + 88.64286) DBCC SHOW_STATISTICS ('dbo.TempTable', IX_tempDateTime) 

 

 

现在我们修改 SELECT 语句以使用局部变量,你会发现查询优化器使用了一个不同的查询计划,这是一个更耗时的计划,为什么?

  1. DECLARE @RequiredDate DATETIME  
  2. SET @RequiredDate = '2012-07-10 03:18:01.640' 
  3.  
  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. 

 

 

预估值和实际值差别更大,相当于查询优化器无法选择最适合的查询计划,因为错误的预估值。因为查询优化在执行时并不清楚局部变量值,导致无法使用统计直方图。

不等式运算符的情况

在我们的查询中使用的不等式运算符,因此查询优化器使用了一个简单的 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' 
  3.  
  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.connectsql.blogspot.tw/2012/07/sql-server-how-local-variables-can.html

原文链接:http://www.oschina.net/question/12_60607

责任编辑:林师授 来源: OSCHINA
相关推荐

2024-07-09 08:35:09

2009-09-17 13:05:38

Linq局部变量类型

2020-11-11 21:26:48

函数变量

2009-08-26 16:37:07

C#迭代器局部变量

2010-03-15 09:32:56

Python函数

2024-05-29 08:49:22

Python全局变量局部变量

2015-01-07 14:41:32

Android全局变量局部变量

2018-05-14 09:15:24

Python变量函数

2009-09-11 10:07:05

Linq隐式类型化局部

2009-10-12 14:13:00

VB.NET使用局部变

2009-12-15 10:48:54

Ruby局部变量

2020-10-26 07:07:50

线程安全框架

2017-02-08 12:28:37

Android变量总结

2009-09-22 17:21:24

线程局部变量

2009-08-27 10:08:36

C#隐含类型局部变量

2021-09-28 07:12:09

函数内存

2010-10-14 09:34:34

JVM局部变量

2009-03-06 09:42:16

性能索引

2011-11-23 10:59:18

Javafinal

2010-01-08 15:22:22

VB.NET局部变量
点赞
收藏

51CTO技术栈公众号