CODEWITHSUNDEEP
javaoracle-databasejdbcoracle11gresultset
Jacel
Jacel

Reputation: 347

Result set takes long to process big data from Oracle

In an Oracle db, I have a query select * from tablehistory

  1. The query can run fast and returns 500,000 records. That's not an issue.
  2. The challenge is the result set takes a long long time to loop over.
  3. How do I solve that result set taking a long time to loop over?
  4. The result set data, I plan to save it into a hash map/do processing.
  5. Below code, start 1, 2, 3 print fast, start 4 takes long time to print; it's slow

How do I speed up my code?

String mayquery="select * from tablehistory";   
try {
        System.out.println("# start 0");
        PreparedStatement preparedStatement = con.prepareStatement(mayquery);
        System.out.println("# start 1");
        ResultSet resultSet = preparedStatement.executeQuery();
        System.out.println("# start 2");
        int count =0;
        System.out.println("# start 3");
        while (resultSet.next()) {
            System.out.println("start 4 count:"+count);
            count++;
        }
        System.out.println("# end a");
        System.out.println("count:"+count);
    } catch(Exception e) {
        
    }

Upvotes: 0

Views: 1228

Answers (1)

Nithin Mathew
Nithin Mathew

Reputation: 181

  1. Try setting resultSet.setFetchSize(int) this to a higher value. By default, it is set as 10. This parameter controls number of network calls from server to DB and can increase performance when used optimally.
  2. Check for network latency.
  3. System.out.println() is a heavy operation. Rather than printing this each time, try printing a line for an interval.

Upvotes: 1

Related Questions