?
Solved

Retrieving data with ResultSet object for a large number of records

Posted on 2004-11-30
9
Medium Priority
?
292 Views
Last Modified: 2013-11-24
I have a Java servlet that retrieves data and creates a text file with the retrieved data and then creates a zip file with the text file.  It works OK for a table or view with around 20,000 records but does not work for a table with say 200,000 records.  For the large table it does create the text file and writes a certain number of records but then quits without completing and no zip file.   I assume it cannot handle this many records at once.   It works OK on a server with 1G more of RAM.  

Here is my code for creating the text file:
    try
    {
      Connection cn = getConnection();
    // Prepare the query statement
    String query_str = "SELECT * FROM " + var0 + "." + var1;
    PreparedStatement ps = cn.prepareStatement(query_str);
    // Execute the query
    ResultSet rs = ps.executeQuery();
    ResultSetMetaData rsMeta = rs.getMetaData();
    int colCount = rsMeta.getColumnCount();
    // For each column in the result set print the column name
    curName = rsMeta.getColumnName(1);
    if (colCount == 1) {
        fileout.println(curName);      
    } else {
        fileout.print(curName);
    }
    for (int curCol = 2; curCol <= colCount; curCol++)
    {
      curName = rsMeta.getColumnName(curCol);
      if (curCol == colCount) {
        fileout.println("|"+curName);      
      } else {
        fileout.print("|"+curName);
      }
    }  
    // Loop over the rows in the query result
    while (rs.next())
    {
      if (rs.getString(1) == null) {
      } else {
          fileout.print(rs.getString(1));            
      }
      for (int curCol = 2; curCol <= colCount; curCol++)
      {
        if (rs.getString(curCol) == null) {
          fileout.print("|");      
        } else {
          fileout.print("|"+rs.getString(curCol));
        }
      }
      fileout.println();
    }
    rs.close();
    ps.close();
    cn.close();    
    }

    catch(Exception e)
    {
      e.printStackTrace();
    }
    fileout.close();

Is there a way to return a smaller number of records at a time (something like multiple result sets, not sure how to do this)?  I am not sure actually what the problem is.  I am assuming it has to do with too many records being retreived.  
 
0
Comment
Question by:perrycn
  • 5
  • 4
9 Comments
 
LVL 92

Expert Comment

by:objects
ID: 12712140
are you getting any errors?

try increasing the fetch size to improve performance.
0
 
LVL 92

Expert Comment

by:objects
ID: 12712144
eg.

ps.setFetchSize(1000);
0
 

Author Comment

by:perrycn
ID: 12712199
No I am not getting any errors.   So I would put this statement before ResultSet rs = ps.executeQuery();.  I do not quite understand what this statment does.  It says:  the number of rows that should be fetched from the database when more rows are needed for this ResultSet object.  
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 92

Expert Comment

by:objects
ID: 12712216
So how is it currenlty failing? Are you saying rs.next() returns false even though there are more rows?

the fetch size controls how many rows at a time are retrieved from server.
0
 

Author Comment

by:perrycn
ID: 12712365
OK, I did put the statement, ps.setFetchSize(1000); in the code.  It is not quitting on creating the text file, before it only got to the size of around 7,000KB.  It is now around 35,000KB (file is should be around 56,000KB) and still running.  If I increased 1000 to 10000, would that make it faster?  Of course the browser timeout on the client.  The zip file is posted back to the user.  Not sure about how to keep browser from timing out if it takes along time for text file to be created.
0
 

Author Comment

by:perrycn
ID: 12732872
objects, Thanks for the quick response.  Do you understand why it works when you put in that statment, ps.setFetchSize(1000); but not when it is left out.  Apache which comes with Oracle 9i database release 2 is the webserver.   It just stops going thru the loop with rs.next().   It does not work on the server with 1G of RAM but does work on the server with 2G of RAM and faster processor.  Both servers are set up with the same software.  


0
 
LVL 92

Expert Comment

by:objects
ID: 12732907
sounds like the request could be timing out.
0
 

Author Comment

by:perrycn
ID: 12756567
Object,  Well back to this problem.  I am far from an expert.  What do you mean request timing out?  Is it the Java Servlet, web server, database, or network?   How do I figure out the problem?   I guess I should really close this and give you your points.
0
 
LVL 92

Accepted Solution

by:
objects earned 1500 total points
ID: 12759217
its the web server timing out the request by the sounds
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

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

Introduction This article is the first of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article explains our test automation goals. Then rationale is given for the tools we use to a…
Java functions are among the best things for programmers to work with as Java sites can be very easy to read and prepare. Java especially simplifies many processes in the coding industry as it helps integrate many forms of technology and different d…
The viewer will learn how to implement Singleton Design Pattern in Java.
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
Suggested Courses
Course of the Month17 days, 5 hours left to enroll

864 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