0

I have been working on improving the performance (emphasis on execution speed) of a Java program that fetches records from a MySQL table. The reason for slowness is the large number of records the table is holding.

After some research, I had 2 options in hand-1. Batching and 2. Pagination.

  1. Batching

Batching was not attempted, since JavaDoc of addBatch says that typically it takes Update commands or insert commands.

  1. Pagination

It makes the process extremely slow because of the multiple calls to MySQL from Java.

Now, I am left out with the option of having a streaming result set for MySQL.

      stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,java.sql.ResultSet.CONCUR_READ_ONLY);

      stmt.setFetchSize(Integer.MIN_VALUE);

My question is whether having a streaming result set improve the execution speed or will it reduce the speed of execution?

Note: I have gone through these discussions 1 and 2 Could you please enlighten. Thanks

Community
  • 1
  • 1
pnv
  • 1,437
  • 3
  • 23
  • 52
  • 1
    There is no single answer. It depends on the size of the result set. For small(ish) result sets the default behavior of MySQL will probably perform better, for larger it will be potentially bad because it will first load all rows into memory (which takes time, and may cause excessive garbage collection). – Mark Rotteveel Sep 17 '14 at 07:06
  • @MarkRotteveel: Thanks alot for the quick reply, Mark. I wanted an optimistic push to go ahead with this and try! :) – pnv Sep 17 '14 at 07:08

0 Answers0