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

[joeforpowerbi]

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

[zoloturu]

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!

[Anonymous]

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?

[Arentir]

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.