What are the database and JDBC driver versions you are working with?
Also, the JDBC code you included does a few things differently than
what the Entity Engine does. Have you done similar tests using the
Entity Engine instead of plain JDBC? Also, why not set the cursor size
and what not (as is done, optional of course, in the entityengine.xml
file)?
-David
On Sep 9, 2008, at 7:08 AM, ian tabangay wrote:
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
