JDBC - getJDBCtemplate with ResultExtractor is slow

[sam101]

Hi,
We are migrating our app to Java. Our current query runs fast in our legacy app.
Now in Using Spring we are running the query like below.

getJdbcTemplate().query(sqlQuery, rsExtractor);

in RsExtractor we are doing while(rs.next)-- we are setting results to Value Object.

This query returns about 5000 rows.... It is very slow compare to legacy app which return in 1 sec. Using Spring JBDCTemplate it takes about 5 sec.

Any way to tune this or use different api in Spring that is faster. We have to use JDBC.

Thanks

[sam101]

Can someone please help out here??
Thanks

[Marten Deinum]

What is the difference? Is your legacy app also a java app? If not simply executing and returning the results of a query is in general fast, however creation/generation of object instances is slow (especially if you have quite some rows).

Post some code and configuration (old and new) and how you are testing things.

[sam101]

Legacy Code is in Perl....


In java right we have this simple link. When we click it gets all 5000 rows and displays as a list in drop down.

Below is our jdbc and resultextractor code.

1)

String sql_query = "select name from users";
displayList = listDAO.queryForObjectName( sql_query, new UserResultSetExtractor());

2) Than in the queryForObjectFromName method we call our jdbctemplate.

return getJdbcTemplate().query(sqlQuery, rsExtractor);

3) Our UserResultSetExtractor class extends extends AbstractResultSetExtractor and code it contains.

protected Object processResultSet(ResultSet rs) throws SQLException,DataAccessException {

List userNameList = new ArrayList();

if(rs.isBeforeFirst() && !rs.isAfterLast()) {
UserVO userList = null;
logger.info("Fetching records");
while(rs.next()) {
userList = new UserVO();
userList.setUserId(rs.getString("USERNAME"));
userNameList .add(userList);
}
}
return userNameList ;
}


4) Than we just display the list using struts2 ui tags.

Thanks

[Marten Deinum]

Please use [ code][/code ] tags when posting code.

Legacy Code is in Perl....

So basically is is comparing apples and oranges (that is IMHO ofcourse).

For starters I would use a RowMapper instead of a ResultSetExtractor that way spring does the looping. Also is the looping slow, the query execution? Next to that are you using a connection pool or are you using a DriverManagerDataSource (which creates a new connection to the database each time it needs one!).

Also is it really necessary to convert it to a list of UserVO? If you are only getting/using the username, why not simply return everything as a string?