Thread: Fetching data from server into MS Excel/PowerBI via ODBC connection is very slow

Hello All,

We are importing/fetching data from the server into Microsoft Excel
and/or PowerBI via ODBC connection. For a data set with 5 columns and
10000 rows it is roughly taking 10 mins to pull data.

The time to import/fetch data to excel/PowerBI increases when the
columns increase. We can confirm that the N/W is not causing the
slowness.

What we found out is that the slowness is the result of directly
calling SQLFetch() every row and then SQLGetData() for every column.
That is the reason when the columns are higher in the result-set the
time take also increases. What we were expecting is that SQLBindCol()
should be called to make the pull faster. Is there any method where we
can force clients (MS Excel/PowerBI) to call SQLBindCol() - instead of
SQLGetData() for every column to speed things up ?

Is our assumption correct ? any best-practices or tuning which needs
to be done. ? From the client or driver side.

Steps to Reproduce:
1. Open Excel/PowerBI -> Data -> Get Data -> From Other sources ->
From ODBC -> Select DSN (PostGreSQL)
2. Execute Query under "SQL statement" ( Select
table1,table2,table3,table4,table5 from TESTDB Limit 10000)

OS = Windows 10
PostGreSQL (ANSI) = Version 12.02

Thanks
Arshad