如此大的一条sql语句在30个左右的并发访问系统当中的性能问题?

04-11-05 eway

现在一个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

Azure_2003
2004-11-05 11:38

一般来说连接漏洞并不是sql语句造成的,是connect和close造成的,你应该先从对方的程序逻辑着手,不应该是数据库的sql语句

eway
2004-11-05 13:34

我知道连接漏洞不是sql造成,但是这个sql是否造成性能瓶颈??毕竟关联了多张表,还有很多分支。

其实是2个问题:

1、db连接漏洞,造成多个数据库连接可否造成系统的上述现象?

2、这种sql查询,是否对性能影响较大?