Usage of Table.View in TSQL query in dataflow

[smpa01]

I previously posted question here

https://community.powerbi.com/t5/Service/Why-SQL-query-evaluation-takes-longer-in-dataflow-than-SSMS...

https://community.powerbi.com/t5/Service/TSQL-Query-runs-longer-in-Dataflow-than-in-SSMS/m-p/1863276...

To summarize - I have a complex  but optimized TSQL query that takes about 6-6.5 minutes to run in SSMS and the same query runs for 10 minutes in dataflow and then it generates "Evaluation was cancelled". Memory allocation is 50% for data flow and I can't run trace on SQL Server Profiler cause I am not the DBA

I was referrred to this link https://blog.crossjoin.co.uk/2020/07/05/why-is-power-bi-running-my-sql-query-twice/ as a fix.

I tried this on my SQL query as well and it did not cure the issue. I am willing to give it another go but I am simply trying to understand whther I am using Table.View in a way I am supposed to.

In the blog post,  Chris applies OverrideZeroRowFilter to a simple SQL Query.

let
Source = Sql.Database(
"localhost",
"AdventureWorksDW2017",
[Query
= "SELECT [DateKey]#(lf) ,[FullDateAlternateKey]#(lf) ,
[DayNumberOfWeek]#(lf) ,[EnglishDayNameOfWeek]#(lf)
FROM [AdventureWorksDW2017].[dbo].[DimDate]"]
),
OverrideZeroRowFilter = Table.View(
null,
[GetType = () => type table[
DateKey = Int32.Type,
FullDateAlternateKey = DateTime.Type,
DayNumberOfWeek = Byte.Type,
EnglishDayNameOfWeek = Text.Type
], GetRows = () => Source, OnTake
= (count as number) =>
if count = 0
then #table(GetType(), {})
else Table.FirstN(Source, count)]
)
in
OverrideZeroRowFilter

But what if my query contains table variable and / cte and / temp table,

do I need to apply OverrideZeroRowFilter for each ctes/table variable/temp table (e.g.cte1,cte2...) used in the query or just for the last select query used in the whole SQL query.

I am currently applying OverrideZeroRowFilter only

on last select query , i.e. select Year,Month, Service,Site,avgCost,optimizedCost,state from cte 8

Not sure, if is meant to be applied to each ctes to help the power query?

A sample query is following

; with cte1 as (select 
invoice, service, site, period, Year, Month
,SUM(Cost) as optimizedCost
, NULLIF(DATEDIFF(day, fromDate, toDate)+1,0) [billSpan]
from fact 
where service <>'s1' and service <>'s2' 
group by vendor, service, site, invoice, period, fromDate,toDate,account)

,cte2 as (select Year,Month,Date
                  ,DENSE_RANK() OVER (order by Year_Month ASC) as [Index] from calendar)
				  
,cte3 as (select Year,Month,[Index] 
           from   cte11
		   group by Calendar_Year,Calendar_Month,sn
		   order by Calendar_Year,Calendar_Month,sn OFFSET 0 ROWS)
--------------------------------------------------------------------
--------------------------------------------------------------------

select Year,Month, Service,Site,avgCost,optimizedCost,state from cte 8		   
		   

Thank you in advance.

@cwebb @ImkeF 

[jennratten]

In your original dataflow that ran for 10 min and then displayed Evaluation was Cancelled, have you tried just saving and closing the dataflow?  The 10 min + the message you received are usually when trying to view the query results in the dataflow editor/Power Query Online.  It has a default timeout of 10 min, which can be adjusted, but sometimes does not directly reflect the actual amount of time it will take for the query to run when the dataflow is refreshed, especially if query folding is taking place. This is because other actions are taking place in the background when in the editor.

I have several queries in dataflows that timeout when I try to preview them in the editor but run just fine in minutes or sometimes just seconds when the dataflow is refreshed.

Here is where you can change the timeout...

[smpa01]

I will leave this thread open, in case someone wants to contribute.

The only thing that worked for me was to divide the sql queries in parts using cte and insert each cte s in corresponding table variable (by default the column schema needs to be mentioned), did not make any impact when I ran that on SSMS, returned the result with exactly same time as before. But, for Power query it made a world of difference, returned my result at the same time as SSMS. Not sure, what is going on inside PQWRY while it attempts to evaluate a SQL query. But I find it inconvenient to divide my queries in smaller parts and have them inserted in a table variable for a query that takes lesser than <10 minutes on server side. Why PQWRY can't evaluate that at the same time?

@jennratten  - thanks for looking into it.

In your original dataflow that ran for 10 min and then displayed Evaluation was Cancelled, have you tried just saving and closing the dataflow? - No it did not strike me as a doable option, will try out next time if I have a similar situation and let you know how it goes.

especially if query folding is taking place - There is no query folding to take place here at all (to my knowledge) as I am running a native SQL query. Query folding only happens if you transform data using Power Query's syntax and the enging translates back the pqwry to the native query to the foldable source. The reason why I am avoiding query folding casue I want to write a native query  so that I can overcome power query's perfomance issue altogether.

I have several queries in dataflows that timeout when I try to preview them in the editor but run just fine in minutes or sometimes just seconds when the dataflow is refreshed. - good to know

Here is where you can change the timeout- this does not have the capacity to overwrite server's default timeout

[jennratten]

Here is some additional information for you...

This is a snip of a dataflow that uses a SQL native query and shows that query folding is taking place.

Some helpful pages on dataflows, query folding and the enhanced compute engine.

https://ssbipolar.com/2019/10/12/power-bi-dataflows-and-query-folding/ 

https://blog.crossjoin.co.uk/2021/04/18/power-bi-dataflow-performance-premium-per-user-and-the-enhan... 

https://powerbi.microsoft.com/en-us/blog/step-folding-indicators-for-power-query-online/