主要实现的功能是事先通过条件筛选一部分id出来,在通过遍历这些id找到相应的Bfinalstatement,存为对象,放入list,但是发现数据量才达到1000时(1000个Bfinalstatement),给id设置了index,但需要的总查询时间都还要500~600ms了,效率太低了,具体关键代码如下,希望得到帮助,谢谢。(采用了dbcp连接池) @Override public BFinalStatement getFinalStatementFromId(String id) throws NamingException, SQLException { // TODO 得到乙方结算信息 Connection con = null; PreparedStatement pr = null; ResultSet rs = null; BFinalStatement fstate = null; sql = "select BConSelfID, PaidAmount, UnpaidAmount, InvoiceTotal, ShouldPay " + "from bfinalstatement where BConSelfID=?"; con = getConnection(); pr = con.prepareStatement(sql); pr.setString(1, id); rs = pr.executeQuery(); while (rs.next()) { fstate = new BFinalStatement(rs.getInt("PaidAmount"),rs.getString("BConSelfID")); fstate.setInvoiceTotal(rs.getInt("InvoiceTotal")); fstate.setShouldpay(rs.getInt("ShouldPay")); fstate.setUnpaidAmount(rs.getInt("UnpaidAmount")); } // 释放连接 close(con, rs, pr); return fstate; }
2 回答

肥皂起泡泡
TA贡献1829条经验 获得超6个赞
单个查当然慢了,你这个sql就算只要1毫秒,执行1000次也1秒了。用where BConSelfID in ()来批量处理会快些,但注意in里面不要太多个ID,一次100一共取10次差不多。
再慢的话就要善用缓存了

守候你守候我
TA贡献1802条经验 获得超10个赞
-- 修改 BConSelfID = 'xxx'
-- 执行以下SQL,将结果贴出来:
explain select BConSelfID, PaidAmount, UnpaidAmount, InvoiceTotal, ShouldPay
from bfinalstatement where BConSelfID = 'xxx'
添加回答
举报
0/150
提交
取消