• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

performance issue in iterating through large list of result set

 
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Ranch Hand
Posts: 62
Hibernate Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Joy,
Here is the full code. Result list is JDBC ResultSet



Thanks
 
Ranch Hand
Posts: 312
MS IE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
@Priyanka

Your code is incomplete. Also, please enclose your code snippet within tags.
 
Ranch Hand
Posts: 277
Oracle Spring Flex
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Ranch Hand
Posts: 75
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Oracle Spring Flex
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi,

This link might be useful

Looping through large result set running very slow
 
Marshal
Posts: 28193
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 6
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Narayanan Madaswamy wrote:Hi,

This link might be useful

Looping through large result set running very slow

 
reply
    Bookmark Topic Watch Topic
  • New Topic