Postgres Pro
Downloads
Home   >   mailing lists

Thread: Anecdotal JDBC vs ODBC bulk export performance

Anecdotal JDBC vs ODBC bulk export performance

From
John Kew
Date:

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

Re: Anecdotal JDBC vs ODBC bulk export performance

From
Dave Cramer
Date:
Hi John,

If you just want speed you could try the copy option.

It would appear that the copy documentation seems to have disappeared, but the API docs are here https://jdbc.postgresql.org/documentation/publicapi/index.html


On 4 January 2016 at 15:37, John Kew <jkew@tableau.com> wrote:

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