Обсуждение: Anecdotal JDBC vs ODBC bulk export performance

Поиск
Список
Период
Сортировка

Anecdotal JDBC vs ODBC bulk export performance

От
John Kew
Дата:

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

От
Dave Cramer
Дата:
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