Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Improving Data Transfer Speed in ETL Process

We are using POWERBI in our organization. WE are having data around 400GB in our server. The issue is that it will take around a day to refresh all the dashboards which will be used to followup and m...
Need Help
Need Suggestions
Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi,

Thanks for your help. We are using ORACLE DBs.

josef78's avatar
josef78
Memorable Member
5 years ago

Connecting to Oracle DB for large tables is very complicated, and is hated from Oracle side.

Performance is very dependent on type and version of Oracle Client.


Oracle ODP.NET (which is used in "Oracle database" connector in Power BI) and ODBC connectors are very slow, independent on version, and are unusable for large tables.

 

Little better is it with Oracle OLEDB provider.
In default configuration are slow same as above, but with some registry (or connection string) settings of FetchSize, you can gain significant performance boost (about 3x to 5x depends on version) if change default value 100 to value 1000 or more (higher values bring only minimal boost, but make makes another troubles in case many parallely loading). It is big improvement, but best what you gain is still much slower then competition solution.

 

Another complication is version, Oracle OLEDB working good only in 12.1 version of client, but this version is now unsupported. In version 12.2 (and also 18.3) Oracle implemented memory bug, which for transfer of large table consume extreme large amount of memory (connector consume aprox 4GB of RAM, for import 400MB table). In version 19.3 Oracle implement another bug, also for large tables, when every table during transfer is rapidly slowing down (aprox to half during every 15minutes).

 

Currently best solution for large Oracle tables is use 3rd party connector (e.g. https://www.progress.com/odbc/oracle-database ) which is much better then Oracle OLEDB. Or use two step extract, first extract Oracle data to MSSQL using SSIS with Microsoft provided Oracle connector https://www.microsoft.com/en-us/download/details.aspx?id=58228 ) and then load to Power BI from MSSQL (although in two step, is still faster then Oracle OLEDB).