TSQL Query runs longer in Dataflow than in SSMS

[smpa01]

Is there a reason why a TSQL query takes a lot longer (>15min) to run in DF than the same query in SSMS (~3min). The TSQL query is complex, consists of 4 large tables, table variable, scalar values, PATINDEX, INNER JOIN, LEFT JOIN, CROSS JOIN, CTE etc. With all these, SSMS gives the output in 3 min max. However, the same query runs in DF for more than 15 min and errors out.

Does nayone know why it happens? I was under the impression that PQWRY gives the output at the same amount of time as SSMS would do.

@ImkeF 

[jeffshieldsdev]

Can you check the SQL Profiler tool? My experience has been the native SQL queries will execute up to 3 times concurrently when coming from dataflows through a gateway.

EDIT: maybe there isn't a gateway, is it on-prem SQL Server or Azure?

[smpa01]

@jeffshieldsdev 

maybe there isn't a gateway, is it on-prem SQL Server or Azure? there is a gateway, On-prem SQL Server

Can you check the SQL Profiler tool? - are you suggesting that I can connect the DF to SQL Server Profiler. Is it possible? If yes, which events to choose?

[jeffshieldsdev]

Sorry, I could have been clearer.  I was suggesting refreshing your dataflow and checking SQL Profiler to see how many times the SQL is executing--it may be actually executing 3 times concurrently, causing the slower performance.

[smpa01]

@jeffshieldsdev  thanks for clarification. Cant run trace on this sql server, not part of SYSADMIN. Thanks for your suggestion.

[v-xiaotang · Accepted solution]

Hi @smpa01 

if "not part of SYSADMIN", perhaps you can contact the database administrator to trace on the sql server.

FYI:

Why Is Power BI Running My SQL Query Twice?
https://blog.crossjoin.co.uk/2020/07/05/why-is-power-bi-running-my-sql-query-twice/

Why Does Power BI Query My Data Source More Than Once?
https://blog.crossjoin.co.uk/2019/10/13/why-does-power-bi-query-my-data-source-more-than-once/

Finding Power Query Query Execution Times In Power BI And Excel Using SQL Server Profiler
https://blog.crossjoin.co.uk/2019/02/09/power-query-execution-times-using-sql-server-profiler/

Hope it helps!

Best Regards,

Community Support Team _ Tang

If this post helps, please consider Accept it as the solution✔️ to help the other members find it more quickly.

[GilbertQ]

Hi @smpa01 

I would suggest making sure that your gateway is as close as to the SQL Server is possible. Could it be that the gateway is the bottleneck?

[smpa01]

@GilbertQmaking sure that your gateway is as close as to the SQL Server is possible - Sorry can you please elaboarte on that. I did not undertsanf what you meant by that.