The open source JDBC driver appears to outperform the ODBC driver when executing bulk extract queries of the form: "SELECT * from [Blah] Limit [n]" - We are just measuring the raw rows per second from this query for pulling all the rows out. After playing with the FetchSize in the ODBC driver we have improved performance significantly but are there any other techniques; tools or tips which you would suggest for further improving performance?
ReadOnly does not appear to have a significant effect. The JDBC connection string is using the default settings and setFetchSize is not called on the JDBC driver. We have not yet started profiling the ODBC driver. For this quick test we are also not using column binding on the ODBC side; but we are calling SQLFetch to count the total # of rows returned. Essentially the same thing occurs on the JDBC side.
-John