SQL Server資料庫查詢最佳化50妙計（下篇）

      26、MIN()和MAX()能使用到合適的索引。
　　27、資料庫有一個原則是代碼離資料越近越好，所以優先選擇Default,依次為Rules,Triggers,

Constraint（約束如外健主健CheckUNIQUE……,資料類型的最大長度等等都是約束）,Procedure.這樣不僅

維護工作小，編寫程式品質高，並且執行的速度快。

　　28、如果要插入大的二進位值到Image列，使用預存程序，千萬不要用內嵌insert來插入(不知JAVA是

否)。因為這樣應用程式首先將二進位值轉換成字串（尺寸是它的兩倍），伺服器受到字元後又將他轉換

成二進位值.預存程序就沒有這些動作: 方法：

　　create procedure p_insert as insert into table(Fimage) values (@image)

　　在前台調用這個預存程序傳入二進位參數，這樣處理速度明顯改善。

　　29、Between在某些時候比IN速度更快,Between能夠更快地根據索引找到範圍。用查詢最佳化工具可見到

差別。

　　select * from chineseresume where title in (’男’,’女’) select * from chineseresume where

between

　　’男’ and ’女’ 是一樣的。由於in會在比較多次，所以有時會慢些。

　　30、在必要是對全域或者局部暫存資料表建立索引，有時能夠提高速度，但不是一定會這樣，因為索引也

耗費大量的資源。他的建立同是實際表一樣。

　　31、不要建沒有作用的事物例如產生報表時，浪費資源。只有在必要使用事物時使用它。

　　32、用OR的字句可以分解成多個查詢，並且通過union 串連多個查詢。他們的速度只同是否使用索引

有關,如果查詢需要用到聯合索引，用union all執行的效率更高.多個OR的字句沒有用到索引，改寫成union

的形式再試圖與索引匹配。一個關鍵的問題是否用到索引。

　　33、盡量少用視圖，它的效率低。對視圖操作比直接對錶操作慢,可以用stored procedure來代替她。

特別的是不要用視圖嵌套,嵌套視圖增加了尋找原始資料的難度。我們看視圖的本質：它是存放在伺服器上

的被最佳化好了的已經產生了查詢規劃的SQL。對單個表檢索資料時，不要使用指向多個表的視圖，直接從表

檢索或者僅僅包含這個表的視圖上讀，否則增加了不必要的開銷,查詢受到幹擾.為了加快視圖的查詢，

MsSQL增加了視圖索引的功能。

　　34、沒有必要時不要用DISTINCT和ORDER BY，這些動作可以改在用戶端執行。它們增加了額外的開

銷。這同union和union ALL一樣的道理。

　　select top 20 ad.companyname,comid,position,ad.referenceid,worklocation,
　　convert(varchar(10),ad.postDate,120) as postDate1,workyear,degreedescription FROM
　　jobcn_query.dbo.COMPANYAD_query ad where referenceID in(’JCNAD00329667’,’JCNAD132168’

,’JCNAD00337748’,’JCNAD00338345’,
　　’JCNAD00333138’,’JCNAD00303570’,’JCNAD00303569’,
　　’JCNAD00303568’,’JCNAD00306698’,’JCNAD00231935’,’JCNAD00231933’,
　　’JCNAD00254567’,’JCNAD00254585’,’JCNAD00254608’,
　　’JCNAD00254607’,’JCNAD00258524’,’JCNAD00332133’,’JCNAD00268618’,
　　’JCNAD00279196’,’JCNAD00268613’) order by postdate desc

　　35、在IN後面值的列表中，將出現最頻繁的值放在最前面，出現得最少的放在最後面，減少判斷的次

數。

　　36、當用select INTO時，它會鎖住系統資料表(sysobjects，sysindexes等等)，阻塞其他的串連的存取。創

建暫存資料表時用顯示申明語句，而不是

　　select INTO. drop table t_lxh begin tran select * into t_lxh from chineseresume
　　where name = ’XYZ’ --commit

　　在另一個串連中select * from sysobjects可以看到select INTO會鎖住系統資料表，create table也會鎖系統

表(不管是暫存資料表還是系統資料表)。所以千萬不要在事物內使用它！這樣的話如果是經常要用的暫存資料表請使用實

表，或者暫存資料表變數。

　　37、一般在GROUP BY個HAVING字句之前就能剔除多餘的行，所以盡量不要用它們來做剔除行的工作

。他們的執行順序應該如下最優: select的where字句選擇所有合適的行，Group By用來分組個統計行，

Having字句用來剔除多餘的分組。這樣Group By個Having的開銷小，查詢快.對於大的資料行進行分組和

Having十分消耗資源。如果Group BY的目的不包括計算，只是分組，那麼用Distinct更快

　　38、一次更新多條記錄比分多次更新每次一條快,就是說批處理好：三

　　39、少用暫存資料表，盡量用結果集和Table類性的變數來代替它,Table 類型的變數比暫存資料表好

　　40、在SQL2000下，計算欄位是可以索引的，需要滿足的條件如下：

　　a、計算欄位的表達是確定的

　　b、不能用在TEXT,Ntext，Image資料類型

　　c、必須配製如下選項 ANSI_NULLS = ON, ANSI_PADDINGS = ON, …….

　　41、盡量將資料的處理工作放在伺服器上，減少網路的開銷，如使用預存程序。預存程序是編譯好、

最佳化過、並且被組織到一個執行規劃裡、且儲存在資料庫中的SQL語句，是流程控制語言的集合，速度當然快

。反覆執行的動態SQL,可以使用暫存預存程序，該過程（暫存資料表）被放在Tempdb中。以前由於SQL

SERVER對複雜的數學計算不支援，所以不得不將這個工作放在其他的層上而增加網路的開銷。SQL2000支

持UDFs,現在支援複雜的數學計算，函數的傳回值不要太大，這樣的開銷很大。使用者自訂函數象游標一樣

執行的消耗大量的資源，如果返回大的結果採用預存程序

　　42、不要在一句話裡再三的使用相同的函數，浪費資源,將結果放在變數裡再調用更快

　　43、select count(*)的效率教低，盡量變通他的寫法，而exists快.同時請注意區別：select count

(Field of null) from Table 和 select count(Field of NOT null) from Table 的傳回值是不同的！！！

　　44、當伺服器的記憶體夠多時，配製線程數量 = 最大串連數+5，這樣能發揮最大的效率；否則使用 配

制線程數量<最大串連數啟用SQL SERVER的線程池來解決,如果還是數量 = 最大串連數+5，嚴重的損害服

務器的效能。

　　45、按照一定的次序來訪問你的表。如果你先鎖住表A，再鎖住表B，那麼在所有的預存程序中都要按

照這個順序來鎖定它們。如果你（不經意的）某個預存程序中先鎖定表B，再鎖定表A，這可能就會導致一

個死結。如果鎖定順序沒有被預先詳細的設計好，死結很難被發現

　　46、通過SQL Server Performance Monitor監視相應硬體的負載Memory: Page Faults / sec計數器如

果該值偶爾走高，表明當時有線程競爭記憶體。如果持續很高，則記憶體可能是瓶頸。

　　Process:

　　1、%DPC Time 指在範例間隔期間處理器用在緩延程式調用(DPC)接收和提供服務的百分比。(DPC 正

在啟動並執行為比標準間隔優先權低的間隔)。 由於DPC是以特權模式執行的，DPC時間的百分比為特權時間 百

分比的一部分。這些時間單獨計算並且不屬於間隔計算總數的一部 分。這個總數顯示了作為執行個體時間百分

比的平均忙時。

　　2、%Processor Time計數器　如果該參數值持續超過95%，表明瓶頸是CPU。可以考慮增加一個處理

器或換一個更快的處理器。

　　3、%Privileged Time指非閑置處理器時間用於特權模式的百分比。(特權模式是為作業系統組件和操縱

硬體驅動程式而設計的一種處理模式。它允許直接存取硬體和所有記憶體。另一種模式為使用者模式，它是一

種為應用程式、環境分系統和整數分系統設計的一種有限處理模式。作業系統將應用程式線程轉換成特權

模式以訪問作業系統服務)。 特權時間的%包括為間斷和DPC提供服務的時間。特權時間比率高可能是由於

失敗裝置產生的大數量的間隔而引起的。這個計數器將平均忙時作為樣本時間的一部分顯示。

　　4、% User Time表示耗費CPU的資料庫操作，如排序，執行aggregate functions等。如果該值很高，

可考慮增加索引，盡量使用簡單的表聯結，水平分割大表格等方法來降低該值。Physical Disk: Curretn Disk

Queue Length計數器該值應不超過磁碟數的1.5~2倍。要提高效能，可增加磁碟。 SQLServer:Cache Hit

Ratio計數器該值越高越好。如果持續低於80%，應考慮增加記憶體。 注意該參數值是從SQL Server啟動後，

就一直累加記數，所以運行經過一段時間後，該值將不能反映系統當前值。

　　47、分析select emp_name form employee where salary > 3000在此語句中若salary是Float類型的，

則最佳化器對其進行最佳化為Convert(float,3000)，因為3000是個整數，我們應在編程時使用3000.0而不要等

運行時讓DBMS進行轉化。同樣字元和整型資料的轉換。

　　48、查詢的關聯同寫的順序

　　select a.personMemberID, * from chineseresume a,personmember b where personMemberID
　　= b.referenceid and a.personMemberID = ’JCNPRH39681’ （A = B ,B = ’號碼’）
　　select a.personMemberID, * from chineseresume a,personmember b where a.personMemberID
　　= b.referenceid and a.personMemberID = ’JCNPRH39681’ and b.referenceid = ’JCNPRH39681’ （

A = B ,B = ’號碼’， A = ’號碼’）
　　select a.personMemberID, * from chineseresume a,personmember b where b.referenceid
　　= ’JCNPRH39681’ and a.personMemberID = ’JCNPRH39681’ （B = ’號碼’， A = ’號碼’）

　　49、

　　(1)IF 沒有輸入負責人代碼THEN code1=0 code2=9999 ELSE code1=code2=負責人代碼END IF執

行SQL語句為: select負責人名FROM P2000 where負責人代碼>=:code1 AND負責人代碼 <=:code2

　　(2)IF 沒有輸入負責人代碼THEN 　select負責人名FROM P2000 ELSE code= 負責人代碼 select 負責

人代碼FROM P2000 where負責人代碼=:code END IF第一種方法只用了一條SQL語句,第二種方法用了兩

條SQL語句。在沒有輸入負責人代碼時,第二種方法顯然比第一種方法執行效率高,因為它沒有限制條件;在輸

入了負責人代碼時,第二種方法仍然比第一種方法效率高,不僅是少了一個限制條件,還因相等運算是最快的

查詢運算。我們寫程式不要怕麻煩

　　50、關於JOBCN現在查詢分頁的新方法（如下），用效能最佳化器分析效能的瓶頸，如果在I/O或者網路

的速度上，如下的方法最佳化切實有效，如果在CPU或者記憶體上，用現在的方法更好。請區分如下的方法，說

明索引越小越好。

　　begin
　　DECLARE @local_variable table (FID int identity(1,1),ReferenceID varchar(20))
　　insert into @local_variable (ReferenceID)
　　select top 100000 ReferenceID from chineseresume order by ReferenceID
　　select * from @local_variable where Fid > 40 and fid <= 60
　　end

　　和

　　begin
　　DECLARE @local_variable table (FID int identity(1,1),ReferenceID varchar(20))
　　insert into @local_variable (ReferenceID)
　　select top 100000 ReferenceID from chineseresume order by updatedate
　　select * from @local_variable where Fid > 40 and fid <= 60
　　end

　　的不同

　　begin
　　create table #temp (FID int identity(1,1),ReferenceID varchar(20))
　　insert into #temp (ReferenceID)
　　select top 100000 ReferenceID from chineseresume order by updatedate
　　select * from #temp where Fid > 40 and fid <= 60 drop table #temp
　　end