If you’re using the native Oracle connector in Power Query, you will probably experience a very slow import performance. Thanks to Tristan Malherbe for recommending to use the OleDB-connector in Power Query instead. This speeds up import enormously. Just have in mind, that this method might prevent query folding, so it is mostly suitable for scenarios where you have to pull the full tables anyway.
How to create the connection string
If you’re using the OleDb.DataSource connector instead, you have to pass a connection string as the first parameter and an optional query record as the second parameter. To speed it up even more, you should use a FetchSize parameter in the connection string. For me, this didn’t work when I pasted it into the popup-window. So I had to manually add it in the query editor:
The “:1521” in the connection string is the port number, which is usually 1521 for Oracle databases.
You can play around with the FetchSize to determine the ideal value for your specific use case.
For security reasons, make sure NOT to pass your credentials in the connection string (as mentioned in the link above), but to pass them in the credentials section instead (that’s the 2nd dialogue in the import process)
Query speed had been improved by order of magnitude for me:
Enjoy and stay queryious 😉