最近在检查一方法时发现程序执行SQL查询时非常慢,但使用PLSQL DEV工具执行查询语句又很快。
看以下代码:
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();时
一开始怀疑是语句使用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