Delegation not working even though I have no warnings

[Assurity]

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 ?

[timl]

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]

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 · Accepted solution]

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]

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.