5

I have a Java application using JDBC that runs once per day on my server and interacts with a MySQL database (v5.5) also running on the same server. The app is querying for and iterating through all rows in a table.

The table is reasonably small at the moment (~5000 rows) but will continue to grow indefinitely. My servers memory is limited and I don't like the idea of the app's memory consumption being indeterminate.

If I use statement.setFetchSize(n) prior to running the query, it's unclear to me what is happening here. For example, if I use something like:

PreparedStatement query = db.prepareStatement("SELECT x, y FROM z");
query.setFetchSize(n);
ResultSet result = query.executeQuery();
while ( result.next() ){
    ...
}

Is this how to appropriately control potentially large select queries? What's happening here? If n is 1000, then will MySQL only pull 1000 rows into memory at a time (knowing where it left off) and then grab the next 1000 (or however many) rows each time it needs to?

Edit:
It's clear to me now that setting the fetch size is not useful for me. Remember that my application and MySQL server are both running on the same machine. If MySQL is pulling in the entire query result into memory, then that affects the app too since they both share the same physical memory.

RTF
  • 6,214
  • 12
  • 64
  • 132
  • 1
    Unless you were looking to mess with row size versus packet size, don't worry about this. Spend a bit more time on why you have to get the whole table every time... – Tony Hopkinson Jun 07 '14 at 14:35
  • I'm not sure what you mean. I don't actually have get the entire table in memory at once, I just have to process every row while controlling memory consumption on the server. – RTF Jun 07 '14 at 14:47
  • 1
    Essentially you do have the entire table in memory at once on the mysql server. Setfetchsize is a hint (may be ignored) to deliver it in chunks to the client. it affects how many network messages are required to deliver all the data. Have a peek at this http://stackoverflow.com/questions/1318354/what-does-statement-setfetchsizensize-method-really-do-in-sql-server-jdbc-driv – Tony Hopkinson Jun 07 '14 at 15:03
  • I see. So, are you saying that regardless of the setFetchSize value (and regardless of whether the driver is actually using it or not) mysql will **always** pull the entire table into memory? – RTF Jun 07 '14 at 15:12

1 Answers1

7

The MySQL Connector/J driver will fetch all rows, unless the fetch size is set to Integer.MIN_VALUE (in which case it will fetch one row at a time AFAIK). See the MySQL Connector/J JDBC API Implementation Notes under ResultSet.

If you expect memory usage to become a problem (or when it actually becomes a problem), you could also implement paging using the LIMIT clause (instead of using setFetchSize(Integer.MIN_VALUE)).

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
  • Paging is exactly what I'm looking for! And it so simple to do with MySQL, just by using the LIMIT clause and an offset. Thanks. – RTF Jun 07 '14 at 16:18