Oracle data load speed into Power BI Desktop

[dgwilson]

Hi there... I seeking some advice with a loading speed experience with Oracle Database loads into Power BI Desktop.

Specifically I’m loading data from an oracle database… I have permission allocated etc…
It’s taking in the order of 1 hour to load 2 million rows. I don’t see 2 million as a lot of rows and believe it should be going significantly faster.

When this is fully loaded… I’d been needed to load 48 million rows. Gulp.


Now I admit that I can add a where clause to the select statement. That will happen later. And I can probably be selective about the columns that are loaded. And I’ll have to do both of those.

For now … I’m concerned that it takes 1 hour to load 2 million rows. Would this be a normal experience?

This is In the office, LAN connected (Gigabit), loading oracle database to Power BI desktop.
HP laptop 24GB RAM.
February and March desktop releases.
… Import… Databases… Oracle… … so Oracle driver I guess.

Any advice on where to look for performance measure and/or performance tuning?

Any ideas?

- David

[MallikarjunaBan]

thanks @dgwilson will Implement and update the status

[MallikarjunaBan]

any break thru for this issue please reply

[dgwilson]

> any break thru for this issue please reply

Nothing specific. 

Advice is to only import the columns you need. Having a narrow table will make a huge difference. And then only the rows you need - that clearly helps tool.

After that I believe that in the July 2023 releas ethere are some changes to the Oracle Connector - so it is worth checking out what may have changed there.

- David

[Anonymous]

Hi,

I recently worked with SSIS +Oracle combination. I had the same performance issue with the  Oracle OLEDB provider. Fortunately, we have Attunity connectors to Oracle that use a different mechanism to extract or load data to oracle much much faster than the OLEDB. I dont think we have the same support for Power BI (at least I havent found something yet).  May be you can try using /+PARALLEL(X)+/ hints in your select statements.

[dgwilson · Accepted solution]

The parallel load commands didn't work for me - the schema needs to be configured to deliver for that.

I have performed a test foing a SELECT <field>, <field>, <field> from <tableName> - i.e. only a selected numebr of fields rather than a SELECT * from <tablename> and I can say that 1.5M rows loaded in about 2.5 minutes - way better than 30+ minutes.

For me the full SELECT * is OK for investigation and understanding the dataset. When it's ramped up to a production scenario it is a good idea to only load what is needed.

- David