2

I have an Oracle 12c database query, which pulls a table of 13 columns and more than 114470 rows in a daily basis.

I was not concerned with this issue until I moved the same code from my DEV server to my PROD server.

On my DEV environment the query takes 3 min:26 sec to complete its execution. However on PROD the exact same code takes 15 min:34 sec for finishing.

These times were retrieved adding logs on the following code execution:

private List<Map<String, String>> getFieldInformation(ResultSet sqlResult) throws SQLException {


    //Map between each column name and the designated data as String
    List<Map<String, String>> rows = new ArrayList<Map<String,String>>();

    // Count number of returned records
    ResultSetMetaData rsmd = sqlResult.getMetaData();
    int numberOfColumns = rsmd.getColumnCount();

    boolean continueLoop = sqlResult.next();

    // If there are no results we return an empty list not null
    if(!continueLoop) {
        return rows;
    }
    while (continueLoop) {
        Map<String, String> columns = new LinkedHashMap<String, String>();

        // Reset Variables for data
        String columnLabel = null;
        String dataInformation = null;

        // Append to the map column name and related data
        for(int i = 1; i <= numberOfColumns; i++) {

            columnLabel = rsmd.getColumnLabel(i);
            dataInformation = sqlResult.getString(i);

            if(columnLabel!=null && columnLabel.length()>0 && (dataInformation==null || dataInformation.length() <= 0 )) {
                dataInformation = "";
            }
            columns.put(columnLabel, dataInformation);
        }
        rows.add(columns);
        continueLoop = sqlResult.next();
    }
    return rows;
}

I understand that "getString" is not the best way for retrieving non TEXT data, but due to the nature of the project I not always know the data type.

Furthermore, I checked in PROD under task manager, that "Memory (Private Working Set)" is being reserved very slowly.

So I would appreciate if you could help in the following questions:

  1. Why there is a discrepancy in the execution timings for both environments? Can you please highlight some ways for checking this issue?
  2. Is there a way were I can see my result set required memory and reserve the same upfront? Will this have some improvements in Performance?
  3. How can I improve the performance for getString(i) method?

Thank you in advance for your assistance.

Best regards, Ziza

Ziza
  • 161
  • 1
  • 4
  • Hello, since I am pulling data from a database that I have no control and since there are policy restrictions regarding software installations, software changes are not an option for me. – Ziza Jul 27 '15 at 10:44
  • Why do you think getString is the problem? Is the result set fetch size (number of rows fetched on each trip to the DB) the same on both environments? And the DB character set, JDBC driver version, etc.? Are both accessing a local database on the same machine the Java runs on, or elsewhere? Have you timed your query from another client for comparison? – Alex Poole Jul 27 '15 at 11:51
  • Number of rows is the same, since both in DEV and PROD I retrive the results from the same Database. – Ziza Jul 27 '15 at 11:54
  • You are describing things like char set, can you detail the type of configurations that you are talking about? Thank you. – Ziza Jul 27 '15 at 11:56
  • I'm asking about [the fetch size](http://docs.oracle.com/database/121/JJDBC/resltset.htm#i1023619) rather than the total number of rows. Your dev environment runs against your prod DB?? You may still have faster network access from one environment, which you can test by using a different client (running the query from SQL Developer say) on the dev and prod machines. But do look at the fetch size too, you may be accessing the DB inefficiently. – Alex Poole Jul 27 '15 at 12:05

0 Answers0