aspose file tools*
The moose likes JDBC and the fly likes performance issue in iterating through  large list of result set Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "performance issue in iterating through  large list of result set" Watch "performance issue in iterating through  large list of result set" New topic
Author

performance issue in iterating through large list of result set

Priyanka Sumanam
Greenhorn

Joined: Aug 17, 2010
Posts: 25
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

Joined: May 16, 2011
Posts: 62

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.


Cheers, Joy [SCJP 1.4, SCBCD 5.0]
get high on alcohol, algorithm or both
Priyanka Sumanam
Greenhorn

Joined: Aug 17, 2010
Posts: 25
Hi Joy,
Here is the full code. Result list is JDBC ResultSet



Thanks
Madhan Sundararajan Devaki
Ranch Hand

Joined: Mar 18, 2011
Posts: 312

@Priyanka

Your code is incomplete. Also, please enclose your code snippet within tags.


S.D. MADHAN
Not many get the right opportunity !
Ashwin Sridhar
Ranch Hand

Joined: Jul 09, 2011
Posts: 272

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


Ashwin Sridhar
SCJP | SCWCD | OCA
joy b chakravarty
Ranch Hand

Joined: May 16, 2011
Posts: 62

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

Joined: Feb 22, 2011
Posts: 75
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

Joined: May 16, 2011
Posts: 62

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

Joined: Jul 09, 2011
Posts: 272

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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: performance issue in iterating through large list of result set
 
Similar Threads
printing on an applet
Retriving Data in a tabular form
performancr tunning in traversing through a resulset (to form a table model)
Initializing JTable
show database on applet