Why SQL query evaluation takes longer in dataflow than SSMS

[smpa01]

My datasource is Azure SQL DB and I am extracting data from the DB ~168mb.

The SQL query is complex but fully optimized. It takes between 6-6.5 minutes to return the query result in SSMS. 

When I run the same in df it runs for 10 minutes and generates "Evaluation was cancelled"

Firstly, for the life of me I don't undertand why would it take longer to evaluate the same sql query in DF? I am not the DBA of the SQL DB, hence can't run trace when the datflow runs the sql code.

Also, I checked https://docs.microsoft.com/en-us/power-query/power-query-online-limits#authoring-limits here and it tells the following

"Authoring Limits

Authoring limits are the same across all products. During authoring, query evaluations return previews that may be subsets of the data. Data is not persisted.

Hourly Evaluation Count: 1000

Daily Evaluation Time: Currently unrestricted

Per Query Timeout: 10 minutes"

Does Per Query Timeout mean that my SQL must return the data view in <=10 min else it will timeout the query which is why I am getting the following?

I am not sure what do I need to do in order for this query to return the data in <=10 min in df where it correctly returns the data in <7  min ssms.

Also, as a fix I don't want to translate this whole SQL query to equivalent Power Query. That would be my last resort (due to Power Query's severe performance issue and it would create a ton of additional work on for me for the conversion).

I am wondering if the Authoring limitation of Per Query Timeout applies on pure PQWRY script too (i.e. not using single native database queries and relying on query folding)? I guess not.

Also, I wrapped my query inside a Table.View mentioned here for the optimization and it still did not work. https://blog.crossjoin.co.uk/2020/07/05/why-is-power-bi-running-my-sql-query-twice/

let
    Source = Sql.Database("server", "db", [Query="sql query"]),
    OverrideZeroRowFilter = Table.View(
null,
[GetType = () => type table[
service = Text.Type,
siteId = Int32.Type,
Year = Int32.Type,
Month = Int32.Type,
Cost=Double.Type,
Condition=Text.Type
], GetRows = () => Source, OnTake
= (count as number) =>
if count = 0
then #table(GetType(), {})
else Table.FirstN(Source, count)]
)
in
OverrideZeroRowFilter

Please any pointers from anyone to troubleshoot this would help me a lot.

[smpa01 · Accepted solution]

Closing the thread, discussing here - https://community.powerbi.com/t5/Power-Query/Usage-of-Table-View-in-TSQL-query-in-dataflow/m-p/20496...

[lbendlin]

Add a filter to your SQL query to limit the return set, and to speed up the data spooling.  If the query runs successfully, compare the timing and then slowly increase the limit until you hit the timeout again.

Also ask your DBA to check the query log for the dataflow's requests. Dataflows are known to ask for a lot of meta data in addition to the actual data.