JSP pages, performance comparison of result set pagination and SQL (top) paging

JSP page two paging modes:

The first: Result set paging, the main code is shown below:

Java code

2. ResultSet rs=stmt.executequery (SQL);
4. ResultSetMetaData Md=rs.getmetadata ();
8. Rs.beforefirst ();
10. Rs.absolute (2000);
12. }
14. int ii=0;
16. while (Rs.next () &&ii<=)
18. {
20. Rs.getstring ("xxx");
22. ii++;
24. }

Second type: SQL statement paging

Sql= "Selec top id,name from table where ID not in (select top with ID from table)";



The two kinds of paging test, in the case of 4 million data, the performance is almost the same, the difference is not small. The speed is about 30 seconds.

Test data (20 per page):
SQL paging

Page time
2--test2 Starttime=mon Oct 16:24:35 CST 2009
2--test2 Endtime=mon Oct 16:25:09 CST 2009 34

200--test2 Starttime=mon Oct 16:25:48 CST 2009
200--test2 Endtime=mon Oct 16:26:21 CST 2009 33

20000--test2 Starttime=mon Oct 16:27:04 CST 2009
20000--test2 Endtime=mon Oct 16:27:39 CST 2009 35

210000--test2 Starttime=mon Oct 16:28:22 CST 2009
210000--test2 Endtime=mon Oct 16:29:58 CST 2009 36


100000--test2 Starttime=mon Oct 16:30:30 CST 2009
100000--test2 endtime= Mon Oct 16:31:10 CST 2009 40


----result set pagination

2--11start time Mon Oct 16:33:37 CST 2009

2---55end time Mon Oct 16:34:12 CST 2009 35


200--11start time Mon Oct 16:34:59 CST 2009

---55end time Mon Oct 16:35:32 CST 2009 33


20000--11start time Mon Oct 16:36:26 CST 2009

20000---55end time Mon Oct 16:36:59 CST 2009 33


210000--11start time Mon Oct 16:38:00 CST 2009

210000---55end time Mon Oct 16:38:33 CST 2009 33


100000--11start time Mon Oct 16:39:10 CST 2009

100000---55end time Mon Oct 16:39:43 CST 2009 33

JSP pages, performance comparison of result set pagination and SQL (top) paging