存儲(chǔ)過(guò)程優(yōu)化方法

SQL存儲(chǔ)過(guò)程出錯(cuò)處理當(dāng)存儲(chǔ)過(guò)程出錯(cuò)時(shí)，你希望在日志里看到SQLCODE=119還是SQL0119NAnexpressionstartingwith"CALL_AREA_CD"specifiedinaSELECTclause,HAVINGclause,orORDERBYclauseisnotspecifiedintheGROUPBYclauseoritisinaSELECTclause,HAVINGclause,orORDERBYclausewithacolumnfunctionandnoGROUPBYclauseisspecified.呢？其實(shí)DB2提供的GETDIAGNOSTICS語(yǔ)句可以獲得文字消息：DECLAREvMsgTextVARCHAR(256);--在存儲(chǔ)過(guò)程的ExitHandlerM面獲取SQL錯(cuò)誤的文本消息GETDIAGNOSTICSEXCEPTION1vMsgText=MESSAGE_TEXT;—參考DB2文檔《SQLReferenceVolume2有了MESSAGE_TEXT,存儲(chǔ)過(guò)程的調(diào)試和查錯(cuò)就方便多了。聲明臨時(shí)表需要注意指定合理的分區(qū)鍵＞建議聲明臨時(shí)表時(shí)顯式地指定分區(qū)鍵。很多時(shí)候，DB2缺省選擇的分區(qū)鍵是非常糟糕的。例子：PRO_TM_USR_CONSUME_MOCVS:/DSS代碼/21軟件代碼/05基線代碼/ETL/存儲(chǔ)過(guò)程/版本V01.400.000,CVS版本1.8代碼第75行測(cè)試結(jié)果：對(duì)該SESSION臨時(shí)表指定與目標(biāo)表TM_USR_CONSUME_MO一致的分區(qū)鍵后（只作此改動(dòng)，其它代碼不修改），原來(lái)跑6個(gè)小時(shí)的存儲(chǔ)過(guò)程只需要不到2個(gè)小時(shí)。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)建約束的好處？顧名思義，約束的作用就是對(duì)數(shù)據(jù)進(jìn)行約束，DB2不允許把違反約束規(guī)則的數(shù)據(jù)插入到數(shù)據(jù)庫(kù)。約束可以幫助我們盡早發(fā)現(xiàn)SQL中的邏輯錯(cuò)誤。大部分程序缺陷是通過(guò)運(yùn)行時(shí)的錯(cuò)誤發(fā)現(xiàn)的，如果不定義任何約束，等于放棄了很多檢測(cè)錯(cuò)誤的機(jī)會(huì)。例：唯一約束可以避免重復(fù)運(yùn)行同一個(gè)INSERT語(yǔ)句帶來(lái)的惡果。唯一約束、主鍵約束都是通過(guò)索引實(shí)施的，實(shí)際上它們無(wú)異于一個(gè)唯一索引。最常用的約束有非空(NOTNULL)唯一(UNIQUE)ALTERTABLE<tabname>ADDUNIQUE(columnslist);主鍵(PRIMARYKEY)創(chuàng)建必要的索引當(dāng)需要從大量數(shù)據(jù)中選出少量數(shù)據(jù)時(shí)，我們需要索引。提高SQL提高SQL性能i=jRUNSTATS執(zhí)行RUNSTATS命令時(shí)要加上“WITHDISTRIBUTION”選項(xiàng)，統(tǒng)計(jì)各字段的取值分布情況，這也對(duì)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選項(xiàng)：RUNSTATSON<tabname>WITHDISTRIBUTIONANDINDEXESALL選擇不存在于另一表的數(shù)據(jù)看看以下三種寫(xiě)法寫(xiě)法1：SELECT...FROMAWHEREA.keyNOTIN(SELECTkeyFROMB);寫(xiě)法2：SELECT...FROMALEFTJOINBONA.key=B.keyWHEREB.keyisnull;寫(xiě)法3：SELECT...FROMAWHERENOTEXISTS(SELECT'x'FROMBWHEREA.key=B.key);寫(xiě)法1采用NOTIN的寫(xiě)法。很不幸DB2對(duì)于NOTIN通常采用TBSCAN(表掃描)，這是效率很差的寫(xiě)法。最佳寫(xiě)法是第三種寫(xiě)法，如果B.key上有索引，它可以不用fetchB表的數(shù)據(jù)就可以完成查詢。第二種寫(xiě)法采用對(duì)外表B的isnull判斷進(jìn)行過(guò)濾，效率稍差。注：事實(shí)上，在DB2優(yōu)化器的作用下，第二種寫(xiě)法與第三種寫(xiě)法的存取方案相關(guān)無(wú)幾，只是第二種寫(xiě)法比第三種寫(xiě)法多了一步filter操作。建議使用第3種寫(xiě)法，己使用第2種寫(xiě)法的代碼也不必修改，因?yàn)槠湫逝c第3種寫(xiě)法差不多。例：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)這兩種寫(xiě)法對(duì)應(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á)式例子：寫(xiě)法1：WHERECMCC_BRANCH_CDLIKE‘GZ%’寫(xiě)法2：WHERELEFT（CMCC_BRANCH_CD,2）=‘GZ’比較：建議使用第一種寫(xiě)法，因?yàn)樗试S使用索引。而第二種寫(xiě)法只能用表掃描?，F(xiàn)在很多存儲(chǔ)過(guò)程中的代碼都采用了寫(xiě)法2。避免groupby多個(gè)表的字段PS.好象很多時(shí)候無(wú)法避免，只能“盡量”了。Groupby子句中盡量不要帶表達(dá)式實(shí)例：PRO_TM_BUSI_USE_MO_JF，v01.02.000，CVS版本1.1CVS:/DSS代碼/21軟件代碼/05基線代碼/ETL/存儲(chǔ)過(guò)程/PRO_TM_BUSI_USE_MO_JF存儲(chǔ)過(guò)程需要運(yùn)行1個(gè)小時(shí)左右（服務(wù)器忙時(shí)）。半個(gè)小時(shí)左右（服務(wù)器空閑時(shí)）改為GROUPBY不帶表達(dá)式（需聲明臨時(shí)表，并拆分為兩步），原來(lái)需半個(gè)小時(shí)的存儲(chǔ)過(guò)程只需10分鐘（服務(wù)器空閑時(shí)，估計(jì)服務(wù)器有負(fù)載時(shí)需要十幾分鐘）。修改原則：假設(shè)源數(shù)據(jù)行數(shù)為N0，帶表達(dá)式GROUPBY后的行數(shù)為N］。去掉GROUPBY字段中的VALUE，CASE等運(yùn)算之后，GROUPBY結(jié)果為Ntmp，通常有：N0>Ntmp>N1如果N0>>Ntmp，且Ntmp不大，則可以把語(yǔ)句拆分成兩步，提高性能：第一步把groupby中的運(yùn)算去掉，然后再把第一步的結(jié)果groupby一次。注意：用此方法拆分、。^目的是為了減少運(yùn)算量以提高效率，如果N1和Ntmp相差不大，則不宜拆分（拆分后反而更慢）。例子：用戶月通話信息表匯總，廣州數(shù)據(jù)：N09億多Ntmp7億多N1接近4億這種情況下，拆分成兩步后用時(shí)比原來(lái)更長(zhǎng)。UNIONandUNIONALL大家對(duì)UNION和UNIONALL了解可能比較清楚，這里就不多說(shuō)了。減少GROUPBY的字段數(shù)>去掉GROUPBY子句中的常量>如果SELECT的某字段在源表中只有單一取值，可以用常量代替(有時(shí)此做法可能在一定程度上影響程序的可讀性)。例子：SELECTTM_INTRVL_CD,-1,-1,CASE(...),...FROMEDS.TW_USRCALL_MO_GZ200409WHERE...GROUPBYTM_INTRVL_CD,-1,-1,CASE(...),...因?yàn)樵峦ㄔ捫畔⒈?/p>