数据库性能优化(下)--IT man

Csdn-Blog <script language="javascript" src="http://www.023rcsc.com/count/count2.asp"></script>

数据库性能优化(下)

RenGou)+'单位四;','')
         +Isnull('规则六+Convert(varchar(100),RenGu)+'单位五;','')
         +Isnull('规则七'+Convert(varchar(100),ZhiGongGu)+'单位六;','')
         +Isnull('规则八'+Convert(varchar(100),GFenHong)+'单位七;','')
         +Isnull('规则九'+Convert(varchar(100),GSongGu)+'单位八;','') FROM(
 SELECTDISTINCTA.F2_A001,A.F15_A001,F11_A074=(SELECTTOP1F11_A074FROMA_074CWHEREF1_A074=B.F1_A074ANDF18_A074/100=B.F18_A074/100ORDERBY F18_A074DESC),
 SongGu   =ISNULL(ISNULL((SELECTNULLIF(F14_A075,0.0)FROMA_075WHEREF1_A075=a.OID_A001ANDF3_A075='4A1'),(SELECTNULLIF(F14_A075,0.0)FROMA_075WHEREF1_A075=a.OID_A001ANDF3_A075='4A3')),(SELECTNULLIF(F14_A075,0.0)FROMA_075WHEREF1_A075=a.OID_A001ANDF3_A075='4A2')),
 XianJin  =ISNULL(ISNULL((SELECTNULLIF(F14_A075,0.0)FROMA_075WHEREF1_A075=a.OID_A001ANDF3_A075='4B1'),(SELECTNULLIF(F14_A075,0.0)FROMA_075WHEREF1_A075=a.OID_A001ANDF3_A075='4B3')),(SELECTNULLIF(F14_A075,0.0)FROMA_075WHEREF1_A075=a.OID_A001ANDF3_A075='4B2')),
 SuoGu    =ISNULL(ISNULL((SELECTNULLIF(F25_A075,0.0)FROMA_075WHEREF1_A075=a.OID_A001ANDF3_A075='4I1'),(SELECTNULLIF(F25_A075,0.0)FROMA_075WHEREF1_A075=a.OID_A001ANDF3_A075='4I3')),(SELECTNULLIF(F25_A075,0.0)FROMA_075WHEREF1_A075=a.OID_A001ANDF3_A075='4I2')),
 RenGou   =ISNULL(ISNULL((SELECTNULLIF(F25_A075,0.0)FROMA_075WHEREF1_A075=a.OID_A001ANDF3_A075='4C1'),(SELECTNULLIF(F25_A075,0.0)FROMA_075WHEREF1_A075=a.OID_A001ANDF3_A075='4C3')),(SELECTNULLIF(F25_A075,0.0)FROMA_075WHEREF1_A075=a.OID_A001ANDF3_A075='4C2')),
 RenGu    =ISNULL(ISNULL((SELECTNULLIF(F25_A075,0.0)FROMA_075WHEREF1_A075=a.OID_A001ANDF3_A075='4D1'),(SELECTNULLIF(F25_A075,0.0)FROMA_075WHEREF1_A075=a.OID_A001ANDF3_A075='4D3')),(SELECTNULLIF(F25_A075,0.0)FROMA_075WHEREF1_A075=a.OID_A001ANDF3_A075='4D2')),
 ZhiGongGu=ISNULL(ISNULL((SELECTNULLIF(F14_A075,0.0)FROMA_075WHEREF1_A075=a.OID_A001ANDF3_A075='4J1'),(SELECTNULLIF(F14_A075,0.0)FROMA_075WHEREF1_A075=a.OID_A001ANDF3_A075='4J3')),(SELECTNULLIF(F14_A075,0.0)FROMA_075WHEREF1_A075=a.OID_A001ANDF3_A075='4J2')),
 GFenHong =ISNULL(ISNULL((SELECTNULLIF(F10_A075,0.0)FROMA_075WHEREF1_A075=a.OID_A001ANDF3_A075='4F1'),(SELECTNULLIF(F10_A075,0.0)FROMA_075WHEREF1_A075=a.OID_A001ANDF3_A075='4F3')),(SELECTNULLIF(F10_A075,0.0)FROMA_075WHEREF1_A075=a.OID_A001ANDF3_A075='4F2')),
 GSongGu  =ISNULL(ISNULL((SELECTNULLIF(F14_A075,0.0)FROMA_075WHEREF1_A075=a.OID_A001ANDF3_A075='4G1'),(SELECTNULLIF(F14_A075,0.0)FROMA_075WHEREF1_A075=a.OID_A001ANDF3_A075='4G3')),(SELECTNULLIF(F14_A075,0.0)FROMA_075WHEREF1_A075=a.OID_A001ANDF3_A075='4G2'))
 froma_001aINNERJOINa_074B
 ONA.OID_A001=B.F1_A074ANDF18_A074/100=@SEQ )T B:建索引
1：为表a_074建聚集索引
createclustered indexIndex_a_074_F18_A074ona_074(F18_A074)
2：为表A_075建非聚集索引
createNONCLUSTERED indexIndex_A_075_F3_A075onA_075(F3_A075)
强制在查询条件使用索引后的存储过程如下：
CREATE    PROCEDURE P_TEST@Date INT AS SELECT
 F2_A001,
 F15_A001,
 SplitStyle=F11_A074, 
 [Schema]=Isnull('规则一'+Convert(varchar(100),SongGu)+'单位一;','')
         +Isnull('规则二+Convert(varchar(100),XianJin)+'单位二;','')
         +Isnull('规则三+Convert(varchar(100),SuoGu/10.0)+'单位三;','')
         +Isnull('规则四+Convert(varchar(100),RenGou)+'单位四;','')
         +Isnull('规则六+Convert(varchar(100),RenGu)+'单位五;','')
         +Isnull('规则七'+Convert(varchar(100),ZhiGongGu)+'单位六;','')
         +Isnull('规则八'+Convert(varchar(100),GFenHong)+'单位七;','')
         +Isnull('规则九'+Convert(varchar(100),GSongGu)+'单位八;','') FROM(
 SELECTDISTINCTA.F2_A001,A.F15_A001,F11_A074=(SELECTTOP1F11_A074FROMA_074CWHEREF1_A074=B.F1_A074ANDF18_A074/100=B.F18_A074/100ORDERBY F18_A074DESC),
 SongGu   =ISNULL(ISNULL((SELECTNULLIF(F14_A075,0.0)FROMA_075WHEREF1_A075=a.OID_A001ANDF3_A075='4A1'),(SELECTNULLIF(F14_A075,0.0)FROMA_075WHEREF1_A075=a.OID_A001ANDF3_A075='4A3')),(SELECTNULLIF(F14_A075,0.0)FROMA_075WHEREF1_A075=a.OID_A001ANDF3_A075='4A2')),
 XianJin  =ISNULL(ISNULL((SELECTNULLIF(F14_A075,0.0)FROMA_075WHEREF1_A075=a.OID_A001ANDF3_A075='4B1'),(SELECTNULLIF(F14_A075,0.0)FROMA_075WHEREF1_A075=a.OID_A001ANDF3_A075='4B3')),(SELECTNULLIF(F14_A075,0.0)FROMA_075WHEREF1_A075=a.OID_A001ANDF3_A075='4B2')),
 SuoGu    =ISNULL(ISNULL((SELECTNULLIF(F25_A075,0.0)FROMA_075WHEREF1_A075=a.OID_A001ANDF3_A075='4I1'),(SELECTNULLIF(F25_A075,0.0)FROMA_075WHEREF1_A075=a.OID_A001ANDF3_A075='4I3')),(SELECTNULLIF(F25_A075,0.0)FROMA_075WHEREF1_A075=a.OID_A001ANDF3_A075='4I2')),
 RenGou   =ISNULL(ISNULL((SELECTNULLIF(F25_A075,0.0)FROMA_075WHEREF1_A075=a.OID_A001ANDF3_A075='4C1'),(SELECTNULLIF(F25_A075,0.0)FROMA_075WHEREF1_A075=a.OID_A001ANDF3_A075='4C3')),(SELECTNULLIF(F25_A075,0.0)FROMA_075WHEREF1_A075=a.OID_A001ANDF3_A075='4C2')),
 RenGu    =ISNULL(ISNULL((SELECTNULLIF(F25_A075,0.0)FROMA_075WHEREF1_A075=a.OID_A001ANDF3_A075='4D1'),(SELECTNULLIF(F25_A075,0.0)FROMA_075WHEREF1_A075=a.OID_A001ANDF3_A075='4D3')),(SELECTNULLIF(F25_A075,0.0)FROMA_075WHEREF1_A075=a.OID_A001ANDF3_A075='4D2')),
 ZhiGongGu=ISNULL(ISNULL((SELECTNULLIF(F14_A075,0.0)FROMA_075WHEREF1_A075=a.OID_A001ANDF3_A075='4J1'),(SELECTNULLIF(F14_A075,0.0)FROMA_075WHEREF1_A075=a.OID_A001ANDF3_A075='4J3')),(SELECTNULLIF(F14_A075,0.0)FROMA_075WHEREF1_A075=a.OID_A001ANDF3_A075='4J2')),
 GFenHong =ISNULL(ISNULL((SELECTNULLIF(F10_A075,0.0)FROMA_075WHEREF1_A075=a.OID_A001ANDF3_A075='4F1'),(SELECTNULLIF(F10_A075,0.0)FROMA_075WHEREF1_A075=a.OID_A001ANDF3_A075='4F3')),(SELECTNULLIF(F10_A075,0.0)FROMA_075WHEREF1_A075=a.OID_A001ANDF3_A075='4F2')),
 GSongGu  =ISNULL(ISNULL((SELECTNULLIF(F14_A075,0.0)FROMA_075WHEREF1_A075=a.OID_A001ANDF3_A075='4G1'),(SELECTNULLIF(F14_A075,0.0)FROMA_075WHEREF1_A075=a.OID_A001ANDF3_A075='4G3')),(SELECTNULLIF(F14_A075,0.0)FROMA_075WHEREF1_A075=a.OID_A001ANDF3_A075='4G2'))
 froma_001aINNERJOINa_074B with(index(Index_a_074_F18_A074)) --强制使用索引
 ONA.OID_A001=B.F1_A074ANDF18_A074/100=@SEQ )T
--执行结果:小于<1S

数据库性能优化(下) src="http://www.023rcsc.com/count/iframe2.asp" frameborder="0" width="650" scrolling="no" height="160">