Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

What strategies can be used to read every row in a large Oracle table, only once, but as fast as possible with JDBC & Java ?

Consider that each row has non-trivial amounts of data (30 columns, including large text in some columns).

Some strategies I can think of are:

  • Single thread and read table. (Too slow, but listed for clarity)
  • Read the id's into ConcurrentLinkedQueue, use threads to consume queue and query by id in batches.
  • Read id's into a JMS queue, use workers to consume queue and query by id in batches.

What other strategies could be used ?

For the purpose of this question assume processing of rows to be free.

share|improve this question
    
what's wrong with the ConcurrentLinkedQueue? Seems like a valid approach. Assuming the data does not change you may even use ROWNUM for your batches –  Archimedes Trajano Apr 26 at 10:00
2  
    
    
@gnat Would totally agree, and never post a broad question like this if this were actually stackoverflow. –  Ben George Apr 26 at 14:57
    
Thanks Archimedes, but both those questions assume synchronous reads. –  Ben George Apr 26 at 15:05

1 Answer 1

First thing is how fast do you need it to be? One should be mindful to not get into the "premature optimization" zone.

The first suggestion for clarity is to increase the jdbc_batch_size. If your oracle database sits on a different host then you will see an increase in performance on all your suggestions.

You can implement paging to split your dataset into multiple pages, then using your suggestions

  • Read the entire row/page into a ConcurrentLinkedQueue, use threads to consume queue (you avoid having to re-query based on id at the expense of using more memory)
  • A similar approach can be taken for the JMS suggestion

Another suggestion, although I am not sure whether this will work, is to use the Java Streams API and process the ResultSet. The code for this will probably be more concise so that would be a plus. The only downside is that you might not get ResultSet to process in parallel (see this and this for some insight).

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.