2 msx985211 msx985211 于 2016.04.01 14:59 提问

Java遍历结果集过慢该怎么解决

各位大神,我数据库中一共才20条数据,但是每次查询的时候都巨慢,
从点击查询到把结果显示在前端,一共需要十多秒。
我在语句执行过程中打印了时间,发现执行完查询语句耗时0.05秒,
遍历结果集耗时13秒,把结果集存入list耗时0.03秒,显然最耗时的就是遍历结果集,
各位大神有啥好的解决办法吗?

13个回答

daidaineteasy
daidaineteasy   Ds   Rxr 2016.04.01 15:10

话说能贴一下代码么,特别想知道你是怎么遍历的~~,太好奇了

msx985211
msx985211 粘出来了
一年多之前 回复
wojiushiwo945you
wojiushiwo945you   Ds   Rxr 2016.04.01 15:09

你是怎么遍历的?即使是ORM框架映射也没有这么慢啊。

msx985211
msx985211 粘出来了
一年多之前 回复
k0363224
k0363224   2016.04.01 15:10

这个没法回答啊,要看具体的代码和数据,如果仅仅是20条数据的遍历,怎么也不会那么久

qwertyuiop20000
qwertyuiop20000 回复msx985211:在哪里啊?
一年多之前 回复
qwertyuiop20000
qwertyuiop20000 回复msx985211:在哪里啊?
一年多之前 回复
msx985211
msx985211 粘出来了
一年多之前 回复
w741998232
w741998232   2016.04.01 15:26

最好贴出代码吧,不然不知道你用什么方法遍历就没办法纠错

msx985211
msx985211 粘出来了
一年多之前 回复
xy_focus
xy_focus   2016.04.01 17:10
msx985211
msx985211 非常感谢,你写的这个JDBC很不错
一年多之前 回复
msx985211
msx985211   2016.04.01 15:36

这是所有的代码
public static void SearchCrisisReport(CrisisReportInfoResponMessage resp, GeneralInfoMessage message,
CrisisReportItemRespon condition, String accountName)
{
Connection conn = DBPool.getConnection();
if(conn == null)
{
logger.error(I18nManager.getValue("db", "ConnFiled"));
resp.responseCommand = I18nManager.getValue("db", "ConnFiled");
return;
}

try
{
  String userSql = "select accountName, userName, typeOridial from ManagerList where accountName='"+accountName+"'";
  ResultSet rs = DBPool.QueryCommandToDB(conn, userSql);
  String userName = "";
  int typeOridial = -1;
  if(rs != null)
  {
    Date date = new Date();
    SimpleDateFormat formater = new SimpleDateFormat("YY-MM-dd HH:mm:ss.SSS");
    String d = formater.format(date);
    System.out.println("------------1----------"+d);
    while(rs.next())
    {
      userName = rs.getString("userName");
      typeOridial = rs.getInt("typeOridial");
    }
    DBPool.closeResultSet(rs);;
  }

  if (!userName.trim().equals(""))
  {
    userName = SlotPath.unescape(userName);
    System.out.println("userName is " + userName);
  }


  HashMap enToCnMap = new HashMap();
  userSql = "select crisisCode, crisisName from crisisInfo ";
  rs = DBPool.QueryCommandToDB(conn, userSql);
  if(rs != null)
  {
    Date date = new Date();
    SimpleDateFormat formater = new SimpleDateFormat("YY-MM-dd HH:mm:ss.SSS");
    String d = formater.format(date);
    System.out.println("------------2----------"+d);
    while(rs.next())
    {
      enToCnMap.put(rs.getString("crisisCode"), rs.getString("crisisName"));
    }
    DBPool.closeResultSet(rs);
  }

  StringBuffer reportSql = new StringBuffer();

  reportSql.append("select a.* ");
  reportSql.append(" from crisisReportList a, storeInfo b ");

  reportSql.append(", (select accountName, userName, bigZoneCode, smallZoneCode from managerList ");
  reportSql.append(" where accountName = '");
  reportSql.append(accountName);
  reportSql.append("') c ");

  if(typeOridial == BSTBasicUserType.APSMALL_ZONE_MANAGER)
  {

// reportSql.append(" where a.storeId=b.storeId and b.isDel!=1 and a.status>0 and c.bigZoneCode = a.bigZone and c.smallZoneCode = a.smallZone ");//能看见本小区所有报告用这句,只能看见本小区指给自己的用下一句
reportSql.append(" where a.storeId=b.storeId and b.isDel!=1 and a.status>0 and c.bigZoneCode = a.bigZone and c.smallZoneCode = a.smallZone and a.respon=c.accountName ");
}
if(typeOridial==BSTBasicUserType.APBIG_ZONE_MANAGER)
{
// reportSql.append(" where a.storeId=b.storeId and b.isDel!=1 and a.status>0 and c.bigZoneCode = a.bigZone ");//能看见本大区所有的报告用这句,只能看见本大区指给自己的用下一句
reportSql.append(" where a.storeId=b.storeId and b.isDel!=1 and a.status>0 and c.bigZoneCode = a.bigZone and a.respon=c.accountName ");
}
if(typeOridial==BSTBasicUserType.APSTORE_MANAGER)
{
reportSql.append(", apmStoreScope d ");
reportSql.append(" where (a.storeId=b.storeId and b.storeId=d.storeId and b.isDel!=1 and c.accountName = a.approver ) ");
}
if(typeOridial==BSTBasicUserType.APSYS_ADMIN || typeOridial==BSTBasicUserType.HQCRISIS_GROUP)
{
reportSql.append(" where a.storeId=b.storeId and b.isDel!=1 ");
reportSql.append(" and a.status>0 ");
}

  if (condition != null)
  {
    if (!condition.storeId.trim().equals(""))
    {
      reportSql.append(" and  ");
      reportSql.append("a.storeId ='");
      reportSql.append(condition.storeId);
      reportSql.append("' ");
    }

    if (!condition.reportCode.trim().equals(""))
    {
      reportSql.append(" and reportCode ='");
      reportSql.append(condition.reportCode);
      reportSql.append("' ");
    }

    if (!condition.startTime.trim().equals(""))
    {
      reportSql.append(" and eventTime >='");
      reportSql.append(condition.startTime);
      reportSql.append("' ");
    }

    if (!condition.endTime.trim().equals(""))
    {
      reportSql.append(" and eventTime <='");
      reportSql.append(condition.endTime);
      reportSql.append("' ");
    }

    if (!condition.bigZone.trim().equals(""))
    {
      reportSql.append(" and b.bigZone='");
      reportSql.append(condition.bigZone);
      reportSql.append("' ");
    }

    if (!condition.smallZone.trim().equals(""))
    {
      reportSql.append(" and b.smallZone='");
      reportSql.append(condition.smallZone);
      reportSql.append("' ");
    }
  }

  reportSql.append("order by storeId, reportCode");
  System.out.println(reportSql.toString());
  ResultSet rs1 = DBPool.QueryCommandToDB(conn, reportSql.toString());

  if (rs1 != null)
  {
    Date date = new Date();
    SimpleDateFormat formater = new SimpleDateFormat("YY-MM-dd HH:mm:ss.SSS");
    String d = formater.format(date);
    System.out.println("------------3----------"+d);

    LinkedList reportList = new LinkedList();
    int ind1 = rs1.findColumn("reportCode");//查找索引
    int ind2 = rs1.findColumn("storeId");

    while (rs1.next())
    {
      CrisisReportItemRespon tmp = new CrisisReportItemRespon();       
      tmp.reportCode = rs1.getString(ind1);//使用索引
      tmp.storeId = rs1.getString(ind2) ;
      tmp.createDate = rs1.getString("createDate") ;
      tmp.eventLevel = rs1.getString("eventLevel")  ;
      tmp.eventClass = rs1.getString("eventClass")  ;
      tmp.eventTime = rs1.getString("eventTime")  ;
      tmp.eventLocation = rs1.getString("eventLocation") ;
      tmp.reportDesc  = rs1.getString("reportDesc") ;
      tmp.responsibility = rs1.getString("responsibility")  ;
      tmp.improvement = rs1.getString("improvement")  ;
      tmp.improveDate = rs1.getString("improveDate")  ;
      tmp.improveTrack = rs1.getString("improveTrack")  ;
      tmp.status = rs1.getInt("status")  ;
      tmp.responType = rs1.getString("responType") ;
      tmp.responCreateDate = rs1.getString("responCreateDate") ;
      tmp.mailCycle = rs1.getInt("mailCycle") ;
      tmp.eventClassCn = (String)enToCnMap.get(tmp.eventClass);
      tmp.eventLocationCn = (String)enToCnMap.get(tmp.eventLocation);
      tmp.eventLevelCn = (String)enToCnMap.get(tmp.eventLevel);

      //下面这几行是为了查危机事件填报者approver加的
      String approver = rs1.getString("approver");
      String approverSql = "select userName from managerList where accountName = '"+approver+"'";
      ResultSet rs2 = DBPool.QueryCommandToDB(conn, approverSql);
      if (rs2!=null)
      {
        while(rs2.next())
        {
          tmp.approver = rs2.getString("userName");
        }
        DBPool.closeResultSet(rs2);
      }

      //下面这几行是为了查询改进人、审批人、审核人用的
      String respon = rs1.getString("respon");
      String responSql = "select userName from managerList where accountName = '"+respon+"'";
      ResultSet rs3 = DBPool.QueryCommandToDB(conn, responSql);
      if (rs3!=null)
      {
        while(rs3.next())
        {
          tmp.respon = rs3.getString("userName");
        }
        DBPool.closeResultSet(rs3);
      }
      reportList.add(tmp);
    }
    Date date1 = new Date();
    SimpleDateFormat formater1 = new SimpleDateFormat("YY-MM-dd HH:mm:ss.SSS");
    String d1 = formater1.format(date1);
    System.out.println("------------4----------"+d1);

    DBPool.closeResultSet(rs1);
    if (reportList.size() >0)
    {
      for (int i = 0; i < reportList.size(); i++)
      {
        CrisisReportItemRespon tmp = (CrisisReportItemRespon)reportList.get(i);
        if(tmp.eventClass.equals("D") || tmp.eventClass.equals("E"))
        {
          StringBuffer estateSql = new StringBuffer();
          estateSql.append("select * from crisisEstateReportDetail where reportCode='");
          estateSql.append(tmp.reportCode);
          estateSql.append("'");
          ResultSet estateRs = DBPool.QueryCommandToDB(conn, estateSql.toString());
          if (estateRs != null)
          {
            Date date2 = new Date();
            SimpleDateFormat formater2 = new SimpleDateFormat("YY-MM-dd HH:mm:ss.SSS");
            String d2 = formater2.format(date2);
            System.out.println("------------5----------"+d2);
            estateRs.last();
            int m = estateRs.getRow();
            estateRs.beforeFirst();
            tmp.estateDetails = new CrisisEstateReportDetailItemRespon[m];
            int k = 0;
            int index = estateRs.findColumn("reportCode");
            while(estateRs.next())
            {
              CrisisEstateReportDetailItemRespon estateItem = new CrisisEstateReportDetailItemRespon();
              estateItem.id = estateRs.getInt("id");
              estateItem.reportCode = estateRs.getString(index);
              estateItem.eventClass = estateRs.getString("eventClass") ;
              estateItem.accidentType = estateRs.getString("accidentType") ;
              estateItem.lose = estateRs.getInt("lose");
              estateItem.mediaBadReport = estateRs.getString("mediaBadReport") ;
              estateItem.isInterruption = estateRs.getInt("isInterruption") ;
              estateItem.resaleTime = estateRs.getString("resaleTime") ;
              estateItem.interruptionTime = estateRs.getString("interruptionTime") ;
              estateItem.accidentReason = estateRs.getString("accidentReason") ;
              estateItem.insuranceApply = estateRs.getString("insuranceApply");
              estateItem.otherReason = estateRs.getString("otherReason") ;
              estateItem.mediaBadReportCn = (String)enToCnMap.get(estateItem.mediaBadReport);
              estateItem.accidentTypeCn = (String)enToCnMap.get(estateItem.accidentType);
              tmp.estateDetails[k] = estateItem;
              k++;
            }
            DBPool.closeResultSet(estateRs);
          }
        }
        else
        {
          Date date2 = new Date();
          SimpleDateFormat formater2 = new SimpleDateFormat("YY-MM-dd HH:mm:ss.SSS");
          String d2 = formater2.format(date2);
          System.out.println("------------6----------"+d2);
          StringBuffer personSql = new StringBuffer();
          personSql.append("select * from crisisPersonReportDetail where reportCode='");
          personSql.append(tmp.reportCode);
          personSql.append("'");
          ResultSet personRs = DBPool.QueryCommandToDB(conn, personSql.toString());
          if (personRs != null)
          {
            personRs.last();
            int m = personRs.getRow();
            personRs.beforeFirst();
            tmp.presonDetails = new CrisisPersonReportDetailItemRespon[m];
            int k = 0;
            int index = personRs.findColumn("reportCode");//从数据库中获取索引
            while(personRs.next())
            {
              CrisisPersonReportDetailItemRespon personItem = new CrisisPersonReportDetailItemRespon();
              personItem.id = personRs.getInt("id");
              personItem.reportCode = personRs.getString(index);//引用索引
              personItem.eventClass = personRs.getString("eventClass") ;
              personItem.injuryLevel = personRs.getString("injuryLevel") ;
              personItem.injuryClass = personRs.getString("injuryClass");
              personItem.injuryContent = personRs.getString("injuryContent") ;
              personItem.injuryerName = personRs.getString("injuryerName") ;
              personItem.injuryerPhone = personRs.getString("injuryerPhone") ;
              personItem.cost = personRs.getFloat("cost");
              personItem.injuryerSex = personRs.getInt("injuryerSex");
              personItem.injuryerAge = personRs.getInt("injuryerAge");
              personItem.injuryerAddress = personRs.getString("injuryerAddress") ;
              personItem.stopWorkDate = personRs.getString("stopWorkDate") ;
              personItem.entryWorkDate = personRs.getString("entryWorkDate");
              personItem.reworkDate = personRs.getString("reworkDate");
              personItem.changeWorkDate = personRs.getString("changeWorkDate");
              personItem.injuryLevelCn = (String)enToCnMap.get(personItem.injuryLevel) ;
              personItem.injuryClassCn = (String)enToCnMap.get(personItem.injuryClass);
              personItem.injuryContentCn = (String)enToCnMap.get(personItem.injuryContent) ;

              tmp.presonDetails[k] = personItem;
              k++;
            }
            DBPool.closeResultSet(personRs);
          }
        }
      }
      resp.items  = (CrisisReportItemRespon[]) reportList.toArray(new CrisisReportItemRespon[reportList.size()]);

      Date date2 = new Date();
      SimpleDateFormat formater2 = new SimpleDateFormat("YY-MM-dd HH:mm:ss.SSS");
      String d2 = formater2.format(date2);
      System.out.println("------------7----------"+d2);
    }
  }
  resp.responseCommand = "OK";
}
catch(Exception e)
{
  logger.error(I18nManager.getValue("db", "SearchCrisisReportFailed")+e.getMessage());
  resp.responseCommand = I18nManager.getValue("db", "SearchCrisisReportFailed")+e.getMessage();
  DBPool.destroyConnection(conn);
}

DBPool.destroyConnection(conn);

}

msx985211
msx985211   2016.04.01 15:40

这代码斩出来怎么有的在框里,有的不在,各位凑合看下吧,看看啥地方需要改进一下啊,先谢过各位了

qq_17637725
qq_17637725   2016.04.01 15:44

你这逻辑处理那么多 是需要花点时间的 坐等大神解决 学习下

msx985211
msx985211
一年多之前 回复
msx985211
msx985211   2016.04.01 16:07

求助各位大神,小弟这里先谢过了

xy_focus
xy_focus   2016.04.01 17:11

还有就是没有看到你关闭连接对象,如此下去坑定越来越慢

msx985211
msx985211 回复Back_In_Time: 你意思是先查出数据来,然后紧接着就关闭连接吗?
一年多之前 回复
xy_focus
xy_focus 回复msx985211: 你不能把数据查出来关闭连接在判断吗?
一年多之前 回复
msx985211
msx985211 我用的连接池DBPool,关闭连接用的就是DBPool.destroyConnection(conn)这一句,我这个一开始的时候就查询很慢,跟蜗牛似的,这还是自己在本地测试,以后要是用起来数据多了不就容易超时吗,要是再换别的连接方式的话可能不满足长连接的要求了啊
一年多之前 回复
共13条数据 1 尾页
Csdn user default icon
上传中...
上传图片
插入图片
准确详细的回答,更有利于被提问者采纳,从而获得C币。复制、灌水、广告等回答会被删除,是时候展现真正的技术了!