文章內(nèi)容

SQL Server: 局部變量是如何影響查詢性能的

發(fā)布時(shí)間: 2012/8/4 17:04:55

數(shù)據(jù)庫(kù)開(kāi)發(fā)者在存儲(chǔ)過(guò)程和腳本中使用局部變量是很常見(jiàn)的事情，但是，局部變量會(huì)影響查詢的性能，接下來(lái)我們來(lái)證實(shí)這一點(diǎn)。

首先讓我們創(chuàng)建一個(gè)表并插入一些測(cè)試數(shù)據(jù)：

USE AdventureWorks
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)
-- 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

然后我們做一個(gè)簡(jiǎn)單的查詢：

SET STATISTICS IO ON
GO
SELECT * FROM TempTable
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.

檢查這個(gè)執(zhí)行計(jì)劃以及索引檢索的屬性，你會(huì)發(fā)現(xiàn)預(yù)估行數(shù)是實(shí)際行數(shù)的兩倍，但并不會(huì)太影響執(zhí)行計(jì)劃，因?yàn)閮?yōu)化器選擇了最合適的查詢方法：

查詢優(yōu)化器根據(jù)基本統(tǒng)計(jì)直方圖來(lái)預(yù)估數(shù)據(jù)行數(shù)，即：EQ_ROWS + AVG_RANGE_ROWS (77 + 88.64286) DBCC SHOW_STATISTICS ('dbo.TempTable', IX_tempDateTime)

現(xiàn)在我們修改 SELECT 語(yǔ)句以使用局部變量，你會(huì)發(fā)現(xiàn)查詢優(yōu)化器使用了一個(gè)不同的查詢計(jì)劃，這是一個(gè)更耗時(shí)的計(jì)劃，為什么？

DECLARE @RequiredDate DATETIME
SET @RequiredDate = '2012-07-10 03:18:01.640'
SELECT * FROM TempTable
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.

預(yù)估值和實(shí)際值差別更大，相當(dāng)于查詢優(yōu)化器無(wú)法選擇最適合的查詢計(jì)劃，因?yàn)殄e(cuò)誤的預(yù)估值。因?yàn)椴樵儍?yōu)化在執(zhí)行時(shí)并不清楚局部變量值，導(dǎo)致無(wú)法使用統(tǒng)計(jì)直方圖。

不等式運(yùn)算符的情況

在我們的查詢中使用的不等式運(yùn)算符，因此查詢優(yōu)化器使用了一個(gè)簡(jiǎn)單的 30% 的算式來(lái)預(yù)估。

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

等式運(yùn)算符的情況