最近在检查一方法时发现程序执行SQL查询时很是慢,但使用PLSQL DEV工具执行查询语句又很快。java
看如下代码:sql
public boolean isExistSonoByStoreCode(String storeCode, String soNo, String billId) { StringBuffer sql = new StringBuffer(); sql.append("select bill_id, so_no from store_in_head where store_code = ? and UPPER(so_no) = UPPER(?) "); DBUtil dbu = null; PreparedStatement preStmt = null; ResultSet rs = null; boolean isExistSono = false; try { dbu = new DBUtil(); preStmt = dbu.getConnection().prepareStatement(sql.toString()); preStmt.setString(1, storeCode); preStmt.setString(2, soNo); rs = preStmt.executeQuery(); while (rs.next()) { String oldBillId = rs.getString("bill_id"); if (!oldBillId.equalsIgnoreCase(billId)) { isExistSono = true; } } } catch (Exception e) { log.error(e.getMessage(),e); } finally { try { rs.close(); } catch (Exception e) { ; } try { preStmt.close(); } catch (Exception e) { ; } closeDButil(dbu); } return isExistSono; }
经DEBUG发现速度慢确实发生在查询时,即执行rs = preStmt.executeQuery();时app
一开始怀疑是语句使用UPER函数致使的,可是把语句修改成不使用函数也同样慢。。怀疑是使用了PreparedStatement 参数须要动态绑定问题,因而将PreparedStatement 改成Statement ,执行却很快,修改后的代码以下:函数
public boolean isExistSonoByStoreCode(String storeCode, String soNo, String billId) { StringBuffer sql = new StringBuffer(); sql.append("select bill_id, so_no from store_in_head where store_code = '" + storeCode + "' and UPPER(so_no) = UPPER('"+ soNo +"') "); DBUtil dbu = null; Statement preStmt = null; ResultSet rs = null; boolean isExistSono = false; try { dbu = new DBUtil(); preStmt = dbu.getConnection().createStatement(); rs = preStmt.executeQuery(sql.toString()); // preStmt.setString(1, storeCode); // preStmt.setString(2, soNo); // rs = preStmt.executeQuery(); while (rs.next()) { String oldBillId = rs.getString("bill_id"); if (!oldBillId.equalsIgnoreCase(billId)) { isExistSono = true; } } } catch (Exception e) { log.error(e.getMessage(),e); } finally { try { rs.close(); } catch (Exception e) { ; } try { preStmt.close(); } catch (Exception e) { ; } closeDButil(dbu); } return isExistSono; }
字段类型:STORE_CODE CHAR(3),SO_NO VARCHAR2(100)工具
相似问题:http://bbs.csdn.net/topics/320181076.net