英文:
Java Spring refcursor queries slows down
问题
我正在尝试调用带有 refcursor 参数的 PostgreSQL 函数。刚开始时，它效果很好，但随着时间的推移，查询速度下降到每个请求约 10 秒钟。有人有什么想法吗？提前感谢您的帮助。以下是我调用该函数的方式：
@Transactional
public List<Table> getTable(TableFilter entity) {
List<Table> tableList = new ArrayList<>();
String cursor = "cursor_" + UUID.randomUUID().toString();
String function = "{? = call mySchema.gettable('" + cursor + "',?)}";
try (Connection conn = jdbcTemplate.getDataSource().getConnection();
CallableStatement cs = conn.prepareCall(function);
) {
ObjectMapper objectMapper = new ObjectMapper();
PGobject jsonbObj = new PGobject();
jsonbObj.setType("json");
jsonbObj.setValue(objectMapper.writeValueAsString(entity));
conn.setAutoCommit(false);
cs.registerOutParameter(1, Types.REF_CURSOR);
cs.setObject(2, jsonbObj);
cs.execute();
ResultSet resultSet = (ResultSet) cs.getObject(1);
while (resultSet.next()) {
tableList.add(new Table(
resultSet.getInt("id"),
resultSet.getString("number")
));
}
conn.commit();
} catch (SQLException e) {
log.error("SQL State: {}", e.getSQLState(), e);
} catch (Exception e) {
log.error("Error while fetching getTable data.", e);
}
return tableList;
}
以及声明的函数：
mySchema.gettable(INOUT refcur refcursor, inJson json)
英文:
I'm trying to call postgresql functions working with refcursor parameter. For the first time it works great, but over time, the query speed drops to about 10 seconds per request.
Does anyone have any ideas? Thanks in advance for your help. Here is the way I'm calling the funcion:
@Transactional
public List<Table> getTable(TableFilter entity) {
List<Table> tableList = new ArrayList<>();
String cursor = "cursor_" + UUID.randomUUID().toString();
String function = "{? = call mySchema.gettable('" + cursor + "',?)}";
try (Connection conn = jdbcTemplate.getDataSource().getConnection();
CallableStatement cs = conn.prepareCall(function);
) {
ObjectMapper objectMapper = new ObjectMapper();
PGobject jsonbObj = new PGobject();
jsonbObj.setType("json");
jsonbObj.setValue(objectMapper.writeValueAsString(entity));
conn.setAutoCommit(false);
cs.registerOutParameter(1, Types.REF_CURSOR);
cs.setObject(2, jsonbObj);
cs.execute();
ResultSet resultSet = (ResultSet) cs.getObject(1);
while (resultSet.next()) {
tableList.add(new Table(
resultSet.getInt("id"),
resultSet.getString("number")
));
}
conn.commit();
} catch (SQLException e) {
log.error("SQL State: {}", e.getSQLState(), e);
} catch (Exception e) {
log.error("Error while fetching getTable data.", e);
}
return tableList;
}
And the function declared:
mySchema.gettable(INOUT refcur refcursor, inJson json)
专注分享java语言的经验与见解，让所有开发者获益！
评论