Power BI runs very slow for a same query which runs fast in Toad Oracle

[NoobAnalyst01]

Hello,

I am trying to import my data to power bi, which gives me the data as below PL/SQL:

SELECT
  A.WINDOW,
  A.ROUTE,
  A.WEBORDERNUM,
  min(A.SCANDATE)
FROM
  SCANNING A
WHERE
  (
   A.CARTONSTATUS  IN  ( 'DELIVERED','REFUSED' )
   AND
   ( A.SCANDATE  >TRUNC(SYSDATE)+1/24)
  )
 Group by
 A.WINDOW,
 A.ROUTE,
 A.WEBORDERNUM

I import the scanning table in power bi, and do the following steps:

c

select "rows"."WEBORDERNUM" as "WEBORDERNUM",
    "rows"."ROUTE" as "ROUTE",
    "rows"."WINDOW" as "WINDOW",
    min("rows"."SCANDATE") as "Min Scan"
from 
(
    select "_"."SCANDATE",
        "_"."WEBORDERNUM",
        "_"."ROUTE",
        "_"."WINDOW"
    from "AIRCLIC_PROD"."CARTONTRACKING" "_"
    where "_"."SCANDATE" > TO_TIMESTAMP('2018-06-26 01:00:00','YYYY-MM-DD HH24:MI:SS.FF') and ("_"."CARTONSTATUS" = 'DELIVERED' and "_"."CARTONSTATUS" is not null or "_"."CARTONSTATUS" = 'REFUSED' and "_"."CARTONSTATUS" is not null)
) "rows"
group by "WEBORDERNUM",
    "ROUTE",
    "WINDOW"

I think they are exactly the same, but I am not sure it takes more than 5 mins to import the data in power bi, but when I run the query in Toad, it only take last than 1 sec.

Anyone has an idea?

[Anonymous · Accepted solution]

This thread should give you instruction on how to use Custom SQL as your source

[Anonymous]

@NoobAnalyst01,

My guess is that since your source does not support query foldings for some transformations, so the transformation are taking place in your local computer instead, so basically it will pull every row from your table into your computer's memory and perform transformation rather than performing all transformation at the source if you write a custom SQL instead. 

[NoobAnalyst01]

I am 90% think the problem is from scandate, here is the sample table of my scandate

im powerbi , in order to achieve scandate>trunc(sysdate)+1/24, i have one step:

= Table.SelectRows(#"Filtered Rows2", each [SCANDATE]>#datetime(Date.Year(Date.From(DateTime.LocalNow())),Date.Month(Date.From(DateTime.LocalNow())),Date.Day(Date.From(DateTime.LocalNow())),1,0,0))

you mean transformation, the transformation must from there, but i really don't know more detail.

[Anonymous]

@NoobAnalyst01,

Can I ask how many records do you have in your table? Also does it run faster if you take out your "Filtered Rows#2" transformation? 

[NoobAnalyst01]

filter 2 is to filter the order status only for delivered or refused, it won;t affect.

i have more than 6000 return orders scans.

when i run the query directly in Oracle, it takes less than 1 sec!!!

but when i import the table and do it in power bi, more than 6 7 mins

[Anonymous]

@NoobAnalyst01,

I would recommend using your custom SQL as source instead since it is running faster

[NoobAnalyst01]

so I should use direct query?

but i have another tables, wihch I import from the database.

[Anonymous · Accepted solution]

This thread should give you instruction on how to use Custom SQL as your source

[MallikarjunaBan]

any one achieved this performance issue please reply

[NoobAnalyst01]

you mena, still use import method, but using custome sql rahter than import the table?

[v-yuezhe-msft · Accepted solution]

@NoobAnalyst01,

Directly add  your original SQL statement into the highlighted box when importing data from Oracle, and check if the time reduces.


Regards,
Lydia

[Via91el]

I am using directquery and my visuals are very basic. However, it takes a bit more time to populate the dashboard, whilst Sql server ran the query instantly. Could this be an issue of having multiple queries or do they run independent of themselves? I doubt this to be the case because some queries from certain sources are immediate, even on the same page, whereas other queries or visualizations take a much longer time (~10-15 seconds) .