Thank you for your reference. I have read it and my understanding is still
the same. Sorry for being hard headed. I made a bench mark between
pagination using ResultSet data with cursors and pagination using limit and
offset. Here's my code for both test:
/**
* Test for pagination via ResultSet cursor
**/
public void testResultSet() throws Exception {
Class.forName("org.postgresql.Driver");
String url = "jdbc:postgresql://127.0.0.1/ofbiz";
Connection con = DriverManager.getConnection(url, "postgres",
"postgres");
Statement ps = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);
int size = 100;
int index = 217;
int number = size * index;
long startTime = System.currentTimeMillis();
ResultSet rs = ps.executeQuery("SELECT * FROM product_facility");
if (!rs.next()) {
System.err.println("Nothing to return");
return;
}
if (!rs.absolute(number)) {
System.err.println("Failed to set cursor");
return;
}
int counter = 0;
do {
store(rs);
counter ++;
} while (size > counter && rs.next());
System.out.println("ENDED: " + (System.currentTimeMillis() -
startTime));
}
/**
* Test for pagination via LIMIT and OFFSET
**/
public void testLimitOffset() throws Exception {
Class.forName("org.postgresql.Driver");
String url = "jdbc:postgresql://127.0.0.1/ofbiz";
Connection con = DriverManager.getConnection(url, "postgres",
"postgres");
Statement ps = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);
int size = 100;
int index = 217;
int number = size * index;
long startTime = System.currentTimeMillis();
ResultSet rs = ps.executeQuery("SELECT * FROM product_facility LIMIT " +
size + " OFFSET " + number);
if (!rs.next()) {
System.err.println("Nothing to return");
return;
}
do {
store(rs);
} while (rs.next());
System.out.println("ENDED: " + (System.currentTimeMillis() -
startTime));
}
Having a row size of 21897, here are my results (in ms):
testResultSet(): 875, 922, 875, 875, 859 (Ave: 881.2 ms)
testLimitOffset(): 94, 78, 62, 78, 78 (Ave: 78 ms)
These figures are the reason why I wasn't convinced with your answer. But
again, please do comment on this. It is important for me to understand why
it was designed like this when the use of limit and offset were one of the
basics in sql pagination.
~ ian
On Tue, Sep 9, 2008 at 7:40 PM, David E Jones <[EMAIL PROTECTED]>wrote:
>
> Your assertions are incorrect, that's not how it works.
>
> I would recommend reading up a bit more on JDBC and how ResultSet data is
> handled along with cursors in the database.
>
> -David
