Hi experts,
I am using java to call sqlserver SP and store the return data in ResultSet in JAVA, and then loop to fetch the data from resultset, but I encounter a performance that it always pause abount 1 minnute after fetching about 40 records(please check following code marked in red), and then resume to process next record, any suggestion would be appreciate.
following is my code:
Statement stmt = null;
CallableStatement cstmt = null;
PreparedStatement pst = null;
ResultSet rs = null;
int batchSize = 1000;
ResultSet rs1 = null;
ResultSet rs2 = null;
public List<String[]> getIMSDataByStoreProcedure(Database database, String[] parameters, Map<String,String> config, StoredProcedures ims_sp) {
List<String[]> results = new ArrayList<String[]>();
Calendar cal = Calendar.getInstance();
//set result set
try {
openConn(database);
cstmt = conn.prepareCall(config.get("stmt"));
logger.info("----------------- [ parameter start ] ----------------------------");
logger.info("stmt => " + cstmt.toString());
logger.info("database => " + database.toString());
logger.info("duration => " + Arrays.toString(parameters));
logger.info("----------------- [ parameter end ] ----------------------------");
if (parameters != null && parameters.length > 0) {
for (int i = 0; i < parameters.length; i++) {
cstmt.setString(i+1, parameters[i]);
}
}
rs2 = cstmt.executeQuery();
String[] column_name = config.get("columns").split(",");
while(rs2.next()) {
logger.info("jerry row start:".concat(Constants.sdf_1.format(new java.util.Date())));
String[] record = new String[column_name.length];
for (int i = 0; i < record.length; i++) {
record[i] = rs2.getString(column_name[i]);
}
results.add(record);
logger.info("jerry row end-------------------:".concat(Constants.sdf_1.format(new java.util.Date())));
}
rs2.close();
cstmt.close();
} catch (Exception e) {
e.printStackTrace();
logger.fatal(">>>>> "+e.getMessage());
} finally {
closeConn();
}
return results;
}