Writing the results of this ResultSet to File is VERY slow (SOLVED)

Hi,

I hope I'm posting in the right place. Since I can't tell whether my problem is in my use of ResultSet or with the OutputStream, I'm posting in general.

This output of this code is running very slowly. There are about 10,000 records for the period in question that my ResultSet object is returning. It is then formatting and writing the results to a BufferedWriter "out".

Statement statement = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);
            ResultSet rs = statement.executeQuery("SELECT TOP 100 PERCENT CPA_Payable.TransactionID, " +
                    "CPA_Payable.Amount, CPA_Payable.addDate, CPA_Payable.isChargeBack, " +
                    "CPA_TransLogIFrame.PubUserID, " +
                    "CPA_TransLogIFrame.PubUserIP, CPA_TransLogIFrame.subID " +
                    "FROM CPA_Payable " +
                    "INNER JOIN CPA_TransLogIFrame " +
                    "ON CPA_Payable.TransactionID = CPA_TransLogIFrame.TransactionID " +
                    "WHERE (CPA_Payable.PublisherID = " + publisherID + ") AND (" +
                    " CPA_Payable.addDate >= '" + fromDate.toString() +
                    "' AND CPA_Payable.addDate <= '" + toDate.toString() +"') AND (CPA_Payable.isPaid != 1)");
            float totalChargeBacks = 0.00f;
            float totalRevenue = 0.00f;
            Calendar c = Calendar.getInstance();
            while(rs.next()) {
                /**
                 * Counter to eyeball time between records.
                 */
                int i = rs.getRow();
                System.out.println("Adding a new value from row number: " + i);
                String transactionID = "" + rs.getInt("TransactionID");
                float pAmount = rs.getFloat("Amount");
                Date d = rs.getDate("addDate");
                c.setTimeInMillis(d.getTime());
                String date = c.get(c.DATE) + "-" + c.get(c.MONTH + 1) + "-" + c.get(c.YEAR);
                int isChargeBack = rs.getInt("isChargeBack");
                String pubUserID = rs.getString("PubUserID");
                String pubUserIP = rs.getString("PubUserIP");
                String chargeBack = "NO";
                if(isChargeBack == 1) {
                    chargeBack = "YES";
                }
                float amount = round(pAmount, 2);
                String rAmount = null;
                if(isChargeBack ==1) {
                    rAmount = "(" + amount + ")";
                }
                else {
                    rAmount = "" + amount;
                }
                out.write("<date>" + date + "</date>" +
                        "<transaction_id>" + transactionID +"</transaction_id>"+
                        "<user_id>" + pubUserID + "</user_id>" +
                        "<ip_address>" + pubUserIP + "</ip_address>" +
                        "<is_charge_back>" + chargeBack + "</is_charge_back>" +
                        "<revenue>" + rAmount + "</revenue>");
                out.flush();
                //now add the data for the final report output.
                if(isChargeBack == 1) {
                    totalChargeBacks = totalChargeBacks + amount;
                }
                totalRevenue = totalRevenue + amount;
            }

}

When this code is first executed, the time it takes the cursor to move to the next row in the while loop is less than a second. It then slows down considerably as it continues to run. I just timed it at record 122 (of 10,000) and it took more than 15 seconds for the cursor to move to the next row. Thanks for helping me figure out what I'm doing wrong.

If you want to know if it is the writing that is causing the slowness or not, simply take out the actual writing (and flushing) code and try again. If it still is slow then it's not caused by the I/O.

I'd be a little surprised if it's the IO, but you're flushing between each chunk, which may somewhat defeat the purpose of using a buffered writer (I'm not sure of the internals off the top of my head).

It's almost certainly the ResultSet.TYPE_SCROLL_SENSITIVE flag, which instructs the driver to re-check the database every time a row is fetched. This flag can drastically reduce your performance.

Thanks! Earnest, you are correct. It was the SENSITIVE flag. I changed it to INSENSITIVE and the report was done in about 15 seconds. 15 seconds versus a day to get through 1,000 records is pretty good.

Actually you would be still better off using a FORWARD_ONLY result set, since that's what your program actually does. It never does any scrolling (reading back old records again) so there's no point in saying it does. If you say you want a scrolling result set, the JDBC driver has to make every single record available in case you might go back and ask for it again. Whereas if you ask for a forward-only result set, the JDBC driver can happily discard a record the instant you ask for the next one. Much easier on the memory if you do that, then.

In fact that's the case for about 99% of JDBC applications in real life, I would guess, so I don't know why the tutorials persist in starting with anything but FORWARD_ONLY examples.

That makes a lot of sense. I changed the ResultSet to Type.FORWARD_ONLY. I'll go back through my code and change all appropriate ResultSets to that flag.

Thanks for your help.

Best,
Al