Show 20L records in Power Apps

(1) Share

Report

Posted on by BenWip

Hi Team,

Good day! We are planning to create a power apps which shows around 20L record from Oracle on-prem table. We are passing name and department to get the data from Oracle on-prem table. The result data contains around 20L record. Will it be possible to show in Power Apps ?

Categories:

Building Power Apps

I have the same question (0)

All responses (10)

Answers (0)

Suggested answer

jrletner 720 Super User 2025 Season 2 on at

Like (2)

Report

This could be an issue with Power Apps and the delegation issues for handling 20 million rows of data. By default, Power Apps has a limit set to 500 records but can be maxed out at 2,000 records.

You would need to implement some filtering of your data and/or sever-side pagination, most likely through a stored procedure. That way you can send Power Apps your data in chunks as the user needs it.

Was this reply helpful? Yes No
BenWip 55 on at

Like (1)

Report

Hi @jrletner,

Thanks for your reply. Can you explain more on how to get the data in chunks using stored procedure ?

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

Like (1)

Report

Why would you want to show all of these rows at one time?

What is the nature of your application?

Was this reply helpful? Yes No
Suggested answer

SaiRT14 1,990 Super User 2025 Season 2 on at

Like (1)

Report
pls try the following:

Displaying such a large dataset (2 million records) directly in Power Apps is challenging due to both technical limitations and performance constraints. Power Apps is not optimized to handle massive datasets

Power Automate (Flow) can handle large data volumes better than Power Apps and can connect to Oracle on-prem via a gateway.

Use Power Automate to fetch and filter the data in smaller chunks and send it back to Power Apps, either as a paginated response or by pre-filtering to the top 500 records.

Was this reply helpful? Yes No
BenWip 55 on at

Like (1)

Report

Hi @ronaldwalcott,

We no need to show all the data at once, we needed to show the data in below format. Please see the screenshot below,

Was this reply helpful? Yes No
rulesrchanged1 on at

Like (2)

Report

So the best way to do this is doing server side pagination and getting data in a reasonable page size like 10-50 rows at once. Your UI requirement also shows pagination, so this will work well.

Now, to do pagination, straight up getting data from oracle table, won't provide you that kind of functionality, so two options which i can think off is

a) Use a stored proc in oracle and then use power automate to fetch data.

b) You can actually use dataverse virtual tables (Create virtual tables using virtual connectors (Microsoft Dataverse) - Power Apps | Microsoft Learn) which supports oracle as a virtual table type. The benefit of this would be that it will create a simple API layer for you to query data with pagination and also the OOB components of Power Apps (like the modern list which already does pagination) will just automatically work.

Was this reply helpful? Yes No
BenWip 55 on at

Like (0)

Report

Hi @rulesrchanged1 ,

Thanks for your response.

1.Regarding first approach, can you explain bit more on this? Are you meant to say use Power automate to get first 50 data , when user navigate to next page then again run the flow get next 50 data and pass it to Power Apps.

2. Regarding virtual tables, if we data gets updated in oracle table, will it reflect in Dataverse and vice versa?

Was this reply helpful? Yes No
BenWip 55 on at

Like (0)

Report

Hi @SaiRT14,

Thanks for the reply. I need to show data in below format. Can please let me know how we can use power automate to get data from oracle and show in power apps ?

Was this reply helpful? Yes No
Suggested answer

ronaldwalcott 3,847 Super User 2025 Season 2 on at

Like (0)

Report

Let me provide an unpopular opinion.

When working with data you are never retrieving more than a pageful of data (however you set your paging sizes) at one time.

You only retrieve a large number of records if you want to cache data.

The number of records in the database has minimal effect on your ability to read data in a timely manner.

The ability to access the data in a timely manner effectively depends on the capabilities of the Oracle connector.

The Oracle connector provides you with the functionality to read and write data while managing paging in galleries.

The connector has the limitation of an inability to work with compound keys so you have to analyze your database structure to determine if the required functionality can be met.

Accessing data by a primary key will always give you faster queries.

Most functions have the same delegable functionality as working with Dataverse.

You should be able to execute SQL queries but not stored procedures (unconfirmed, could have changed).

You need to install the connector and start testing.

Was this reply helpful? Yes No
rulesrchanged1 on at

Like (0)

Report

@benwip -

for #1, yes basically using automate to implement pagination, something like this on DB side Top-N Queries and Pagination — Oracle SQL & PL/SQL Optimization for Developers 3.1.0 documentation

for #2, yes virtual tables are essentially pass through, so every time data is retrieved, it is fetched directly from DB. Similarly when u make changes, it will directly make the changes in DB. Its major benefit is providing you a ready-made API layer to be used.

Was this reply helpful? Yes No