Forum Discussion
Oracle data load speed into Power BI Desktop
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
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
5 Replies
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.
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
any break thru for this issue please reply
> 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
thanks dgwilson will Implement and update the status
