Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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?
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?
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.
@jeffshieldsdev thanks for clarification. Cant run trace on this sql server, not part of SYSADMIN. Thanks for your suggestion.
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.
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?
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.