Solved

Handling large records in a Java EE application

Posted on 2014-11-24
11
211 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 21

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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
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
 

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 27

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
eclipse argument 14 60
rhino JavaScript import, load 25 82
couple of eclipse 5 35
Configure a Bean in an XML file 4 31
Thoughout my experience working on eCommerce web applications I have seen applications succumbing to increased user demand and throughput. With increased loads the response times started to spike, which leads to user frustration and lost sales. I ha…
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 tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

813 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

9 Experts available now in Live!

Get 1:1 Help Now