Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
joeforpowerbi
New Member

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 3
zoloturu
Memorable Member
Memorable Member

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
Not applicable

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
Resolver III
Resolver III

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors