Solved

Handling large records in a Java EE application

Posted on 2014-11-24
11
216 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 
LVL 35

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 27

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

How our DevOps Teams Maximize Uptime

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

Question has a verified solution.

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

Introduction This article is the last of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers our test design approach and then goes through a simple test case example, how …
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
This video teaches viewers about errors in exception handling.
Use Wufoo, an online form creation tool, to make powerful forms. Learn how to choose which pages of your form are visible to your users based on their inputs. The page rules feature provides you with an opportunity to create if:then statements for y…

733 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