• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

performance issue in iterating through large list of result set

 
Priyanka Sumanam
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have the below code which takes more than a minute to iterate through 5000 records...which is not acceptable performance

while( rs.next() )
{
Object[] resultFields=new Object[colCount];

for(int i=1;i<=colCount;i++)
resultFields[i-1]=rs.getObject(i);
resultList.add(resultFields);

}

Could any one suggest something.
 
joy b chakravarty
Ranch Hand
Posts: 62
Hibernate Java Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It would be helpful if you could larger/complete code snippet.. what is resultList?
If its a List (deduced from the name), then looks like you are performing this action
Lets say colCount is 3
list.add([]{r1.c1, null, null})
list.add([]{r1.c1, r1.c2, null})
list.add([]{r1.c1, r1.c2, r1.c3})
list.add([]{r2.c1, null, null})
list.add([]{r2.c1, r2.c2, null})
list.add([]{r2.c1, r2.c2, r2.c3})

which really doesn't make sense to me.
 
Priyanka Sumanam
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Joy,
Here is the full code. Result list is JDBC ResultSet



Thanks
 
Madhan Sundararajan Devaki
Ranch Hand
Posts: 312
Java MS IE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
@Priyanka

Your code is incomplete. Also, please enclose your code snippet within tags.
 
Ashwin Sridhar
Ranch Hand
Posts: 277
Flex Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Please post complete code. Also from your initial post, i could find that you are creating Object array everytime in the while loop. you could create the Object array once outside the loop and keeping using the same in the loop.

something like below


This may or may not improve your performance. but a suggestion
 
joy b chakravarty
Ranch Hand
Posts: 62
Hibernate Java Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
i could find that you are creating Object array everytime in the while loop. you could create the Object array once outside the loop and keeping using the same in the loop.
Object[] resultFields=null;
while{
resultFields=new Object[colCount];


The object creation still happens inside the loop, you are just declaring the variable outside, broadening its scope.
 
Sudheer Bhat
Ranch Hand
Posts: 75
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You need to find out where the performance issue is. You say that you process around 5000 records. There is very unlikely chance that the moment you say statement.executeQuery, all the records are returned to middle tier from DB.
Check the following.

1. How much time it takes for the PS.executeQuery(); statement to run?
2. Check to see if your iteration over the resultset takes time.

First find out where the performance problem is.
The very first step in performance tuning should be to find out where the problem is, then collect relevant data to support that and most importantly what is your expectation. If you expect to fetch 5000 records from DB, do some processing on that in a 1/1000th of a second, then its probably not possible!!
 
joy b chakravarty
Ranch Hand
Posts: 62
Hibernate Java Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
as I had mentioned earlier, your code is doing something like this

list.add([]{r1.c1, null, null})
list.add([]{r1.c1, r1.c2, null})
list.add([]{r1.c1, r1.c2, r1.c3})
list.add([]{r2.c1, null, null})
list.add([]{r2.c1, r2.c2, null})
list.add([]{r2.c1, r2.c2, r2.c3})


I think what you probably wanted is
list.add([]{r1.c1, r1.c2, r1.c3})
list.add([]{r2.c1, r2.c2, r2.c3})



 
Ashwin Sridhar
Ranch Hand
Posts: 277
Flex Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
joy b chakravarty wrote:
i could find that you are creating Object array everytime in the while loop. you could create the Object array once outside the loop and keeping using the same in the loop.
Object[] resultFields=null;
while{
resultFields=new Object[colCount];


The object creation still happens inside the loop, you are just declaring the variable outside, broadening its scope.


So just single variable is created and object is referenced to that variable.

instead of creating 5000 variables in the memory.
 
Narayanan Madaswamy
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

This link might be useful

Looping through large result set running very slow
 
Paul Clapham
Sheriff
Posts: 20990
31
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ashwin Sridhar wrote:So just single variable is created and object is referenced to that variable.

instead of creating 5000 variables in the memory.


Nope. There's only one variable in either case, but there are 5000 objects which are assigned to that variable at various times. So a strategy which attempts to minimize the number of "variable creations" is a complete waste.
 
Narayanan Madaswamy
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Narayanan Madaswamy wrote:Hi,

This link might be useful

Looping through large result set running very slow
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic