Poor performance using ResultSet.TYPE_SCROLL_INSENSITIVE
Posted by:
Date: September 22, 2011 12:25PM

Hi All,

I use to write my queries using ResultSet.TYPE_SCROLL_INSENSITIVE statements.
The reason is that it's easy to paginate the results and retrieve query length without retrieving all rows neither running two queries.
Also this method helps me in every database (or it was what i thank).

Here is an example of what I use to do:

----------------------------------------------------------
int start = 0; // start will be the first row to retrieve
int limit = 20; // limit is page size

String sql = "select * from table";
PreparedStatement stmt = con.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
stmt.setFetchSize(limit);

rs = stmt.executeQuery();
if (rs.last()) {
// I can move to the last row and get query length
queryLength = rs.getRow();
rs.beforeFirst();
}
if (start != 0) {
// I can jump to whatever position i need without having to read all positions in the recordset
rs.absolute(start);
}

int i = 0;
// I can get only limit (pagesize) or go to the end of results
while (rs.next() && i++ < limit) {
// Here I initialize Transfer or Value Objects (the list of....)
}
----------------------------------------------------------

This peace of code performs the best on oracle, sqlserver and DB2 and is database independent (because I'm using jdbc standard methods).

The problem I have is that MySQL JConnector don't use fetchsize, instead it retrieves all rows allways
(unless you use FORWARD_ONLY, CONCUR_READ_ONLY and FetchSize(Integer.MIN_VALUE and i can't use them to achieve what I want)).
See ResultSet information at http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-implementation-notes.html.

then, there is any chance to get the submitted code to behave optimally with MySQL???
Any ideas (or I will have to change all my DAOs for MySQL using limit clause for pagination and count(*) for query length....)

thanks in advance,

Options: ReplyQuote


Subject
Written By
Posted
Poor performance using ResultSet.TYPE_SCROLL_INSENSITIVE
September 22, 2011 12:25PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.