Amit Chandak on 17 Jul 2020 17:22:59
Oracle data import is slow. This idea was delivered long back but is still slow -https://ideas.powerbi.com/ideas/idea/?ideaid=4cfb2858-ef99-4a62-9fa1-94d5a2b1dc75
Users are still updating the same idea. Logging a new one for tracking as the old one is marked completed.
- Comments (1)
RE: Faster oracle database connection
Let me tell you a story...
It happened many years ago, when Microsoft didn't like Oracle and Oracle didn't like Microsoft. One day, a developer at Oracle figured out how to get data from a Microsoft SQL Server. Then someone conversely, someone at Microsoft figured out how to get data from Oracle. The data speeds were pretty good, But the decided, each camp on their own, to slow down these cross-platform connecters so that they could point to their competitor and state, "THEIR data is slow, but OURS is fast." They each slowed down these components to point where they were almost unusable.
Finally they called a truce and decided that they would agree to some sort of 'detente' and not screw with them any more.
Anyway, I read it on the internet so it must be true :)
Seriously though, Power BI does not do Query Folding of Oracle like it does for SQL, and/or the interpretation and execution is not the same in both camps.
For example, consider the following query:
select * from ( select * from ) as x where =
And for argument sake, let's suppose that the WHERE clause will limit the rows from 1 million to 10 K
SQL Server Query Optimizer will figure out how to interpret this and ONLY pull the 10 K records.
But the Oracle connection, that will A) run the inner query, get 1 M rows, then run the outer query against it to limit it to 10K rows.
To get around that, build a dynamic SQL statement using parameters in your WHERE clause, then execute that query text in a query that returns rows.