group by delegation

[beniwal]

hi,

I am using the below formula to get the data from my SQL server.

GroupBy('[dbo].[Notes]',"email","grouped")

My data row limit is set to 2000.

So now the problem is that the above-mentioned formula is grouping only 2000 records and showing it. Moreover, it's not even giving any delegation warning.

Please guide me how to overcome this problem.

Thanks,

Mohit

[shailendra74]

Hi @beniwal 

As you are using SQL server you should be writing stored procedure /views to get the data as required with further filter criteria. Also what are you expecting user to do by fetching 2000 records. Instead use pagination.

This way your app will also have better performance.

Thanks.

[beniwal]

hi @shailendra74 

I am already using a view. The problem is that it has around 30,000 records.

Even after filtering, I expect more than 2000 records. But Group by function is getting applied only on first 2000.

[shailendra74]

Hi @beniwal 

You should use filter if possible, other option is to go with paging, where you need to define PageSize and PageNumber

CREATE PROCEDURE dbo.Pagination_Test
  @PageNumber INT = 1,
  @PageSize   INT = 100
AS
BEGIN
  SET NOCOUNT ON;
 
  SELECT CustomerID, FirstName, LastName,
      EMail, Active, Created, Updated
    FROM dbo.Customers_I
    ORDER BY CustomerID
    OFFSET @PageSize * (@PageNumber - 1) ROWS
    FETCH NEXT @PageSize ROWS ONLY OPTION (RECOMPILE);
END
GO

Thanks.

[v-yuxima-msft]

Hi @beniwal 

You could take a try to first collect the table into a collection with delegable functions, then working with the collection with the non-delegable function.

For example:

ClearCollect(Tmp, SQLTableName)

then use the Tmp to perform filters or calculations or group by in App.

Take a look at the article below for the Delegable functions for SQL Server and the other data sources:

Delegable data sources

Besides, you may follow the steps mentioned by mr.Dang through the following link:

Overcoming the 500 record limit locally

Best Regards.

Yumia

[seadude]

Hi @shailendra74 ,

Can you explain a bit more about using pagination? I'm familiar with SQL views to pare down large data sets, but this is my first exposure to pagination. What are the effects of using an SP in this way as opposed to a view?

Thanks for your expertise!

[shailendra74]

Hi @seadude 

As you see the sample stored proc, you can pass current page and page size. Along with these, you can add more params and pass the same, so that the data which is fetched and show on PowerApps is minimal and overall performance is maintained.  All the processes will be done on DB side is another advantage considering the app can be accessed on a tablet too.

We will having view with joins and basic filters like status. Other than these rest filters will be based on what is sent or applied through app, which is not same with stored proc.

Also showing too many records in your app might not help end users.  

[seadude]

Hm. I'm going to have to check more into that. Thank you for the sample code and explanation.