Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Paging retrieve data question

 
michael yue
Ranch Hand
Posts: 204
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am trying to do a paging of some sort and below is my code when retrieving the data.

Statement stmt = cnt.createStatement();
ResultSet rst = stmt.executeQuery(strsql);

int countpaging=0;
while (rst.next()){
countpaging++;
if((countpaging >= start+1) && (countpaging <= start+100)){

var1=rst.getString("col1");
var2=rst.getString("col2");
...
}

the start variable is passed in to indicate where to start displaying the results and when to end.
I just want to know whether this technique will affect the performance of the query since the resultset is being loaded all the results. What is the limit a resultset can hold in terms the number of data?
Thanks.
 
Bear Bibeault
Author and ninkuma
Marshal
Pie
Posts: 65340
97
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Moving to the JDBC forum.
 
Stefan Wagner
Ranch Hand
Posts: 1923
Linux Postgres Database Scala
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
since the ResultSet-method repeatedly use int, (int i = rs.getRow (); rs.absolute (int i) , I would expect MAX_INT to be the limit.
 
michael yue
Ranch Hand
Posts: 204
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If there are a lot of data to get like for example 10000 or more will the resultset slow down and consume memory? Is there a way to traverse a resultset using the for loop?
 
Adeel Ansari
Ranch Hand
Posts: 2874
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
i would suggest, dont load all the data while doing paging stuff. and it is very unlikely to have full table scans when having 10,000 or more records. indices doesn't help in full table scans and then that results in lack of performance.
 
michael yue
Ranch Hand
Posts: 204
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Is it possible to use for loop instead of while loop for this line of code?
while(rst.next)
 
Senthil B Kumar
Ranch Hand
Posts: 160
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
it is possible to do it by finding the size of the resultset and looping till that, but using a while statement results in a more understandable code.
 
Adeel Ansari
Ranch Hand
Posts: 2874
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by lin park:
Is it possible to use for loop instead of while loop for this line of code?
while(rst.next)


do you think you will overcome the performance overhead?
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic