Help with SQL View, and Monitor reporting excessive GetRowCount

[3ation]

Hi Guys

We have a view vwMyTasks, which returns the number of records that belong to me, When I execute the View, I get 43 records, which is exactly what I expect. 

I want to get the Modified At from the top task from this list in powerApps, So have been using the following command

Set(  MyTask,

First(

SortByColumns(

Filter(

'[dbo].[vwMyTasks]',

MyName in AssignTo

),

"ModifiedAt",

Descending

)

).ModifiedAt

);

When I run this through the monitor, it reports:

Requested 2000 records, Received 2000 rows.

Formula Not delegated, 43 rows scanned.

Why would it return 2000 records, when the view only returns 43?

Thanks in Advance

[WarrenBelz]

Hi @3ation ,

This should return one value in your Variable and avoid Delegation issues

With(
   {
      wAssigned:
      Sort(
         Filter(
            '[dbo].[vwMyTasks]',
            MyName in AssignTo
         ),
         ModifiedAt,
         Descending
      )
   },
   Set(
      MyTask,
      First(wAssigned).ModifiedAt
   )
)

2,000 records are the maximum that can be returned with a non-Delegable query and First() is not Delegable.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[3ation]

Hi @WarrenBelz ,

Thanks for your suggestion, unfortunately, both queries have the same impact, the issue it seems monitor says, it received 2000 Rows, from a a SQL Server view that returns only 43 records, and Delegation still says Formula not delegated... 

[3ation]

Hi @WarrenBelz 

By modifying your query, We no longer get the Delegation warning! THANK YOU for that! 

I only moved the Sort our fo the Filter, and into the First Call... 

With( { wAssigned:Filter( '[dbo].[vwRelevantTaskListLastModifiedDate]', ThisUserPrincipalName in AssignTo ) }, Set(CurrentLastModifiedAt, First( Sort( wAssigned, ModifiedAt, Descending )).ModifiedAt ) ) ;

Regarding the GetRowsCount, I think this is a Microsoft Monitor issue, 

We are not calling GetRowCounts, And when I look at the details of it, I see the following:

{

"status": null,

"duration": null,

"dataSource": "[dbo].[vwRelevantTaskListLastModifiedDate]",

"responseSize": null,

"controlName": "RefreshTaskListTimer",

"propertyName": "OnSelect",

"nodeId": 2,

"formulaData": {

"script": "",

"spanStart": null,

"spanEnd": null

},

"data": {

"context": {

"entityName": "RefreshTaskListTimer",

"propertyName": "OnSelect",

"nodeId": 2,

"id": 4240,

"diagnosticContext": {

"dataOperation": {

"protocol": "cdp",

"operation": "showGetRowsCount",

"apiId": "/providers/microsoft.powerapps/apis/shared_sql",

"dataSource": "[dbo].[vwRelevantTaskListLastModifiedDate]",

"table": "[dbo].[vwRelevantTaskListLastModifiedDate]",

"operationName": "getRowsCount"

}

}

},

"info": "Requested 2000 rows. Received 2000 rows."

}

}

[WarrenBelz · Accepted solution]

Hi @3ation ,

It should work either way (with the filter), note however that the With() statement is not Delegable in so much as it will only return up to 2,000 records after using the filter (but give no warning), however if you know this will always be the case then you get rid of the Delegation warning. I wrote a blog on this subject that may be of interest to you.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[3ation]

Hi @WarrenBelz 

Great article, Keep up the great support you giving us. 

Thanks a million. 

Fadi