Sql Server and Canvas Apps

(0) Share

Report

Posted on by gikido

1,002

Good evening everyone

I am developing a power apps application for a client, the database is in SQL Server, after the data becomes large more than 100,000 records (Tables and views) the client starts to complain about the slowness of data retrievals, even if we have increased the performance of the database the slowness remains and we have to wait even 5 minutes to retrieve the data.

I tried several methods like using collections but it didn't change anything because the max for collections is 5000 lines.

My question is: what are the solutions to solve this problem? Thanks in advance

Categories:

Power Apps Pro Dev & ISV

I have the same question (0)

All responses (4)

Answers (0)

Mark Nanneman 991 Super User 2025 Season 2 on at

Like (0)

Report

What's the use case for having thousands of rows in the app? Is it absolutely necessary or can you just retrieve much smaller groups as needed with delegated filtering/searching? Usually you wouldn't need that many rows loaded.

Was this reply helpful? Yes No
gikido 1,002 on at

Like (0)

Report

So how do I load batch by batch? Or do you have another solution?

Was this reply helpful? Yes No
Mark Nanneman 991 Super User 2025 Season 2 on at

Like (0)

Report

It all depends on what kind of data you are storing and what you're doing with it. If you have to run some sort of calculations or graphing on thousands of rows, a canvas app probably isn't the right solution to start with.

You might need to rethink what the purpose of the app is for. The idea would be to come up with a plan for how to filter and search for only the records in the database that the user needs to do something with. Is the app to show the user brand new rows/records to approve? Is it to show them aging records to decide to keep or delete? Is it to show them records flagged for manual review? Is it so they can search up any record they want and modify it?

In any of these cases you want to just leave as much of the database as possible in the server outside the app. Only pull in what you need when you need it.

For example, if you have a gallery control that displays some items from your database, make a good filter and/or search function on the datasource.

Something like "Sort(Search(Filter(<your data source>,<data source column to filter> = <your filter value>),txt_search_field.Value,<data source column to search>),<data source column to sort>, SortDirection.Descending)"

Whenever you change the search field input, or change the filter control selection or input, it will trigger the app to refresh the datasource, and send the search and/or filter conditions to the external server to return the data. As long as your filter/search formula is delegable, the server will handle it and only return what you need.

Was this reply helpful? Yes No
ronaldwalcott 3,847 Super User 2025 Season 2 on at

Like (1)

Report

In addition, think about using stored procedures for some operations.

Provide details on the functionality that you are trying to achieve which requires retrieving a large number of records that cannot be solved using a stored procedure.

Was this reply helpful? Yes No