存儲過程優(yōu)化方法

SQL存儲過程出錯處理當(dāng)存儲過程出錯時，你希望在日志里看到SQLCODE=119還是SQL0119NAnexpressionstartingwith"CALL_AREA_CD"specifiedinaSELECTclause,HAVINGclause,orORDERBYclauseisnotspecifiedintheGROUPBYclauseoritisinaSELECTclause,HAVINGclause,orORDERBYclausewithacolumnfunctionandnoGROUPBYclauseisspecified.呢？其實DB2提供的GETDIAGNOSTICS語句可以獲得文字消息：DECLAREvMsgTextVARCHAR(256);--在存儲過程的ExitHandlerM面獲取SQL錯誤的文本消息GETDIAGNOSTICSEXCEPTION1vMsgText=MESSAGE_TEXT;—參考DB2文檔《SQLReferenceVolume2有了MESSAGE_TEXT,存儲過程的調(diào)試和查錯就方便多了。聲明臨時表需要注意指定合理的分區(qū)鍵＞建議聲明臨時表時顯式地指定分區(qū)鍵。很多時候，DB2缺省選擇的分區(qū)鍵是非常糟糕的。例子：PRO_TM_USR_CONSUME_MOCVS:/DSS代碼/21軟件代碼/05基線代碼/ETL/存儲過程/版本V01.400.000,CVS版本1.8代碼第75行測試結(jié)果：對該SESSION臨時表指定與目標(biāo)表TM_USR_CONSUME_MO一致的分區(qū)鍵后（只作此改動，其它代碼不修改），原來跑6個小時的存儲過程只需要不到2個小時。ETL.PRO_TM_CALLVAL_USR_MO_JF＞盡量使關(guān)聯(lián)表之間的分區(qū)鍵一致，并且關(guān)聯(lián)條件包含所有的分區(qū)列。目的是減少昂貴的分區(qū)間數(shù)據(jù)通信。＞盡量使源表、目標(biāo)表的分區(qū)鍵一致。目的是減少昂貴的分區(qū)間數(shù)據(jù)通信。

定義必要的約束創(chuàng)建約束的好處？顧名思義，約束的作用就是對數(shù)據(jù)進行約束，DB2不允許把違反約束規(guī)則的數(shù)據(jù)插入到數(shù)據(jù)庫。約束可以幫助我們盡早發(fā)現(xiàn)SQL中的邏輯錯誤。大部分程序缺陷是通過運行時的錯誤發(fā)現(xiàn)的，如果不定義任何約束，等于放棄了很多檢測錯誤的機會。例：唯一約束可以避免重復(fù)運行同一個INSERT語句帶來的惡果。唯一約束、主鍵約束都是通過索引實施的，實際上它們無異于一個唯一索引。最常用的約束有非空(NOTNULL)唯一(UNIQUE)ALTERTABLE<tabname>ADDUNIQUE(columnslist);主鍵(PRIMARYKEY)創(chuàng)建必要的索引當(dāng)需要從大量數(shù)據(jù)中選出少量數(shù)據(jù)時，我們需要索引。提高SQL提高SQL性能i=jRUNSTATS執(zhí)行RUNSTATS命令時要加上“WITHDISTRIBUTION”選項，統(tǒng)計各字段的取值分布情況，這也對AccessPlan的選擇有影響。CREATEINDEXKF2.IDX_CNSM_GZONKF2.TW_USR_CNSM_RNK_GZ(CURRMO_AMT_FEE);SELECT*FROMKF2.TW_USR_CNSM_RNK_GZWHERECURRMO_AMT_FEE=100;SELECT*FROMKF2.TW_USR_CNSM_RNK_GZWHERECURRMO_AMT_FEE<>100;建議RUNSTATS選項：RUNSTATSON<tabname>WITHDISTRIBUTIONANDINDEXESALL選擇不存在于另一表的數(shù)據(jù)看看以下三種寫法寫法1：SELECT...FROMAWHEREA.keyNOTIN(SELECTkeyFROMB);寫法2：SELECT...FROMALEFTJOINBONA.key=B.keyWHEREB.keyisnull;寫法3：SELECT...FROMAWHERENOTEXISTS(SELECT'x'FROMBWHEREA.key=B.key);寫法1采用NOTIN的寫法。很不幸DB2對于NOTIN通常采用TBSCAN(表掃描)，這是效率很差的寫法。最佳寫法是第三種寫法，如果B.key上有索引，它可以不用fetchB表的數(shù)據(jù)就可以完成查詢。第二種寫法采用對外表B的isnull判斷進行過濾，效率稍差。注：事實上，在DB2優(yōu)化器的作用下，第二種寫法與第三種寫法的存取方案相關(guān)無幾，只是第二種寫法比第三種寫法多了一步filter操作。建議使用第3種寫法，己使用第2種寫法的代碼也不必修改，因為其效率與第3種寫法差不多。例：SELECTA.*fromEDS.TW_BCUST_200409ALEFTOUTERJOINKF2.TW_BCUSTBONA.TM_INTRVL_CD=B.TM_INTRVL_CDandA.CUST_ID=B.CUST_IDandA.USR_ID=B.USR_IDandA.BCUST_EFF_MO=B.BCUST_EFF_MOWHEREB.TM_INTRVL_CDisnullSELECT*fromEDS.TW_BCUST_200409AwhereNOTEXISTS(select'x'fromKF2.TW_BCUSTBWHEREA.TM_INTRVL_CD=B.TM_INTRVL_CDandA.CUST_ID=B.CUST_IDandA.USR_ID=B.USR_IDandA.BCUST_EFF_MO=B.BCUST_EFF_MO)這兩種寫法對應(yīng)的存取方案：RETURNRETURN(11)(11)BTQBTQ(12)(12)FILTERHSJOIN(3)(3)|/\HSJOINTBSCANTBSCAN(4)(4)(5)/\||TBSCANTBSCANTable:Table:(5)(6)EDSKF2||TW_BCUST_200409TW_BCUSTTable:Table:EDSKF2TW_BCUST_200409TW_BCUST避免在WHERE子句中使用表達(dá)式例子：寫法1：WHERECMCC_BRANCH_CDLIKE‘GZ%’寫法2：WHERELEFT（CMCC_BRANCH_CD,2）=‘GZ’比較：建議使用第一種寫法，因為它允許使用索引。而第二種寫法只能用表掃描?，F(xiàn)在很多存儲過程中的代碼都采用了寫法2。避免groupby多個表的字段PS.好象很多時候無法避免，只能“盡量”了。Groupby子句中盡量不要帶表達(dá)式實例：PRO_TM_BUSI_USE_MO_JF，v01.02.000，CVS版本1.1CVS:/DSS代碼/21軟件代碼/05基線代碼/ETL/存儲過程/PRO_TM_BUSI_USE_MO_JF存儲過程需要運行1個小時左右（服務(wù)器忙時）。半個小時左右（服務(wù)器空閑時）改為GROUPBY不帶表達(dá)式（需聲明臨時表，并拆分為兩步），原來需半個小時的存儲過程只需10分鐘（服務(wù)器空閑時，估計服務(wù)器有負(fù)載時需要十幾分鐘）。修改原則：假設(shè)源數(shù)據(jù)行數(shù)為N0，帶表達(dá)式GROUPBY后的行數(shù)為N］。去掉GROUPBY字段中的VALUE，CASE等運算之后，GROUPBY結(jié)果為Ntmp，通常有：N0>Ntmp>N1如果N0>>Ntmp，且Ntmp不大，則可以把語句拆分成兩步，提高性能：第一步把groupby中的運算去掉，然后再把第一步的結(jié)果groupby一次。注意：用此方法拆分、。^目的是為了減少運算量以提高效率，如果N1和Ntmp相差不大，則不宜拆分（拆分后反而更慢）。例子：用戶月通話信息表匯總，廣州數(shù)據(jù)：N09億多Ntmp7億多N1接近4億這種情況下，拆分成兩步后用時比原來更長。UNIONandUNIONALL大家對UNION和UNIONALL了解可能比較清楚，這里就不多說了。減少GROUPBY的字段數(shù)>去掉GROUPBY子句中的常量>如果SELECT的某字段在源表中只有單一取值，可以用常量代替(有時此做法可能在一定程度上影響程序的可讀性)。例子：SELECTTM_INTRVL_CD,-1,-1,CASE(...),...FROMEDS.TW_USRCALL_MO_GZ200409WHERE...GROUPBYTM_INTRVL_CD,-1,-1,CASE(...),...因為月通話信息表