數據庫性能概要規范

N

DocumentInformation

LastUpdated6/6/21

Filename:數據庫性能概要規范.doc

AuthorInformation

AuthorDavidDong

GroupMIS

目錄

1目的..........................................................................5

2PerformanceAuditChecklist...............................................................5

3常用的數據庫優化工具.........................................................6

4性能調整......................................................................6

4.1TSQL...........................................................................................6

4.1.1不要訪問多于你需要的數據.......................................6

4.1.2所有者.對象名(objectownername.objectname)...............6

4.1.3Union&UnionAll...............................................................6

4.1.4Union&Jion......................................................................8

4.1.5Distinct..............................................................................9

4.1.6TOPN...............................................................................10

4.1.7InAndExist.....................................................................11

4.1.8InandBetween...............................................................11

4.1.9Like...................................................................................11

4.1.10OR....................................................................................12

4.1.11Orderby/sorting............................................................12

4.1.12Existandcount(*)...........................................................13

4.1.13使用Case...........................................................................14

4.1.14NullVsNotNull.................................................................15

4.1.15Bit.....................................................................................15

4.1.16字符串操作.....................................................15

4.1.17在Where語句中存在冗余條件..................................15

4.1.18在WHERE子句中的列上使用函數..............................16

4.1.19避免在WHERE中使用no-sargable....................................16

4.1.20避免使用游標...................................................17

4.1.21使用存儲過程...................................................24

4.1.22不要包括不做任何事情的代碼....................................25

4.1.23sp_?.................................................................................25

4.1.24CHECKSUM索引..............................................25

4.1.25索引提示(Indexhint)......................................................26

4.2事務和死鎖............................................................26

4.2.1Transaction......................................................................27

4.2.2Complextransaction........................................................28

4.2.3Deadlock...........................................................................28

4.3表操作................................................................30

4.3.1AvoidTablescan..............................................................30

4.3.2SELECTINTO....................................................................30

4.3.3Update..............................................................................30

4.3.4大批量更新，刪除，插入數據....................................31

4.3.5TempTable&DerivedTables..........................................31

4.3.6有效的使用JOIN..................................................................34

4.3.7父子表..........................................................35

4.3.8關于統計信息(Statistics)...................................................35

4.3.9為列選擇合適數據類型...........................................36

5參考資料.....................................................................36

1目的

通過對?些常見的影響數據庫性能的查詢部分進行分析，提出相應的解決方法，以提高公司業務

數據庫業務系統的高可用性及可并發性。

本文基本分為兩部分，第一部分會給出一個簡單的checklst,列出了常見的一些影響性能的問

題，第二部分則強調在設計，開發方面的性能調整問題。

另外，由于個人經驗的關系，本文檔可能會有很多的缺陷，如果各位能夠提供相關不足，將非常

感激，請發送郵件至DavidDonq@

說明:

在不同的數據庫環境中，相同的數據庫性能設置技巧可能會有完全不同的表現結果，所以性能方

面的技巧一般都是與特定的環境相關的，不能盲目的相信所謂的性能調整技巧，而在我們每次進

行相關性能調整的過程中，我們也需要進行多種測試來達到最理想的效果。

2PerformanceAuditChecklist

Transact-SQLChecklistYourCheck

查詢時不返回多余的數據(比如沒有SELECT*)

盡量不使用游標

如果確實需要使用游標，能否有其他的解決方法

注意T-SQL語句中UNION和UNIONALL區別

盡量避免使用SELECTDISTINCT

注意T-SQL語句中Exist。和Count(*)區別

注意T-SQL語句中IN和EXISTS區別

在Where語句中不存在no-sargable("ISNULL",

n

"!>"z"NOTz"NOTIN","NOTLIKE'；和"LIKE

'%500'")

注意在WHERE子句中不存在數據類型的轉換

注意在WHERE語句中不存在對列進行函數操作

注意查詢中盡力避免使用臨時表

注意到臨時表不可由derivedtable替代

注意到查詢使用了合適的表提示

注意到沒有以sp_作為前綴的存儲過程

注意到存儲過程中是否使用了SETNOCOUNTON

注意到所有的數據庫對象是依dbo.objectname的形式來引用

盡力保證事務盡可能短小并盡可能不使用嵌套事務

對JOIN操作進行了優化

3常用的數據庫優化工具

>QueryAnalyzer

>Profiler

>IndexWizard

>System(Performance)Monitor

4性能調整

以下分別從TSQL、事務和死鎖、表操作這三個方面來講性能調整的問題。

4.1TSQL

4.1.1不要訪問多于你需要的數據

這個聽起來是多余的，但是確實是非常必要的，做起來也不大容易。其中包括不要返回給客戶端

不需要的列和行。比如在SELECT語句中不要使用SELECT*,否則會經常返回給客氣端多于

它們所需要的數據，這樣可以有效減輕網絡傳輸壓力，減少不必要的I/O,以及減少內存耗費，

并能夠使查詢優化器最優化我們的查詢執行計劃，從而減少潛在的性能問題。

4.1.2所有者?對象名(objectownername.objectname)

建議在所有對表、視圖和存儲過程引用時，加上它們的前綴。下面討論一下為什么使用前綴可以

改進查詢性能。

例子:有一存儲過程dbo.foo,此存儲過程運行查詢

SELECTcollFROMtablel

當用戶Lucy調用存儲過程時,查詢優化器必須決定是檢索Lucy.tablel還是檢索dbo.tablelo

然后，當用戶Lily調用同一個存儲過程時，查詢優化器必須對查詢計劃進行重新編譯，以決定

用戶是需要Lily.tablel還是需要dbo.tablelo但是如果把上面的SELECT語句修改如下

SELECTcollFROMdbo.tablel

查詢優化器將不會遇到任何模糊性，從而避免重新編譯，達到提升性能的目的。

4.1.3Union&UnionAll

首先講一下UNION的工作原理，當使用UNION語句時，它的功能與在結果集上SELECT

DISTINCT類似，也就是說使用UNION時它會首先合并兩個結果集，然后執行一個類似于

SELECTDISTINCT的操作，以避免重復行的出現。這個過程在兩上結果集沒有任何重復行的

情況下也會進行DISTINCT處理，所以如果我們確認在UNION的兩個結果集確實存在重復行,

并且要消除重復行的出現時，就可以使用UNION.從另一方面來說，如果我們知道在結果集中并

不會存在重復行，或者說出現重復行對我們的應用程序沒有什么影響時，我們應該使用UNION

ALL語句來替代UNION語句，UNIONALL和UNION相比的優點在于它并不會對兩個結果集

進行SELECTDISTINCT操作，這樣可以節省SQLServer的資源使用。

例子:比較UNION和UNIONALL的差別:

--query1,useUnionAll,thesecondresultwillspendtothefirstresultset.

SELECT1,2

SELECT1,2

--query2zuseunion,thiswilldothedistinctoperation

SELECT1,2

SELECT1,2

我們來看一下其結果集，兀以在第一個查詢中只是把第二個查詢的結果集附加到第一個結果集

中，而第二個杳詢就執行了DISTINCT操作，消除了重復行：

口Results_jMessages屋Executionplan

(Nocolumnname)(Nocolumnname)

(Nocolumnname)(Nocolumnname)

1i'1.........................■2

卜面來看性能方面的對比，分析卜.面的執行計劃，我們可以看到第二個操作非常消耗資源，如果

兩個查詢一塊執行時,第二個查詢幾乎使用了所有的數據庫資源（主要消耗在MERGEJOIN中

的消除重復行部分）：

Query1:Querycost(relativetothebatch):0%

select1,2unionallselect1,2

宣■一由

SELECTConcatenationConstantScan

Cost:0%Cost:IS%Cost:43%

ConstantScan

Query2:Querycost(relativetothebatch):100%

select1,2unionselect1,2

口大

MergeJoin;Tl

SELECTConstantScan

(Union)

Cost:0%Cost:0%

Cost:LOO+

_±1

ConstantScan

Cost:0%

結論：在對UNION和UNIONALL進行選擇時，除非必要，推薦使用UNIONALL

4.1.4Union&Jion

在比較UNION和UNIONALL的基礎上，下面來看UNION合并結果集和使用JION查詢得到

結果集之間的差別.

例子:使用UNION來合并多個結果集的情況：

USEPUBS;

GO

SELECTFROMDBO.SALES

WHEREQTY-.10

UNION

SELECTFROMDBO.SALES

WHEREQTY>11

上面的查詢可以被重寫為下面的查詢，這樣一般就會有性能提升:

USEPUBS;

GO

SELECTdistinctFROMDBO.SALES

WHEREQTY<10CxQTY>11

NOTE：如果知道結果集中并不存在重復的數據行，我們也可以使用UNIONALL來提高性能。

但是UnionAll性能一般來說比Union要好，但是比Join要差。

杳看它們的運行結果，可以發現運行結果是相同的,現在來分析一卜它們的執行計劃和性能差別:

SELECT*FROMDBO.SALESWHEREQTY<10UNIONSELECT-FROMDBO.SALESWHEREQTY>11

□

SortClusteredIndexScan

SELECT

(DistinctSort)[pubi].[dbo].[sales].[UPKCL^salts]

Cost:0%

Cost:L3%Cost:43%

CluBteredIndexScan

[pub?].[dbo].[Balei].[OTKCL_sale?]

Cost:43%

Query2:Querycost(relatiretothebatch):30%

SELECTdistinct*FROMDBO.SALESWHEREQTY<10ORQTY>11

□

ClusteredIndexScan

SELECT

[pubs].[^o].[sales].[UPKd_saies]

Cost:0%

Cost:100，

可見使用UNION時執行兩次CLUSTERED索引的掃描，并且要把結果使用DISTINCT合并起

來，而第二個查詢只要進行一次CLUSTERED索引進行掃描，然后直接展現出來，從而大大提

高了性能。

但是如果能夠使用JOIN達到目的的話，我們建議不要使用Union和UnionALL而是直接使

用JOIN來取我們需要的數據。

4.1.5Distinct

有開發人員在寫查詢的時候不管是否需要都習慣性的在查詢中寫上DISTINCT,這是一個很不好

的習慣，特別是當我們的查詢中包含很大數據量的時候更會大大消耗有限的數據庫資源并降低的

應用程序性能。

DISTINCT應該在確認結具集中存在重復行，并且這些重復的確數據并不是我們所需要的數據

時才能夠使用。這是因為DISTINCT在數據庫上執行了很多額外的操作從而消耗了很多數據庫

資源，這樣會減少別的查詢在執行時所能夠使用的資源，增加數據庫出現性能問題的幾率,

例子：

USEPUBS;

GO

SELECTDISTINCT

au_fnamez

au_lname

FROMauthors

這是一個非常簡單的SELECTDISTINCT查詢語句的示例，但是當我們執行一個復雜的語句時

就應該考慮重新編碼以達到性能的要求，如下面的語句，當返回authore表中已經出版過書的

作者時，很多人會像下面這樣寫：

SELECTDISTINCT

au_fnamez

au_lname

FROMauthorsaJOtitleAuthort

ONt.auid=a.auid

但這時只想得到作者名字，我們可以重寫這個語句來提升我們查詢語句的性能:

SELECTau_fname,

au_lname

FROMauthorsa

WHEREEXISTS(

SELECT*

FROMtitleAuthort

WHEREt.au_ida.au_id

上面的查詢能夠提升性能是因為如果某個作者出過很多書，當查到這個作者出版的第一條記錄

時，就會停止對這個作者的處理。

我們要慎重考慮是不是真的需要DISTINCT

4.1.6TOPN

如果我們的應用程序要返回上千行乃至上萬行數據的時候，我們要考慮是不是真正的需要這么多

數據，是不是可以使用TOP操作符來限制返回給客戶端的行數或者返向給客戶端結果集行的百

分比，這樣可以減少資源的使用，提高數據庫性能并且有效節省帶寬

下面說一下關于減少網絡傳輸壓力的內容。如果我們每次多返回給客戶端10行數據，每行多返

回200個字節，每天10000次被執行，這就是一筆不小的網絡傳輸成本

(10*200*10000Byte),另外，如果再加上傳輸圖片等，將耍占用更多的帶寬。

例子:

USEPUBS;

GO

SELECTTOP1FNAME,LNAME,HIRE_DATEFROMDBO.EMPLOYEE

WHERELNAME'A*'

這時如果有100,000行數據符合WHERE條件，也只返回限制的1行結果集。因為SQLServer

在處理時，當結果集的行數達到TOP中指定的數目時，所有的處理都將停止，這樣就可以潛在

的提高SQLServer的負載，增加性能。

此外，TOP操作還可以讓我們指定返回給客戶端結果集行數的百分比，如：

USEPUBS;

GO

SELECTTOP1PERCENTFNAME,LNAMEfHIRE_DATEFROMDBO.EMPLOYEE

WHERELNAMELIKE*A%'

NOTE：如果?個SELECT語句既包含TOP又包含ORDERBY了?句，那么返回的行將會

從排序好的結果集中選擇，并且只返回已排好序結果集的前n行（或者前百分之N行）。

4.1.7InAndExist

當我們能夠在我們的杳詢中選擇使用IN和Exist語句時，推薦使用EXISTS,因為EXISTS一

般更加高效（EXIST只遇到附合條件的很第一個結果時，此記錄就退出處理）。

4.1.8InandBetween

在兗詢語句中選擇使用IN和BETWEEN時，建議使用BETWEEN,因為它在很多場合下更高效。

例子:有一個customer表,在customer_number列上有一個非聚集索引，進行如下查詢：

SELECTcustomer_number,customer_name

FROMcustomer

WHEREcustomer_numberin(1000,1001,1002,1003,1004)

查詢優化器可以使我們下面使用BETWEEN的語句比使用IN的語句性能更高效:

SELECTcustomer_numberzcustomer_name

FROMcustomer

WHEREcustomer_numberBETWEEN1000and1004

4.1.9Like

當在WHERE中使用Like時，盡可能的在Like語句中使用一個或者多個前導字符，比如:

使用

而不是:

%m

如果在Like中使用一個前導字符，此時查詢優化器就會自動使用相應索引（如果有合適索引存

在的話）來處理這個杳詢，但是如果我們Like語句中的前導字符使用通配符，杳聞優化器不會

使用索引，而會對表進行掃描，從而大大降低性能。這點在SQLServer2005卜.有所提升，

在SQLServer2005中，置詢優化器可以為類似于LIKE，％m'語句使用索引。

NOTE:使用的前導字符越多，查詢優化器就越有可能找到合適的索引，從而加快查詢。

另外，如果應用程序的查詢中有很多對CHAR和VARCHAR列進行Like操作時，我們可能要

考慮使用SQLServer的全文索引，全文索引對于處理這方面查詢會大大提升性能。

4.1.10OR

如果一個查詢語句中包括多個OR子句，為提升性能，一般情況下可以重寫為一系列查詢并使用

UNIONALL來合并結果集。

例子:

SELECTemployeelD,firstname,lastname

FROMnames

WHEREdept=*prod*orcity=*Orlando'ordivision=*food,

這個語句在WHERE中有三個獨立的條件，為了使用到索引，我們要在這三列上創建組合索引。

像這樣的語句可能被重寫為多個查詢并使用UNIONALL來合并結果集，從而替代OR操作。

如下：

SELECTemployeelD,firstname,lastnameFROMnamesWHEREdept'prod'

SELECTemployeelD,firstname,lastnameFROMnamesWHEREcity'Orlando,

SELECTemployeelD,firstname,lastnameFROMnamesWHEREdivision1food,

每一個查詢都得出一樣的結果集（元數據相同），如果在這個表中只有dept列上存在索引，此

時第一個SELECT就會使用此索引，而第二個第三個SELECT語句會進行表的掃描，而不是全

部進行表的掃描。

這只是一個非常簡單的例子，只是示范了如何重寫查詢來提高性能，如果這個查詢比較復雜，那

么使用UNIONALL就會大大提高性能，因為這樣我們可以對每個語句進行調整，而如果是全

在一個OR子句中，這些我們是做不到的。

NOTE：注意在這里我們是使用UNIONALL而不是UNION,原因是UNION不光會對結果集進

行合并，也會對結果集進行排序并移除所有的重復行，所以性能會大大降低。

4.1.11Orderby/sorting

ORDERBY要占用很多額外的資源，所以除非在真正需要的情況下，否則不要在SELECT語句

中使用ORDERBY.我們可以考慮，這些數據在客戶端進行排序是否會更好，或者客戶端是否

需要我們對結果集進行排序。

SORT一般是伴隨著以下T-SQL語句出現：

>ORDERBY

>GROUPBY

>SELECTDISTINCT

>UNION

>CREATEINDEX(此時也需要對數據進行排序)

一般情況下，這些命令不可能避免，但是另一方面，我們可能想辦法減少這些語句對資源的使用，

這包括：

>盡可能減少被排序的行數,就是只對必要排序的數據行集進行排序。

>盡可能減少結果臭中列的數目。

>盡可能減少所有行的物理寬度.

>盡可能對INT的列進行排序，而不是Character或者Char列。

當使用SORT操作時，一定要考慮上面的建議，并保持下面的原則“除非必要，否則不要來對

結果集進行排序”。

4.1.12Existandcount(*)

對數據庫進行更新時，很多時候要首先判斷被更新的記錄是否存在，或者對刪除對象之前，判斷

對象是否存在，此時不要使用SELECTCOUNT(*)來標識，因為它的性能是非常低的。為了提

升性能，我們可以使用IFEXISTS來實現相關的操作。IFEXISTS能提高性能是因為當有一條

記錄為真是，處理就會立即退出，但是COUNT(*)會檢查表中的每一條記錄的情況，不管符合

條件的數據有一條還是上萬條。

例子:

USEPUBS

--COUNT(*):

IF(SELECTCOUNT(*)FROMDBO.AUTHORS)>0

BEGIN

PRINT?ASDF,

END

--下面是使用EX工ST的語句，也是性能較好的語句：

IF■'XI(SELECTFROMDBO.AUTHORS

BEGIN

PRINT'ASDF'

END

看一下執行計劃，可見使用EXIST確實能夠提高性能。

Query1:Querycost(relativetothebatch):85%

IF(SELECTCOUNT。FROMDSO.AUTHORS)>0

科一■5回

C3C&ScaXArCouutn3c?

Coae:0ICoat:0tiIDMZJoxalCom:0I

COM:0t

3鏘團

:nS*SCAB

(Affxefawl①g,]」一】jMUmrg]

COM:0t

CF:01——100t

Query2:Querycost(relativetothebatch):15%

IFEXISTS(SELECT?FROMDBO.AUTHORS)

/q回

A___n

Loope

CONDConpureScalarConstantScan

Cost:0%Co?r:0?(LeftSeniJoin)Coir:0%

COST:0%

血

ZndaxScan

[pubs〕.IdboJ.(authors)(auniund]

Coit:100、

雖然這兩個杳詢語句都使用了一樣的索引，但是在COUN-(*)時返回的行的數據是表中符合條

件的所有行.然后才對這些行進行Aggregate,這也就是為什么數據越多,使用COUNT(*)

性能越低的原因。

4.1.13使用Case

當對一個表進行多次更新時，盡量合并到一個更新語句中。

例子:如果要對PUBS數據庫中的相關書根據不同的條件來調整它的銷售價格，需要調高商務

類書的銷售價格至原來的130%,調高非商務類書的銷售價格至原來的110%,我們很多人在

UPDATE的時候是以一個事務中包括兩個獨立的UPDATE語句來進行的，如下：

BEGINTRAN

UPDATEtitlesSETpriceprice1.30WHEREtype'business'

UPDATEtitlesSETpriceprice1.10WHEREtype<>,business'

COMMITtran

上面語句的缺點是它會對表進行兩次的讀寫，下面看一下如何把它們寫到一個語句中來:

UPDATEtitles

SETprice

CASE

WHENtype-1business

THENprice1.30

ELSEprice*1.10

END

比較一下下面的執行計劃，發現第一個查詢語句的COST為66.7%,第二個查詢語句的COST

為33.3%.這樣就可以大大提高性能。

Query1:Querycost(relativetothebatch):33%

UPDATE[titles]SET[prxue]■[pirxce]?&】WHERE[type]?@2

CluydXndftxUpdateCIuiMz*dIndexScan

CcRpvScalasTop

[puba].[dbo].(tlclM).[U?KCLtitle[pubB].[dbo].CtlclM].nXPJCL

Cott:0%Cott:0%

Coe：22%-Co*t：78%

Query2:Querycost(relativetothebatch):33%

UPDATE[titles]SET[price]?[price]*81WHERE[type]<>82

n

UPDATEClusteredIndexUpdikeCospusScalarTopClusteredIndexScan

(pubs).(dbo].(titlts].EPKCL_xtl?」(pjbs).(dbol.(titles).

Coit:0?COST:0%Coir:0%

Coit:22%CoIX：78%

Query3:Querycost(relativetothebatch):33%

UPDATEtitlesSt工priu。.CAS￡WHENtype.'business'HHENpriup1.32ELSEpr；u。,l?10END

回n。

ClustorMIndaxUpda^o-*ClusteredIndexSean

VPDA7EP

[pubs].[d^o].(cities].[UPKCLJ.0.Cpub?)?[So：.(titlM].lUPKCL^ltlei-

Coet:0%

Coet：22QCo"："，

4.1.14NullVsNotNull

在創建表時應該為每列創建默認值而不是允許為NULL,因為對可空列進行操作和對非可空列進

行操作相比將需要更多額外的資源。如比較(comparisons)就是一個很好的例子。

所以我們應該盡可能的使用NOTNULL來替代NULL。

4.1.15Bit

不推薦使用Bit歹人

如果已經使用Bit歹U，請確保不要允許為NULL,如果此列有兩個以上的狀態，請考慮其它類型。

4.1.16字符串操作

盡量避免在Transact-SQL中進行字符的串聯操作，因為這是個很慢的操作，會造成應用程序

的減慢(TSQL是為集合操作而優化的)。

4.1.17在Where語句中存在冗余條件

在WHERE語句中小心地使用OR,這經常會得到一些多余的數據，同時也很容易在WHERE

語句中出現冗余的子句。

例子:

SELECTcompanyid,plantid,formulaid

FROMbatchrecords

WHERE(companyid10001'plantid,02021formulaid,39988773')

(companyid'0001'ndplantid=*0202,)

這兒的WHERE語句是冗余的:

companyid=100011andplantid-'0202*a：：dformulaid=139988773,

是下面子句的子集：

companyid'00011mdplantid*0202,

換句話來說，這個查詢是冗余的，但SQLServer2000的查詢優化器并不會自動探測是否存

在冗余（這個在SQLServer2005中有所提高），它完全按你所寫的條件去執行，此時SQL

Server就會首先得到所有的數據，然后使用SELECTDISTINCT來移除冗余的數據。如果刪

除以下的查詢子句：

companyid'0001*anciplantid*0202'

此時這個查詢語句就會加快很多。

4.1.18在WHERE子句中的列上使用函數

默認情況下，在進行比較時，經常寫類似下面的語句：

SELECTcolumn_nameFROMtable_name

WHERELOWERicolumn_name*name*

也就是說，大家認為SQLServer是大小寫敏感的，但一般情況下我們SQLServer的配置為

大小寫不敏感，所以不使用類似于上面的語句，就可以大大加快查詢的速度。

但如果數據庫確實是配置為大小寫敏感時，上面的代碼依然性能很低。因為原則是：不要在

WHERE中對列進行任何函數操作，在列上進行函數操作會導致此查詢并不能夠使用索引，如下:

SELECTmember_number,first_name,last_name

FROMmembers

WHEREDATEDIFF(yyzdatofbirth,GETDA7E())>21

如果需要進行函數操作的話可以寫類似于下面的代碼，下面的代碼可以使用dateofbirth列上的

索引;

SELECTmember_number,first_name,last_name

FROMmembers

WHEREdateofbirthDATEADD(yy,-21,GETDATE())

4.1.19避免在WHERE中使用no-sargable

像“ISNULL","K'*,"NOT","NOTEXISTS","NOTIN","NOT

LIKE"，和"LIKE'%50G”的操作一般來說將導致查詢優化器不能夠使用索引來進行數據的查

找。一般情況下，我們可以重寫這樣的語句來達到性能的提升。

例子:

下面語句：

WHERESUBSTRINGifirstname,1,1?m,

可能被重寫為下面的查詢:

WHEREfirstname*m%

NOT其實并不是no-sargable,但是一般情況下盡可能在查詢中查詢中不使用NOT操作符,

如：

WHERE\0Tcolumn_nane>5

可以被重寫為:

WHEREcolumnname<-5

4.1.20避免使用游標

游標可以嚴重影響SQLServe「性能，但是一些場合下不可避免，不過更多場合下我們是可以

不使用游標的。所以如果現在你的應用程序使用T-SQL游標，請重新檢杳你的代碼并盡可能的

重寫它們。

Transact-SQL是設計用來處理集合操作的，而不是對獨立的數據記錄行進行操作。不過SQL

提供了一種對數據行進行操作的選項，在Transact-SQL中游標就可以被用來處理獨立的行，

但是問題就是它的操作是如此慢，理想情況下，在高性能的基于SQLServer的應用程序中，

游標應該被避免。

所以如果你要進行一個基于行的操作，設法找到其它的方法來完成這項工作是一個非常好的開發

習慣。考慮使用以下選項來替代游標：

>使用臨時表

>使用WHILE循環

>使用derivedtables

>使用子查詢

>使用CASE語句

>使用多個查詢

上面的這些選項有時候可以替代游標，從而大大提升性能。但并不是所有的情況下都能夠使用以

上的選項來替代游標，如果發現不可能避免使用游標，請參考下面的建議或許會有幫助：

?SQLServer提供了兒種不同種類的游標，它們之間性能也存在著差異。我們要選擇對

服務器性能影響最小的能夠達到目標的游標。最高效的游標就是fastforward-only游

標。這個在我們的命名規范中也有提及

?當使用游標時，取盡可能少的數據

?當用過游標后，不僅CLOSE它，同時也要DEALLOCATE此游標，也就是說游標占用

的存儲單元也需要擇放，如果只是簡單的關閉而不擇放，雖然此游標鎖死的資源被釋放，

但SQLServer的資源并沒有釋放，游標使用的資源依然存在直到你釋放它為止。

SQLServer游標非?常消耗數據庫資源，這是因為它極低的性能所致。但是根據“存在就是必然”

的原則(Montaque語錄)，它們也有存在的道理，因為在有些地方是確實非常有用的，它們非

常靈活?，可以使我們對每行的數據進行非常靈活的操作，但一般情況下我們使用的游標可能使用

其它方法來代替，比如DerivedTable,集合查詢和臨時麥，下面會討論一下這個問題。

下面討論游標的可選替代方案。

這里通過一些示例來演示一下如何不使用游標來解決一些以前用游標來解決的問題。

例子:

首先，先看一下簡單的游標，此游標在循環一個表，然后我們看一下如何不使用游標來達到相同

的目標。

使用游標：

ifexists(selectAfromsysobjectswherenameN*prcCursorExample')

dropprocedureprcCursorExample

go

CREATEPROCEDUREprcCursorExample

AS

/*

**putyourcommenthere

★*

*/

SETNOCOUNTON

--declareallvariables!

DECLAREQiRowIdint,

@vchCustomerNamenvarchar255),

@vchCustomerNmbrnvarchar10

-declarethecursor

DECLARECustomerCURSORFOR

SELECTiRowId,

vchCustomerNmbr.

vchCustomerName

FROMCustomerTable

OPENCustomer

FETCHCustomerINTO@iRowId,

@vchCustomerNmbr,

QvchCustomerName

--startthAmainp-ssHng1ccp.

WHILE@@Fetch_Status=0

BEGIN

——Thisiswhereyouperformyourdetailedrow-by-row

-processing.

——Getthenextrow.

FETCHCustomerINTO@iRowId,

^vchCustomerNmbrz

@vchCustomerName

END

CLOSECustomer

DEALLOCATECustomer

RETURN

不使用游標:

ifexists(selectfromsysobjectswherename=N*prcLoopExample')

dropprocedureprcLoopExample

g。

CREATEPROCEDUREprcLoopExample

AS

/*

**putyourcommenthere

*/

SETNOCOUNTON

--declareallvariables!

DECLAREQiReLuxiiCudeinL,

SiNextRowZdint,

QiCurrentRowIdint,

@iLoopControlint,

QvchCustonerNamenvarchar(255),

@vchCustonerNmbrnvarchar(10)

QchProductNumbernchar(30)

--Initializevariables!

SELECT@iLoopControl1

SELECT@iNextRowIdMIN(iRowId

FROMCustomerTable

Makesurethetablehasdata.

IFSN：」L.@iNextRow：d,00

BEGIN

SELECT'Nodatainfoundintable!1

RETURN

END

Retrievethefirstrow

SELECT@iCurrentRowIdiRowId,

@vchCustomerNmbrvchCustonerNmbr

@vchCustomerNamevchCustonerName

FROMCustomerTable

WHEREiRowZd@iNextRowId

startthemainprocessingloop.

WHILEGiLoopControl-1

BEGIN

Thisiswhereyouperformyourdetailedrow-by-row

processing.

Resetloopingvariables.

SELECTGiNextRowId

getthenextiRowId

SELECTGiNextRowIdMIN(iRowId)

FROMCustomerTable

WHEREiRov/idUiCurrentRowid

didwegetavalidnextrowid?

IFTSNUI/@iNextRowId,00

BEGIN

BREAK

END

getthenextrow.

SELECT0iCurrentRowIdiRowId.

@vchCustomerNmbrvchCustomerNmbr

@vchCustomerNamevchCustomerName

FROMCustomerTable

WHEREiRowIdQiNextRowId

END

RETURN

我們現在來看一下上面的游標，一般來說，為了性能的因素.我們的表上都有一個類似于RowID

的列，此列可以被用來做循環，并得到相關的數據。一般來說此列是IDENTITY歹U,主鍵并有

clustered索引。

但是很多情況下，我們的表中并不包括可以被用來循環的行ID,比如，可能在一個具有

uniqueindentifier屬性的列上創建了主鍵索引，這時候可以為這個表增加一個自增列并創建相

應的索引，來實現此功能。

卜.而例子使用了MIN函數和〃>"來得到下一行我們需要的數據，當然我們也可以使用MAX函

數然后使用〃達到相同的功能。

例子:

SELECT@iNextRowIdMAX(iRowId

FROMCustomerTable

WHEREiRowId<@iCurrentRowId

有一個比較重要的地方需要注意：要在取得下一次要循環的行ID之前設置此ID為NULL,是

因為當此循環實現所有的行循環后SELECT語句并不會為此ID設置為NULL,從而造成一個死

循環。當循環變量為NULL后，意味著這個循環已經完成了它需要實現的功能，此時我們可能

使用BREAK來退出WHILE循環，當然也有其它的途徑來推出循環。

你可以在上面的存儲過程中的如下注釋處加上自己的基于行的操作。

-Thisiswhereyouperformyourdetailedrow-by-rovz

--processing.

可以看出，基于行的操作對性能非常有影響。舉例來說，如果你有一個非常復雜的任務需要進行

嵌套的循環，此時你會使用嵌套的游標，內層的游標根據外層游標的條件進行相應的操作，這時

候如果我們使用游標來進行處理的話對服務器會有非常大的壓力。

例子:

ifexist(select-fromsysobjectswhe