java - Is there a mysql JDBC that will respect fetchSize?

Question

I am using MySQL and want to utilize the setFetchSize property. The default MySQL JDBC implementation does not really respect it. If you set fetchsize to Integer.MIN_VALUE it will fetch each row individually, but considering the reason I want to use fetchSize is that I have enough data to put my memory usage into the 2 G range having to do one query per row is going to take forever.

I would like to instead plug in a JDBC implementation that will work with MySQL and properly respects fetch size, allowing me to set a fetchsize of 10,000 or some other higher limit. Can anyone point me to a jar that may provide such an implementation? failing that is there any other resource to allow me to reasonable do a query containing tens of thousands of entries in a manner that is efficient, but in memory and number of sql queries required.

Answer 1

If you enable the MySQL JDBC option useCursorFetch, fetchSize will indeed be respected by the driver.

However, there is one disadvantage to this approach: It will use server-side cursors, which in MySQL are implemented using temporary tables. This will mean that results will not arrive until the query has been completed on the server, and that additional memory will be used server-side.

If you just want to use result streaming and don't care about the exact fetch size, the overhead of setFetchSize(Integer.MIN_VALUE) is not as bad as the docs might imply. It actually just disables client-side caching of the entire response and gives you responses as they arrive; there is no round-trip per row required.