查詢速度慢的原因很多,常見如下幾種:
1,沒有索引或者沒有用到索引(這是查詢慢最常見的問題,是程序設計的缺陷)
2,I/O吞吐量小,形成了瓶頸效應.
3,沒有創建計算列導致查詢不優化.
4,內存不足
5,網絡速度慢
6,查詢出的數據量過大(可以采用多次查詢,其他的方法降低數據量)
7,鎖或者死鎖(這也是查詢慢最常見的問題,是程序設計的缺陷)
8,sp_lock,sp_who,活動的用戶查看,原因是讀寫競爭資源.
9,返回了不必要的行和列
10,查詢語句不好,沒有優化
可以通過如下方法來優化查詢 :
1,把數據,日誌,索引放到不同的I/O設備上,增加讀取速度,以前可以將Tempdb應放在RAID0上,SQL2000不在支持.數據量(尺寸)越大,提高I/O越重要.
2,縱向,橫向分割表,減少表的尺寸(sp_spaceuse)
3,升級硬件
4,根據查詢條件,建立索引,優化索引,優化訪問方式,限制結果集的數據量.註意填充因子要適當(最好是使用默認值0).索引應該盡量小,使用字節數小的列建索引好(參照索引的創建),不要對有限的幾個值的字段建單壹索引如性別字段
5,提高網速;
6,擴大服務器的內存,Windows 2000和SQL server 2000能支持4-8G的內存.配置虛擬內存:虛擬內存大小應基於計算機上並發運行的服務進行配置.運行 Microsoft SQL Server? 2000 時,可考慮將虛擬內存大小設置為計算機中安裝的物理內存的 1.5 倍.如果另外安裝了全文檢索功能,並打算運行 Microsoft 搜索服務以便執行全文索引和查詢,可考慮:將虛擬內存大小配置為至少是計算機中安裝的物理內存的 3 倍.將 SQL Server max server memory 服務器配置選項配置為物理內存的 1.5 倍(虛擬內存大小設置的壹半).
7,增加服務器 CPU個數;但是必須明白並行處理串行處理更需要資源例如內存.使用並行還是串行程是MsSQL自動評估選擇的.單個任務分解成多個任務,就可以在處理器上運行.例如耽擱查詢的排序,連接,掃描和GROUP BY字句同時執行,SQL SERVER根據系統的負載情況決定最優的並行等級,復雜的需要消耗大量的CPU的查詢最適合並行處理.但是更新操作Update,Insert, Delete還不能並行處理.
8,如果是使用like進行查詢的話,簡單的使用index是不行的,但是全文索引,耗空間. like 'a%' 使用索引 like '%a' 不使用索引用 like '%a%' 查詢時,查詢耗時和字段值總長度成正比,所以不能用CHAR類型,而是VARCHAR.對於字段的值很長的建全文索引.
9,DB Server 和APPLication Server 分離;OLTP和OLAP分離
10,分布式分區視圖可用於實現數據庫服務器聯合體.聯合體是壹組分開管理的服務器,但它們相互協作分擔系統的處理負荷.這種通過分區數據形成數據庫服務器聯合體的機制能夠擴大壹組服務器,以支持大型的多層 Web 站點的處理需要.有關更多信息,參見設計聯合數據庫服務器.(參照SQL幫助文件'分區視圖')
a,在實現分區視圖之前,必須先水平分區表
b,在創建成員表後,在每個成員服務器上定義壹個分布式分區視圖,並且每個視圖具有相同的名稱.這樣,引用分布式分區視圖名的查詢可以在任何壹個成員服務器上運行.系統操作如同每個成員服務器上都有壹個原始表的復本壹樣,但其實每個服務器上只有壹個成員表和壹個分布式分區視圖.數據的位置對應用程序是透明的.
11,重建索引 DBCC REINDEX ,DBCC INDEXDEFRAG,收縮數據和日誌 DBCC SHRINKDB,DBCC SHRINKFILE. 設置自動收縮日誌.對於大的數據庫不要設置數據庫自動增長,它會降低服務器的性能.在T-sql的寫法上有很大的講究,下面列出常見的要點:首先, DBMS處理查詢計劃的過程是這樣的:
1, 查詢語句的詞法,語法檢查
2, 將語句提交給DBMS的查詢優化器
3, 優化器做代數優化和存取路徑的優化
4, 由預編譯模塊生成查詢規劃
5, 然後在合適的時間提交給系統處理執行
6, 最後將執行結果返回給用戶其次,看壹下SQL SERVER的數據存放的結構:壹個頁面的大小為8K(8060)字節,8個頁面為壹個盤區,按照B樹存放.
12,Commit和rollback的區別 Rollback:回滾所有的事物. Commit:提交當前的事物. 沒有必要在動態SQL裏寫事物,如果要寫請寫在外面如: begin tran exec(@s) commit trans 或者將動態SQL 寫成函數或者存儲過程.
13,在查詢Select語句中用Where字句限制返回的行數,避免表掃描,如果返回不必要的數據,浪費了服務器的I/O資源,加重了網絡的負擔降低性能.如果表很大,在表掃描的期間將表鎖住,禁止其他的聯接訪問表,後果嚴重.
14,SQL的註釋申明對執行沒有任何影響15,盡可能不使用光標,它占用大量的資源.如果需要row-by-row地執行,盡量采用非光標技術,如:在客戶端循環,用臨時表,Table變量,用子查詢,用Case語句等等.遊標可以按照它所支持的提取選項進行分類: 只進 必須按照從第壹行到最後壹行的順序提取行.FETCH NEXT 是唯壹允許的提取操作,也是默認方式.可滾動性可以在遊標中任何地方隨機提取任意行.遊標的技術在SQL2000下變得功能很強大,他的目的是支持循環.有四個並發選項 READ_ONLY:不允許通過遊標定位更新(Update),且在組成結果集的行中沒有鎖. OPTIMISTIC WITH valueS:樂觀並發控制是事務控制理論的壹個標準部分.樂觀並發控制用於這樣的情形,即在打開遊標及更新行的間隔中,只有很小的機會讓第二個用戶更新某壹行.當某個遊標以此選項打開時,沒有鎖控制其中的行,這將有助於最大化其處理能力.如果用戶試圖修改某壹行,則此行的當前值會與最後壹次提取此行時獲取的值進行比較.如果任何值發生改變,則服務器就會知道其他人已更新了此行,並會返回壹個錯誤.如果值是壹樣的,服務器就執行修改.選擇這個並發選項?OPTIMISTIC WITH ROW VERSIONING:此樂觀並發控制選項基於行版本控制.使用行版本控制,其中的表必須具有某種版本標識符,服務器可用它來確定該行在讀入遊標後是否有所更改.在 SQL Server 中,這個性能由 timestamp 數據類型提供,它是壹個二進制數字,表示數據庫中更改的相對順序.每個數據庫都有壹個全局當前時間戳值:@@DBTS.每次以任何方式更改帶有 timestamp 列的行時,SQL Server 先在時間戳列中存儲當前的 @@DBTS 值,然後增加 @@DBTS 的值.如果某 個表具有 timestamp 列,則時間戳會被記到行級.服務器就可以比較某行的當前時間戳值和上次提取時所存儲的時間戳值,從而確定該行是否已更新.服務器不必比較所有列的值,只需比較 timestamp 列即可.如果應用程序對沒有 timestamp 列的表要求基於行版本控制的樂觀並發,則遊標默認為基於數值的樂觀並發控制. SCROLL LOCKS 這個選項實現悲觀並發控制.在悲觀並發控制中,在把數據庫的行讀入遊標結果集時,應用程序將試圖鎖定數據庫行.在使用服務器遊標時,將行讀入遊標時會在其上放置壹個更新鎖.如果在事務內打開遊標,則該事務更新鎖將壹直保持到事務被提交或回滾;當提取下壹行時,將除去遊標鎖.如果在事務外打開遊標,則提取下壹行時,鎖就被丟棄.因此,每當用戶需要完全的悲觀並發控制時,遊標都應在事務內打開.更新鎖將阻止任何其它任務獲取更新鎖或排它鎖,從而阻止其它任務更新該行.然而,更新鎖並不阻止***享鎖,所以它不會阻止其它任務讀取行,除非第二個任務也在要求帶更新鎖的讀取.滾動鎖根據在遊標定義的 Select 語句中指定的鎖提示,這些遊標並發選項可以生成滾動鎖.滾動鎖在提取時在每行上獲取,並保持到下次提取或者遊標關閉,以先發生者為準.下次提取時,服務器為新提取中的行獲取滾動鎖,並釋放上次提取中行的滾動鎖.滾動鎖獨立於事務鎖,並可以保持到壹個提交或回滾操作之後.如果提交時關閉遊標的選項為關,則 COMMIT 語句並不關閉任何打開的遊標,而且滾動鎖被保留到提交之後,以維護對所提取數據的隔離.所獲取滾動鎖的類型取決於遊標並發選項和遊標 Select 語句中的鎖提示.鎖提示 只讀 樂觀數值 樂觀行版本控制 鎖定無提示 未鎖定 未鎖定 未鎖定 更新 NOLOCK 未鎖定未鎖定未鎖定 未鎖定 HOLDLOCK ***享 ***享 ***享 更新 UPDLOCK 錯誤 更新 更新 更新 TABLOCKX 錯誤 未鎖定未鎖定更新其它 未鎖定 未鎖定 未鎖定 更新 *指定 NOLOCK 提示將使指定了該提示的表在遊標內是只讀的.
16,用Profiler來跟蹤查詢,得到查詢所需的時間,找出SQL的問題所在;用索引優化器優化索引
17,註意UNion和UNion all 的區別.UNION all好
18,註意使用DISTINCT,在沒有必要時不要用,它同UNION壹樣會使查詢變慢.重復的記錄在查詢裏是沒有問題的
19,查詢時不要返回不需要的行,列
20,用sp_configure 'query governor cost limit'或者SET QUERY_GOVERNOR_COST_LIMIT來限制查詢消耗的資源.當評估查詢消耗的資源超出限制時,服務器自動取消查詢,在查詢之前就扼殺掉. SET LOCKTIME設置鎖的時間.
21,用select top 100 / 10 Percent 來限制用戶返回的行數或者SET ROWCOUNT來限制操作的行
22,在SQL2000以前,壹般不要用如下的字句: "IS NULL", "<>", "!=", "!>", "!<", "NOT", "NOT EXISTS", "NOT IN", "NOT LIKE", and "LIKE '%500'",因為他們不走索引全是表掃描.也不要在Where字句中的列名加函數,如Convert,substring等,如果必須用函數的時候,創建計算列再創建索引來替代.還可以變通寫法:Where SUBSTRING(firstname,1,1) = 'm'改為Where firstname like 'm%'(索引掃描),壹定要將函數和列名分開.並且索引不能建得太多和太大.NOT IN會多次掃描表,使用EXISTS,NOT EXISTS ,IN , LEFT OUTER JOIN 來替代,特別是左連接,而Exists比IN更快,最慢的是NOT操作.如果列的值含有空,以前它的索引不起作用,現在2000的優化器能夠處理了.相同的是IS NULL,"NOT", "NOT EXISTS", "NOT IN"能優化她,而"<>"等還是不能優化,用不到索引.
23,使用Query Analyzer,查看SQL語句的查詢計劃和評估分析是否是優化的SQL.壹般的20%的代碼占據了80%的資源,我們優化的重點是這些慢的地方.
24,如果使用了IN或者OR等時發現查詢沒有走索引,使用顯示申明指定索引: Select * FROM PersonMember (INDEX = IX_Title) Where processid IN ('男','女')
25,將需要查詢的結果預先計算好放在表中,查詢的時候再Select.這在SQL7.0以前是最重要的手段.例如醫院的住院費計算.
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 ——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 = '號碼')