LookUp Function and Delegation (Azure SQL DB datasource)

[PaulD1]

I have the following formula as the datasource for a Gallery:

AddColumns(GrpOrderByHeader, "MyOrderHeaderID", LookUp('[Order].[OrderDetail]',GrpOrderByHeader[@OrderDetailID] = OrderDetailID, OrderHeaderID))

The following part (in the LookUp) is showing underlined in blue as non-delegable:

[@OrderDetailID] = 

For reference, GrpByOrderHeader is defined as follows:

SortByColumns(GroupBy(Filter('[Order].[OrderDetail]',
OrderDetailTimeInt >= (varTodayInt-(10000*TextInput1)))  ,
"OrderHeaderID","GrpOrderByHeader"),"OrderHeaderID",Descending)

I only actually need the data for the last few days. If I change the references in my 'AddColumns' formula for [Order].[OrderDetail] to filter the data (as below) will PowerApps delegate the filter to SQL Server and then process the (non-delegable) LookUp internally using just the filtered data?

Filter('[Order].[OrderDetail]',OrderDetailTimeInt >= varTodayInt)

I guess the crux of my question is: When one part of a formula is non-delegable, does that mean every part of the formula will not be delegated? I.e. Will PowerApps locally cache the entire [Order].[OrderDetails] table to evaluate the formula regardless of the filters I put in?

[v-monli-msft · Accepted solution]

Hi @PaulD1,

Formulas that cannot be delegated will be processed locally. All the data will brought to the device first, and has a limit on the amount of data that can be processed locally: 500 records. Since the [Order].[OrderDetails] part will be processed locally, then only the first 500 records of the million records will be scanned. If the desired record is record 501, or 500,001, it will not be considered or returned by Filter. 

For your reference:

https://powerapps.microsoft.com/en-us/tutorials/delegation-overview/

Regards,

Mona

[PaulD1]

Thanks @v-monli-msft

Fortunately, with the help of the fine people who frequent these forums, I was able to rewrite the formula so it is delegable. But it is good to know that any single element that cannot be delegated causes every part of a formula to be processed locally with the problems that causes for performance and limits to record caching.

[mseklec]

@PaulD1, can you share or link to the solution you found for this issue? I am trying to overcome the same delegation issue.

[PaulD1]

Hi @mseklec 

Although the documentation seems to suggest that using any non-delegable function within a formula makes the whole formula non-delegable this is not always the case.

For example, if I have a formula in SharePoint (where Search is non-delegable) such as:

Search(Filter(MyTable,MyField = "X"),txtMySearchText.Text,MySearchField))

Then in my experience, the Filter will run server side and then the Search will act on the records returned by the filter within the client. The innermost part of the formula is the first to run, so structure your formulas with the delegable functions inner-most.

It is worth testing by varying the Delegation limit (usually 500) in App Settings with some test data to check the behaviour when the delegation limit is exceeded.

Some things I have found:

• When a formula becomes complex enough, it often won't report whether it is delegable or not - you'll need to test.
• If your filter predicate is a claculation e.g. X<= Value(DateDiff(Today(),Now(),Hours)) that will sometimes force delegation - put the calculation into a label (or variable) and reference that (the result of the calculation) in the predicate instead and sometimes it will become delegable.
• Different data sources have different delegation capabilities (Azure SQL has the best capabilities but is moving to premium pricing which will put it out of reach for many solutions ) - there used to be a really useful matrix comparing the delegation capabilities of the most popular data sources at (or linked from) https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-overview but I can no longer find it (<cynic> perhaps it has been memory-holed to enourage uptake of CDS over Azure SQL </cynic>).
• SQL does not delegate filters on dates - use a calculated column in the SQL table (or a view) to convert the date into an integer in YYYYMMDD format (20191092) and then use that column as your filter predicate to overcome this.