如此大的一條sql語句在30個左右的併發訪問系統當中的效能問題？

eway發表於2004-11-05
SQL
現在一個web系統在併發訪問高峰狀況下出現響應十分緩慢（幾分鐘甚至幾十分鐘）的現象，不知道是什麼原因導致？db連線數是否有較大影響？對方使用的是一個自實現的連線池，但是我發現存在漏洞，連線數達到60多個。而且發現其查詢sql語句如下，是否主要瓶頸？？？

SELECT N_COMESHIPINFOID,N_SHIPID, NVC_SHIPNAME, NVC_SHIPENGNAME, NVC_SHIPCODE,
NVC_SHIPCOMPANYNAME, NVC_COUNTRYNAME, N_SHIPLOC, NVC_SHIPTYPENAME, NVC_SHIPTYPECODE,
N_QUICKSEND, NVC_SHIPPERCODE, NVC_SHIPPERNAME, NVC_BIGGOODSCODE, NVC_BIGGOODSNAME,
to_char(D_DISEMBOGUETIME,'MM-DD hh24:mi')D_DISEMBOGUETIME, to_char(D_FORECASTTOTIME,
'YYYY-MM-DD hh24:mi')D_FORECASTTOTIME, to_char(D_FORECASTTOTIME,'MM-DD hh24:mi')D_FORECASTTOTIME1,
N_DRAINTIME, NVC_FLOWWAYCODE, NVC_FLOWWAYNAME, NVC_BIOLOGYTESTCODE, NVC_BIOLOGYTESTNAME, N_SHIPLENGTH,
N_SHIPWIDTH, N_WATERLINE, N_LOAD, N_STOWAGE, NVC_BIGGOODSNAME, NVC_SHIPDEPUTYCODE,
NVC_SHIPDEPUTYNAME,N_DELIVERFLAG, N_TUGBOATFLAG,
to_char(D_CUSTOMSTIME,'MM-DD hh24:mi')D_CUSTOMSTIME, NVC_REMARK, to_char(D_COMETIME,'MM-DD hh24:mi')D_COMETIME,
to_char(D_VERIFYEND_TIME,'MM-DD hh24:mi')D_VERIFYEND_TIME,
(case when NVC_BERTHNAME is not null then NVC_BERTHNAME||'-'||to_char((select count(n_shipnumber)
from v_currenttaskplan a where a.NVC_BERTHKEY=t.NVC_BERTHKEY and a.n_shipnumber<=t.n_shipnumber)) end)
as NVC_BERTHNAME, to_char(D_ANCHORAGETIME,'MM-DD hh24:mi')D_ANCHORAGETIME,
to_char(D_CREATETIME,'MM-DD hh24:mi')D_CREATETIME, NVC_LITTERGOODSNAME,NVC_BURCOMPANYNAME,
NVC_SFPERSONNAME,NVC_MTYPENAME,N_ROUTEKEY,NVC_SEAROUTENAME,NVC_SHIPETYPEKEY,nvc_stationname,
NVC_SHIPCONSTANT,NVC_REMARK,ChangeFlag,NVC_PROCEDUREFLAG,secondberth,n_planmoveberthflag
FROM V_PREDICTSHIP t WHERE N_SHIPLOC=0 OR N_SHIPLOC=1 order by t.NVC_BIGGOODSNAME,
t.nvc_tradekind,N_SHIPLOC desc,(case when t.N_SHIPLOC=0 then t.D_FORECASTTOTIME
else t.D_COMETIME end),t.nvc_berthCompCode,t.NVC_BERTHNAME,t.n_shipnumber

相關文章