cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Resolver I
Resolver I

Help with SQL View, and Monitor reporting excessive GetRowCount

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

 
 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions

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.

 

 

View solution in original post

5 REPLIES 5
Super User III
Super User III

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.

 

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... 

 

Resolver I
Resolver I

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."
}
}

 

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.

 

 

View solution in original post

Hi @WarrenBelz 

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

Thanks a million. 

Fadi

Helpful resources

Announcements
New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

New Power Super Users

Congratulations!

We are excited to announce the Power Apps Super Users!

Power Apps Community Call

Power Apps Community Call: February

Did you miss the call? Check out the Power Apps Community Call here.

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

Top Solution Authors
Top Kudoed Authors
Users online (50,023)