Fetching Data From ResultSet

Hi,

I was facing one issue regarding traversing the ResultSet Object.

If the resulset has 10000 rows and if the application needs to traverse these many rows then performance of application degrades.

Are there any tips to improve performance while traversing ResultSet Object.

Thanks in advance.

go through page by page iterator pattern. It will definitely degrade the performance if you are facing 10000 rows.

-Jignesh

go through page by page iterator pattern. It will definitely degrade the performance if you are facing 10000 rows.
-Jignesh
===================================================================
sorry but I didn't get you,can you please explain with an example.
It would be great!!!
Thanks for quick reply.

Hi Raj,

I want to understand one thing that u want to implement the pagination for viewing the 10000 records or at a time u want to show the results.

If ur idea was pagination then the implementation of the same is depending wholly on ur application logic ie how it is fitted in the application.

if the second ie u want to show 10000 records at a time to the user then the time factor would be there but that can be reduced using the code optimization i mean by simplying the display logic.

I am not clear about ur requirement ... if u give clarity i would certainly help u out

getting 10000 records from a database and iterating thru them or displaying them, is nothing and its not a major performance problem. check out your code this will be done in a split of a second.
If you are concerned on the time it is taking to load 10000 records then there should be some other problem. Specify your problem correctly....

I hope it all depends on the size of each record in the result set.
i would advise
1) Find out the size of the 10,000 record result set, you want to display. Set the result set to null and run a system.gc(). Take a memory usage stat before and after gc.
2) If it is too huge, then you have to resort to paging.
3) But even if you display 5 records per page, get the optimum no. of records from database at each fetch. This will reduce the no. of calls made to the database.

sorry for delayed reply, actually 10000 was just an example.
there can be are more than 200000 records and I want to solve the performance problem here.
I am planning to use threads here, but still I would be happy if we can share our experience on improving performance.

Thanks

What are you going to do with the data? Display it? Process it in some other manner?

Yes threading would be a good solution if CPU is powerful. So you need to check the capcity of CPU before going for threading.

It seems you requirement is not for display purpose in that case you can write a stored procedure fetch the necessary data process it. Keep the the track number of rows processed and start processsing again from the last row.

One thing that may help is using ResultSet.getString().intern() instead of ResultSet.getString() (assuming your result set has strings). But you should benchmark to be sure.