Forum Discussion

joeforpowerbi's avatar
joeforpowerbi
New Member
7 years ago

SQL Statement Fast in SQL Management Studio - and Very Slow in Power BI

Hello All,

I am new to power bi.  I am using a SQL statement to pull data into our model. This SQL statement takes 10 seconds to complete in SQL Server Management Studio; However it is taking about 35 to 40 mins to complete in Power BI. Can you please assist? below is the SQL:

 

SELECT


location
SalesDate
,sum(qty)
,sum(amount)
,Sum(cost*qty)
,sum(promocost)
,Department
,ProductNumber
,Description


FROM


SalesTable

 

WHERE
SalesDate >= '01/01/2017'

GROUP BY
location
SalesDate
Department
ProductNumber
Description

 

Data Connectivity mode is Import.

 

Thank you in advance.

 

Joe

3 Replies

  • Arentir's avatar
    Arentir
    Icon for Resolver III rankResolver III
    7 years ago

    Hi joeforpowerbi

    I would create a trace in SQL Profiler and see what query is actually send to the database. Maybe it get reformulated somehow and cause the sql engine to choose the wrong sql plan.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    I would also suggest double checking whether this query is in fact completing within 10 seconds.  Any easy trap to fall into with SQL management studio is that it can often show data very quickly, but still be running the query.  On first glance its pretty subtle that its still running, but you should see an excution time ticking away.

     

    That being said, also make sure you are making an Apples to Apples comparison.  When you are getting your 35-40 timing, where is this occuring?  Is that using Power BI desktop or through the Service?  Are you doing anything additional with this query in Power BI as part of the import, such as additional joins?  Are you doing anything in the query editor that might be causing Power BI to alter the query to perform additional steps (i.e. query folding) such that you are actually running a longer more complex query?

  • zoloturu's avatar
    zoloturu
    Icon for Memorable Member rankMemorable Member
    7 years ago

    Hi joeforpowerbi,

     

    I agree with Arentir. I would propose to run SQL Profiler tracing two times:

     

    1) Run your query in SSMS query and save a trace of it.

    2) Run refresh of the report and save this trace as well.

     

    And then compare both.

     

    P.S. If your database is in Azure then you need to use Extended Events

     

    About SQL Server Profiler - https://www.youtube.com/watch?v=mJ8Dyv4Uk6E

    Extended Events - https://www.youtube.com/watch?v=qqqJeC8u6cA

     

    Regards,
    Ruslan
    -------------------------------------------------------------------
    Did I answer your question? Mark my post as a solution!