CODEWITHSUNDEEP
javaperformancedesign-patternsout-of-memoryfile-writing
Akhil Khandelwal
Akhil Khandelwal

Reputation: 103

Design Pattern for writing file with minimum memory consumption

I need to write a file by reading an xml, containing format information and values to be fetched from database. I am working on large number of records(200,000). I have tried keeping all the data in memory but got out of memory error. I have tried hitting database again and again but then I got performance issue. As queries being hit again and again decreases performance.

Steps involved for the process:

  1. hitting db to get all records for which file is to be created
  2. reading xml file using Jaxb
  3. creating map of all the data obtained from step 1.
  4. iterating over map obtained from step1
  5. deriving what data need to be displayed from format object created in step 2
  6. appending result in a string for each record and then writing same into file

Though, finally I resolved this by reading a predefined count of data once and write file for this and then work on next bunch. But I have not followed any design pattern for this.

Is there a design pattern that uses minimum memory and let me write file efficiently?

Upvotes: 1

Views: 145

Answers (2)

Nir Alfasi
Nir Alfasi

Reputation: 53565

The design pattern is called Pagination (using a cursor). When you send a query to Oracle DB for example, the default number of results in the resultset returned is 50. Only when you ask the resultset to get next() it'll return the next 50 results. That's how most DBs work and designed to be efficient for such a pattern (see this code example):

public static void viewTable(Connection con, String dbName)
    throws SQLException {

    Statement stmt = null;
    String query =
        "select COF_NAME, SUP_ID, PRICE, " +
        "SALES, TOTAL " +
        "from " + dbName + ".COFFEES";

    try {
        stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery(query);
        while (rs.next()) {
            String coffeeName = rs.getString("COF_NAME");
            int supplierID = rs.getInt("SUP_ID");
            float price = rs.getFloat("PRICE");
            int sales = rs.getInt("SALES");
            int total = rs.getInt("TOTAL");
            System.out.println(coffeeName + "\t" + supplierID +
                               "\t" + price + "\t" + sales +
                               "\t" + total);
        }
    } catch (SQLException e ) {
        JDBCTutorialUtilities.printSQLException(e);
    } finally {
        if (stmt != null) { stmt.close(); }
    }
}

So, you can open the file, and write every "page" of results that you're getting, ask for the next page etc. Once you're done - close the file.

Upvotes: 2

Peter Lawrey
Peter Lawrey

Reputation: 533870

If you are writing to XML, I suggest writing out each record as you read it in. This way you only need enough memory for one record at a time and it doesn't matter how many records you have.

e.g.

Statement stmt = null;
String query =
    "select COF_NAME, SUP_ID, PRICE, " +
    "SALES, TOTAL " +
    "from " + dbName + ".COFFEES";

try {
    stmt = con.createStatement();
    ResultSet rs = stmt.executeQuery(query);
    while (rs.next()) {
        xmlFile.println("<coffee>\n" +
              "<coffeename>" + rs.getString("COF_NAME") + "</coffeename>\n" +
              "<supId>" + rs.getInt("SUP_ID")+ "</supId>\n" + 
              "<price>" + rs.getFloat("PRICE") + "</price>\n" + 
              "<sales>" + rs.getInt("SALES") + "</sales>\n" + 
              "<total>" + rs.getInt("TOTAL") + "</total>\n" +
              "</coffee>");
    }
} catch (SQLException e ) {
    JDBCTutorialUtilities.printSQLException(e);
} finally {
    if (stmt != null) { stmt.close(); }
}

You may need to escape out strings in case they contain special characters.

Upvotes: 0

Related Questions