查看單個文章
舊 2006-03-24, 03:55 AM   #5 (permalink)
psac
榮譽會員
 
psac 的頭像
榮譽勳章
UID - 3662
在線等級: 級別:30 | 在線時長:1048小時 | 升級還需:37小時級別:30 | 在線時長:1048小時 | 升級還需:37小時級別:30 | 在線時長:1048小時 | 升級還需:37小時級別:30 | 在線時長:1048小時 | 升級還需:37小時級別:30 | 在線時長:1048小時 | 升級還需:37小時
註冊日期: 2002-12-07
住址: 木柵市立動物園
文章: 17381
現金: 5253 金幣
資產: 33853 金幣
預設

儲存於程序編寫經驗和最佳化措施
經驗之談,看看……

一、適合讀者對像:資料庫開發程序員,資料庫的資料量很多,涉及到對SP(儲存於程序)的最佳化的項目開發人員,對資料庫有濃厚興趣的人。  

  二、介紹:在資料庫的開發程序中,經常會遇到複雜的業務邏輯和對資料庫的操作,這個時候就會用SP來封裝資料庫操作。如果項目的SP較多,書寫又沒有一定的規範,將會影響以後的系統維護困難和大SP邏輯的難以理解,另外如果資料庫的資料量大或者項目對SP的效能要求很,就會遇到最佳化的問題,否則速度有可能很慢,經過親身經驗,一個經過最佳化過的SP要比一個效能差的SP的效率甚至高幾百倍。  

  三、內容:  

  1、開發人員如果用到其他庫的Table或View,務必在當前庫中建立View來實現跨庫操作,最好不要直接使用「databse.dbo.table_name」,因為sp_depends不能顯示出該SP所使用的跨庫table或view,不方便校驗。  

  2、開發人員在提交SP前,必須已經使用set showplan on分析過查詢計劃,做過自身的查詢最佳化檢查。  

  3、高程序執行效率,最佳化應用程式,在SP編寫程序中應該注意以下幾點:   

  a)SQL的使用規範:

   i. 盡量避免大事務操作,慎用holdlock子句,提高系統並發能力。

   ii. 盡量避免反覆訪問同一張或幾張表,尤其是資料量較大的表,可以考慮先根據條件抽取資料到臨時表中,然後再做連接。

   iii. 盡量避免使用游標,因為游標的效率較差,如果游標操作的資料超過1萬行,那麼就應該改寫;如果使用了游標,就要盡量避免在游標循環中再進去行表連接的操作。

   iv. 注意where字句寫法,必須考慮語句順序,應該根據索引順序、範圍大小來確定條件子句的前後順序,盡可能的讓字段順序與索引順序相一致,範圍從大到小。

   v. 不要在where子句中的「=」左邊進行函數、算術運算或其他陳述式運算,否則系統將可能無法正確使用索引。

   vi. 盡量使用exists替代select count(1)來判斷是否存在記錄,count函數只有在統計表中所有行數時使用,而且count(1)比count(*)更有效率。

   vii. 盡量使用「>=」,不要使用「>」。

   viii. 注意一些or子句和union子句之間的取代

   ix. 注意表之間連接的資料檔案類型,避免不同檔案類型資料之間的連接。

   x. 注意儲存於程序中參數和資料檔案類型的關係。

   xi. 注意insert、update操作的資料量,防止與其他套用衝突。如果資料量超過200個資料頁面(400k),那麼系統將會進行鎖昇級,頁級鎖會昇級成表級鎖。   

  b)索引的使用規範:

   i. 索引的新增要與套用結合考慮,建議大的OLTP表不要超過6個索引。

   ii. 盡可能的使用索引字段作為查詢條件,尤其是聚簇索引,必要時可以通過index index_name來強制指定索引

   iii. 避免對大表查詢時進行table scan,必要時考慮新增索引。

   iv. 在使用索引字段作為條件時,如果該索引是聯合索引,那麼必須使用到該索引中的第一個字段作為條件時才能保證系統使用該索引,否則該索引將不會被使用。

   v. 要注意索引的維護,週期性重建索引,重新編譯儲存於程序。  

  c)tempdb的使用規範:

   i. 盡量避免使用distinct、order by、group by、having、join、cumpute,因為這些語句會加重tempdb的負擔。

   ii. 避免頻繁新增和移除臨時表,減少系統表資源的消耗。

   iii. 在新增臨時表時,如果一次性插入資料量很大,那麼可以使用select into替代create table,避免log,提高速度;如果資料量不大,為了緩和系統表的資源,建議先create table,然後insert。

   iv. 如果臨時表的資料量較大,需要建立索引,那麼應該將新增臨時表和建立索引的程序放在單獨一個子儲存於程序中,這樣才能保證系統能夠很好的使用到該臨時表的索引。

    v. 如果使用到了臨時表,在儲存於程序的最後務必將所有的臨時表顯式移除,先truncate table,然後drop table,這樣可以避免系統表的較長時間鎖定。

    vi. 慎用大的臨時表與其他大表的連接查詢和修改,減低系統表負擔,因為這種操作會在一條語句中多次使用tempdb的系統表。  

  d)合理的算法使用:   

  根據上面已提到的SQL最佳化技術和ASE Tuning手冊中的SQL最佳化內容,結合實際套用,採用多種算法進行比較,以獲得消耗資源最少、效率最高的方法。具體可用ASE調優指令:set statistics io on, set statistics time on , set showplan on 等。
__________________
http://bbsimg.qianlong.com/upload/01/08/29/68/1082968_1136014649812.gif
psac 目前離線  
送花文章: 3, 收花文章: 1631 篇, 收花: 3205 次