ResultSet.next()有时在结果集非常小(0或1命中)时花费大量时间

Question

Oracle 11g返回的Resultset在Java中使用rs.next()进行处理。有时(大约千分之一)，这个陈述需要很长时间才能得出"错误"的结论。有时甚至是几百秒。这只发生在结果集包含0或1行时。但是，具有0或1行的结果集在几毫秒内处理了1000个结果集中的999个。

rs.next()有时花这么多时间的原因是什么?

尽管这个过程花费了很多时间，但在几百秒之后，Java代码继续运行，没有问题。

private List<SomeInfo> getCases(long medewerkerId, long ogeId, OurFilter filter,
ZTCZoekFilter zoekFilter, InterfaceZTC.Sortering sortering) {
List<SomeInfo> foundInfos = new ArrayList<SomeInfo>();
String query = OurImplHelper.createQuery(medewerkerId, ogeId, filter, zoekFilter);
CallableStatement cs = null;
Connection connection = null;
int rowCount = 0;
try {
DataSource ds = null;
String jndiLookup = "java:/dataDS";
Context initCtx = new InitialContext();
ds = (DataSource) initCtx.lookup(jndiLookup);
connection = ds.getConnection();
cs = connection.prepareCall(query);
cs.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);
cs.execute();
ResultSet rs = (ResultSet) cs.getObject(1);
while (rs.next()) {
rowCount++;
SomeInfo caseInfo = new SomeInfo();
caseInfo.setSomecode((String) rs.getObject(1));
....
foundInfos.add(caseInfo);
}
if (cs != null) {
cs.close();
}
if (connection != null) {
connection.close();
}
} catch (Exception e) {
throw new ZakenmagazijnManagerException(e.getMessage());
}
finally {
}
return foundInfos;
}

Answer 1

您似乎没有关闭东西(连接可能，cs, rs)。而且cs的变量范围从它的prepareCall来看似乎太大了。

使用try-with-resources，如:

try (ResultSet rs = (ResultSet) cs.getObject(1)) {
while (rs.next()) {  
rowCount++;
SomeInfo someInfo = new SomeInfo();
someInfo.setSomecode((rs.getString(1));
foundInfo.add(someInfo);
}
return foundInfo;
} // rs is closed.

---

我的代码风格:

String query = OurImplHelper.createQuery(medewerkerId, ogeId, filter, zoekFilter);
try {
String jndiLookup = "java:/dataDS";
Context initCtx = new InitialContext();
DataSource ds = (DataSource) initCtx.lookup(jndiLookup);
try (Connection connection = ds.getConnection();
CallableStatement cs = connection.prepareCall(query)) {
cs.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);
cs.execute();
try (ResultSet rs = (ResultSet) cs.getObject(1)) {
List<SomeInfo> foundInfos = new ArrayList<>();
int rowCount = 0;
while (rs.next()) {
rowCount++;
SomeInfo caseInfo = new SomeInfo();
caseInfo.setSomecode((String) rs.getObject(1));
....
foundInfos.add(caseInfo);
}
return foundInfos;
}
}
} catch (Exception e) {
throw new ZakenmagazijnManagerException(e.getMessage());
}