cancel
Showing results for 
Search instead for 
Did you mean: 

Need help in designing fetching of huge amount of records from a DB

Former Member
0 Kudos

Hello,

I am having a starard application which fetches data from MSSQL Server using this standard code:

CallableStatement cs;
Connection connection;
...
cs = connection.prepareCall("{call " + PROCREQUEST_SP + "(?,?,?)}");
cs.execute();
ResultSet rs = cs.getResultSet();
while (rs.next())
...

Most of the queries the users run are no more than 10,000 records and the code works OK like this.

But, the Database contains more the 7 million records and I would like to enable the user to see all these records if he wants to.

If I enable it at the current code I finally receive java.lang.OutOfMemory Exception. How can I improve my code in order to do this kind of task?

Accepted Solutions (0)

Answers (3)

Answers (3)

Former Member
0 Kudos

Hello Roy,

Usually for fetching huge data from DB, which the user may scroll through is done in chunks (groups of few hunderds or thousands). For showing it on the UI side we use pagination i.e. user sees only 100 or 1000 records in one screen and then clicks next or page no. to get the other 1000 records.

Hope this helps..

Cheers,

Rahul

Former Member
0 Kudos

Hey Rahul,

So what happen every time a user clicks next or page no., it connects to the DB and query the DB all over again?

Roy

Former Member
0 Kudos

Hello Roy,

Yes the DB is connected again and only the data chunk asked for is called. eg. if user is on page 2, containing recoreds 1000-2000, and clicks on 3 only records from 2000-3000 should be fetched from the DB. This logic should be taken care by your query.

This saves a lot of Memory,so you wouldn't face MemoryException.

Regards,

Rahul

Former Member
0 Kudos

Thank you all for the advices...

Former Member
0 Kudos

Hello,

try with scrollable ResultSet - ResultSet.TYPE_SCROLL_INSENSITIVE.

It's able to scroll on results.

Kind regards,

Marcin

Former Member
0 Kudos

Hi Roy,

does the user really need 7 million records all at once? Or can you implement some kind of background job that loads the data the user request for (e.g. when the user is scolling in a list, the job gets the next n elements)? What are your settings for heap-size?

bye

Torsten

Former Member
0 Kudos

Hey Torsten,

He doesn't need it all at once but he does need to scroll it. A bakground job could be a solution. Regarding the heap-size: Good question, do you know where at the portal I can see it's JVM heap-size?

Former Member
0 Kudos

Hi Roy,

sorry I don't know where you can find the heap size in th portal, but

Runtime.getRuntime().maxMemory()

can show you the max memory your runtime will use.

hth

Torsten