Solved

Handling large records in a Java EE application

Posted on 2014-11-24
11
206 Views
Last Modified: 2014-12-06
Hi,

I would like to know which  technique  and technology one should use in order to retrieve a millions of record from the database table,  whether pagination is the better approach or any other approach can help me ?  

Thank you
0
Comment
Question by:roy_sanu
11 Comments
 
LVL 20

Expert Comment

by:Amitkumar Panchal
ID: 40464061
I would suggest that pagination is a better approach.

Think about it, you are retrieving millions of record and you are converting it into a java object (or say ResultSet), how much memory will it need to store the entire content ? Somewhere, i done know when, it will definitely end-up with OutOfMemoryError.

But, lets wait for some time. I am sure the other experts will have better solution than above.
0
 

Author Comment

by:roy_sanu
ID: 40466247
Thanks Amit for good info, I will waits other expert comments  is there any way i can think up on
spring jdbctemplate which hold this object efficently or on database side i.e stored procedure or cursor may help me on that. Let us know other experts....
0
 
LVL 21

Expert Comment

by:MogalManic
ID: 40467302
It all depends on how you envision the users will view or access the data.  If they are only going to be viewing a small subset of the records, then pagination would be the best solution.

If the user is retrieving the whole record set and viewing/accessing the results (for example the record set constitutes a file), then some other sort of pattern.  For example you could use a datareader and buffer the results to the file (or Response)
0
 
LVL 34

Expert Comment

by:Gary Patterson
ID: 40467342
You might get more answers if you explained your requirement in more detail.  There are a variety of ways to process large data sets, and the best technique often depends on what you plan to do with the data.
0
 
LVL 26

Expert Comment

by:dpearson
ID: 40467394
The main thing I'd suggest is to not do:

a) Read all data from the database into memory
b) Process the data

The simplest alternative is to read one record at a time, process it and then proceed to the next record.

If you're using JDBC, you can use the db-utils library from Apache commons:
http://commons.apache.org/proper/commons-dbutils/examples.html

which shows how to implement a "ResultSetHandler" which is called for each row in the ResultSet.

You can implement your processing there and avoid sending the entire result set back to the client app as a single chunk of data which you then process.

Another option is to do the processing inside the database itself (with a stored procedure).  It really depends on what the "processing" is that you need to do.

Hope that helps,

Doug
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:roy_sanu
ID: 40468294
What I had is the customer and the product table which has I million records transaction as a master detail relation , what I need to display on the ui layer to display 100 records on each page.
0
 
LVL 26

Expert Comment

by:mrcoffee365
ID: 40474103
You didn't get more answers because the first response was correct.  It's a waste of processing time and space to get a million records for each page request.  The most common method is to get 100 records at a time with the sql query.
0
 
LVL 21

Expert Comment

by:MogalManic
ID: 40474327
So yes, the 1st response is correct.  You should use a datareader to grab the 100 records based on the user's current page.

The algorithm is something like this:
1.  (optional) - Run a "count" query to get the total # of records (required if you want to compute the number of pages)
2. Run the query and return the results in a ResultSet:
ResultSet rs = st.executeQuery("SELECT * FROM mytable");

Open in new window

3. Skip the required number of pages by calling rs.next() until you get to the required row
4. Process the next 100 rows
5. Close the ResultSet object

The above algorithm will work with most databases.  For a database specific way of doing the same thing, you can push the logic into the query itself.  For example in MySQL you can do something like this:
SELECT * FROM tbl LIMIT 5,10;  # Retrieve rows 6-15

Open in new window

which means starting at row 6, retrieve the next 10 rows.
0
 

Author Comment

by:roy_sanu
ID: 40480264
Thanks for the info, what  your  thought on using stored procedure and calling using callable statement,
will that be faster retrieval, well i will be using  jsp as a front end...
0
 
LVL 21

Accepted Solution

by:
MogalManic earned 500 total points
ID: 40480691
Yes that is the best way to do this type of operation.  Any Database specific language can be used inside the procedure to achieve the goal.  The java code just needs to do something like this:
String sql = "{call getPagedData(?, ?)}";
stmt = conn.prepareCall(sql);
stmt.setInt(1, PageNo); 
stmt.setInt(1, RecordsPerPage);
ResultSet resultSet = stmt.executeQuery();
/*process results and cleanup*/

Open in new window

The stored procedure computes the row offset and records to skip by the PageNo and RecordsPerPage parameters.  The java code does not need to be concerned with the SQL and pageno math.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction This article is the second of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers the basic installation and configuration of the test automation tools used by…
It’s a strangely common occurrence that when you send someone their login details for a system, they can’t get in. This article will help you understand why it happens, and what you can do about it.
This video teaches viewers about errors in exception handling.
Learn how to set-up PayPal payment integration in your Wufoo form. Allow your users to remit payment through PayPal upon completion of your online form. This is helpful for collecting membership payments, customer payments, donations, and more.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now