While working on a section of our companies application that handles migration between various database types, I ran into an interesting issue that I had not really noticed before. There has been a very wide desparity between how various jdbc driver implementations handle the fetching size in a result set.
For example..
MySQL will always return all rows for the query, while DB2 will default to returning exactly 32 rows. I found this odd and as I worked on the application, I noticed that the results seemed to be all across the board. One of the other major considerations when working with the fetching size was the type of data that I was migrating. Migrating smaller varchar based tables wasn't really an issue, however, I found that I had to check each table to ensure that there were not BLOB types present, as the size of those columns could vary greatly. (Between 2-3mb upwards of 1gb Thus, the data type for each column would greatly affect the fetching strategy for each table.
Then I hit some of the big kickers.. since I had to support older drivers, I found myself fighting the older MySQL driver. In order to do any type of streaming for this driver, you must set the fetchSize to Integer.MIN_VALUE, this seemed a bit of an odd selection and was very far off from the jdbc specification. Later builds did improve by allowing you to pass a useCursorFetch hint in the connetion string.
Ultimately, it was a bit of a struggle to make five very different database types migrate between one another in a seemless manner. However, it was a rewarding and challenging experience. In a world where I usually play thru abstractions such as Hibernate.. understanding what it really takes to make a database seamless/brandless/neutral is a very painful task.
Helpful Links:
Good advice for large result sets: http://nullpointer.ph/questions/1103/iterating-over-a-large-db-resultset
Lists a few brands defaults: http://webmoli.com/2009/02/01/jdbc-performance-tuning-with-optimal-fetch-size/
