cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Assurity
Frequent Visitor

Delegation not working even though I have no warnings

Hi,


My PowerApp has no delegation warnings yet when I run the azure sql profiler I can see the queries are always getting all the records rather than what I have requested. Take the following formula as an example : 

ClearCollect(LocalAsbestosItem,Filter('[dbo].[AsbestosItem]',ReportDraftID = GalleryReportList.Selected.ReportDraftID));

When I check the azure sql profiler I can see that its not delegated as I can see the SELECT TOP 300 * ... (I have my data row limit set to 300) in the query : 

select top 300
[$Ordered].[AsbestosID],
[$Ordered].[ReportDraftID],
[$Ordered].[ReportNumber],
[$Ordered].[Building],
[$Ordered].[Floor],
[$Ordered].[Room],
[$Ordered].[Item],
[$Ordered].[RefNo],
[$Ordered].[RASMaterial],
[$Ordered].[RASActivity],
[$Ordered].[RASDisturbance],
[$Ordered].[RASHumanExposure],
[$Ordered].[RASMaintenance],
[$Ordered].[RASPriorityAssessment],
[$Ordered].[RASRiskRating],
[$Ordered].[AssessmentDate],
[$Ordered].[AssessmentTeam],
[$Ordered].[ACMCondition],
[$Ordered].[Comments],
[$Ordered].[ACMVulnerable],
[$Ordered].[ReviewFrequency],
[$Ordered].[ACMVulnerableOptionPermit],
[$Ordered].[ACMVulnerableOptionRestrict],
[$Ordered].[ACMVulnerableOptionContact],
[$Ordered].[ACMVulnerableOptionReview],
[$Ordered].[ACMVulnerableComments],
[$Ordered].[LastModifiedUser],
[$Ordered].[LastModifiedDate],
[$Ordered].[CreatedUser],
[$Ordered].[CreatedDate],
[$Ordered].[FurtherActionRequired],
[$Ordered].[Completed],
[$Ordered].[IsDeleted]
from
(
select [_].[AsbestosID],
[_].[ReportDraftID],
[_].[ReportNumber],
[_].[Building],
[_].[Floor],
[_].[Room],
[_].[Item],
[_].[RefNo],
[_].[RASMaterial],
[_].[RASActivity],
[_].[RASDisturbance],
[_].[RASHumanExposure],
[_].[RASMaintenance],
[_].[RASPriorityAssessment],
[_].[RASRiskRating],
[_].[AssessmentDate],
[_].[AssessmentTeam],
[_].[ACMCondition],
[_].[Comments],
[_].[ACMVulnerable],
[_].[ReviewFrequency],
[_].[ACMVulnerableOptionPermit],
[_].[ACMVulnerableOptionRestrict],
[_].[ACMVulnerableOptionContact],
[_].[ACMVulnerableOptionReview],
[_].[ACMVulnerableComments],
[_].[LastModifiedUser],
[_].[LastModifiedDate],
[_].[CreatedUser],
[_].[CreatedDate],
[_].[FurtherActionRequired],
[_].[Completed],
[_].[IsDeleted]
from [dbo].[AsbestosItem] as [_]
where [_].[ReportDraftID] = '6e8e4fdf-2f7e-4a93-b28d-00b8aafa5af9'
) as [$Ordered]
order by [$Ordered].[AsbestosID]


This is happening in various places in my PowerApp and is making it slow in some parts. How can I fix these sort of problems as I thought the PowerApp designer is supposed to flag these delegation issues ?

1 ACCEPTED SOLUTION

Accepted Solutions
timl
Super User
Super User

Hi @Assurity 

To clarify, ClearCollect will collect no more than the number of rows that are specified in the data row limit setting.

 

Strictly speaking, ClearCollect is delegable. That is, Power Apps will correctly 'delegate' the query expression to SQL Server, and SQL Server will filter the data as required. In your SQL profiler trace, the SELECT TOP 300 should be accompanied by a WHERE clause that matches records based on ReportDraftID.

 

If ClearCollect were not delegable or if we were using a non-delegable query expression, we would see no WHERE clause in SQL profiler, and Power Apps would carry out the filtering client-side.

View solution in original post

4 REPLIES 4
timl
Super User
Super User

Hi @Assurity 

ClearCollect will not retrieve in one go, more than the number of records that are specified in the data row limit setting. That's the reason for this behaviour.

If you were to set the Items property of a gallery to directly to your formula, the expression would be delegable. That is, the gallery will load all of the records in batches as you scroll down the gallery.

Filter('[dbo].[AsbestosItem]',ReportDraftID = GalleryReportList.Selected.ReportDraftID)
Assurity
Frequent Visitor

Hi @timl 

 

Thanks for your response. The App is designed to work offline, so I am loading the data into collections first, and using the collections in my galleries and other controls. Are you saying that ClearCollect does not support delegation and will just go for the maximum number of the data row limit setting ?

timl
Super User
Super User

Hi @Assurity 

To clarify, ClearCollect will collect no more than the number of rows that are specified in the data row limit setting.

 

Strictly speaking, ClearCollect is delegable. That is, Power Apps will correctly 'delegate' the query expression to SQL Server, and SQL Server will filter the data as required. In your SQL profiler trace, the SELECT TOP 300 should be accompanied by a WHERE clause that matches records based on ReportDraftID.

 

If ClearCollect were not delegable or if we were using a non-delegable query expression, we would see no WHERE clause in SQL profiler, and Power Apps would carry out the filtering client-side.

Assurity
Frequent Visitor

Thanks @timl . That makes some sense to me. I did some further testing and could see what you described happening on my PowerApp. This should not affect the performance of my app as the number records I need are within the data row limit setting.

Helpful resources

Announcements
Super User 2 - 2022 Congratulations 768x460.png

Welcome Super Users

The Super User program for 2022 - Season 2 has kicked off!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

<
Users online (4,565)