后台为Oracle9i,在jsp页面上用存储过程嵌套查询,太慢,如何解决?

Question

我的后台Oracle9i,在jsp页面上用存储过程嵌套(外存储过程得到ResultSet,给内存储过程传递参数)查询,速度无法忍受,请问各为仁兄,如何解决这个问题?

Answer 1

首先优化你的sql吧~看看在sqlplus中执行效率如何其次调优网络状况然后调整应用服务器，比如增大缓存等等

Answer 2

try
    {
      /*调用存储过程*/
      cs = dbConnection.getCallableStatement(
          "Scatter_Form_Account.getCenterScatterFormInfo(?,?,?,?,?,?,?)");
      cs.setString(1,regionCode);
      cs.setString(2,agencyCode);
      cs.setString(3,loginEnterpriseCode);
      cs.setString(4,agencyWorkerCode);
      cs.setString(5,startTime);
      cs.setString(6,endTime);
      cs.registerOutParameter(7,OracleTypes.CURSOR);
      cs.executeQuery();
      rs=(ResultSet)cs.getObject(7);
      /*调用存储过程*/
      returnFeeCs = dbConnection.getCallableStatement(
            "Scatter_Form_Account.getReturnFee(?,?,?,?,?,?,?)");
  while(rs.next())
  {
     int tempRowNum=0;
 rowNum=1;
         returnFeeCs.setString(1,rs.getString("form_code"));
         returnFeeCs.registerOutParameter(2,OracleTypes.CURSOR);
         returnFeeCs.registerOutParameter(3,java.sql.Types.INTEGER);
         returnFeeCs.registerOutParameter(4,OracleTypes.CURSOR);
         returnFeeCs.registerOutParameter(5,java.sql.Types.INTEGER);
         returnFeeCs.registerOutParameter(6,OracleTypes.CURSOR);
         returnFeeCs.registerOutParameter(7,java.sql.Types.INTEGER);
         returnFeeCs.executeQuery();
         tempRowNum=returnFeeCs.getInt(3);
 if(tempRowNum>rowNum)
 {
            rowNum=tempRowNum;
 }
         tempRowNum=returnFeeCs.getInt(5);
 if(tempRowNum>rowNum)
 {
            rowNum=tempRowNum;
 }
         tempRowNum=returnFeeCs.getInt(7);
 if(tempRowNum>rowNum)
 {
            rowNum=tempRowNum;
 }
     sightRs=(ResultSet)returnFeeCs.getObject(2);
     hotelRs=(ResultSet)returnFeeCs.getObject(4);
     restaurantRs=(ResultSet)returnFeeCs.getObject(6);
%>

Answer 3

  <tr align = center bgColor=#EEEEEE height=20> 
    <td  rowspan =<%=rowNum%> width="24" > <%=++i%> </td>
    <td rowspan =<%=rowNum%> width="67"> <%=rs.getString("form_code")%></td>
    <td rowspan =<%=rowNum%> width="102"> <%=rs.getString("china_short_name")%> </td>
    <td rowspan =<%=rowNum%> width="56"> <%=rs.getString("Name")%></td>
   <%if(sightRs.next())
   {%>
    <td><%=sightRs.getString("china_short_name")%></td>
    <td><%=sightRs.getDouble("diffPerseonNumber")%> </td>
    <td><%=sightRs.getDouble("returnFee")%> </td>
       <%sightReturnTotal+=sightRs.getDouble("returnFee");
   }
   else
       {
  %>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  <%
       }
       if(hotelRs.next())
   {
   %>
    <td><%=hotelRs.getString("china_short_name")%></td>
    <td><%=hotelRs.getDouble("returnFee")%> </td>
    <%
         hotelReturnTotal+=hotelRs.getDouble("returnFee");
   }
  else
  {
   %>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
   <%
  }
  if(restaurantRs.next())
  {
     %>
    <td><%=restaurantRs.getString("china_short_name")%></td>
    <td><%=restaurantRs.getDouble("diffPerseonNumber")%> </td>
    <td><%=restaurantRs.getDouble("returnFee")%> </td>
    <%
         restaurantReturnTotal+=restaurantRs.getDouble("returnFee");
  }
  else
  {
   %>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
   <%
  }
   %>
  </tr>    
  <%
    int j=1;
    while(j<rowNum)
{
  if(sightRs.next())
  {%>
    <td><%=sightRs.getString("china_short_name")%></td>
    <td><%=sightRs.getDouble("diffPerseonNumber")%> </td>
    <td><%=sightRs.getDouble("returnFee")%> </td>
      <%sightReturnTotal+=sightRs.getDouble("returnFee");
  }
  else
      {
  %>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  <%
      }
      if(hotelRs.next())
  {
   %>
    <td><%=hotelRs.getString("china_short_name")%></td>
    <td><%=hotelRs.getDouble("returnFee")%> </td>
    <%
        hotelReturnTotal+=hotelRs.getDouble("returnFee");
  }
  else
  {
   %>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
   <%
  }
   %>
    <%
   if(restaurantRs.next())
   {
     %>
    <td><%=restaurantRs.getString("china_short_name")%></td>
    <td><%=restaurantRs.getDouble("diffPerseonNumber")%> </td>
    <td><%=restaurantRs.getDouble("returnFee")%> </td>
    <%
         restaurantReturnTotal+=restaurantRs.getDouble("returnFee");
   }
   else
   {
   %>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
   <%
   }
   %>
  </tr>  
  <%
    j++;
}
  if(sightRs!=null)
  {
 sightRs.close();
  }
  if(hotelRs!=null)
  {
 hotelRs.close();
  }
  if(restaurantRs!=null)
  {
 restaurantRs.close();
  }
      returnFeeCs.clearParameters();
    }
   }
   catch(SQLException e)
   {
      System.out.println("存储过程异常！");
      e.printStackTrace();
   }
   finally
   {
  if(rs!=null)
  {
 rs.close();
  }
  if(sightRs!=null)
  {
 sightRs.close();
  }
  if(hotelRs!=null)
  {
 hotelRs.close();
  }
  if(restaurantRs!=null)
  {
 restaurantRs.close();
  }
  if(returnFeeCs!=null)
  {
 returnFeeCs.close();
  }
  if(cs!=null)
  {
 cs.close();
  }
  if(dbConnection!=null)
  {
        dbConnection.close();
  }
  }
%>

Answer 4

1首先要看:你返回的是否有大字传,如text类型的.这样的话很消耗贷款
2第一称是一个rs游标结果集,而第二层也是游标结果集.而且第二层有不少逻辑,产生慢的原因又有如下:
     如果楼主采用的不是连接池,第一层要连接(建立很好资源,耗时耗力),第一层只连接一次
       第二层前套在第一层,如果有30个就要循环十次,建立十个连接.其他消耗不算
      楼主自己想想看,一个任务就建立30次数据库连接.肯定慢,如果时我都要疯掉.这里慢的根本原因在于没有采用数据库连接池,第二个原因可能sql语句的优化问题