When using the mysql jdbc drivers to run queries with that return large result sets you are very likely to get an OutOfMemoryError. Reason for this is that by default the mysql jdbc driver implementation tries to store the entire result set in memory. This is for performance reasons, with the assumption that the majority of result sets are relatively small and will fit in memory (which is fair enough).
If you do have a large result set then this is just not going to work and you will either get an OutOfMemoryError, or at best your application footprint drastically increases and slows to a crawl. Solution is to stream the result set, which is done by using a read only, forward only statement and setting the fetch size to Integer.MIN_VALUE.
Statement statement = connection.createStatement( java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY); statement.setFetchSize(Integer.MIN_VALUE);
Downside to this is that you can’t use the connection for anything else until your streaming result set has been closed.
Leave a Reply
You must be logged in to post a comment.