How does performe quering to a local database through a data gateway vary versus a cloud database?

(1) Share

Report

Posted on by bslahsen

I have a performance issue associated with a Power Apps application that through Power Automate flows queries an On-premise SQL database through a Gateway. I would like to know if this slowness is mainly due to the architecture of the solution. I have already tried running the Tscipts in SQL where the queries take milliseconds and the cost is very low. I can only think that this slowness is due to the fact that the database is not of cloud type.

Any comments on this matter?

Thank you very much.

Categories:

Connecting to Data

All responses (4)

Answers (0)

lbendlin 7,794 Super User 2025 Season 1 on at

Like (0)

Report

How does performe quering to a local database through a data gateway vary versus a cloud database?

Please provide more details.

- what is the setup of the gateway? How many cluster members? How many cores each? How much memory? How far are they from the database (network latency)?

- what's the payload size? (both the query text and the returned data)? Do you observe query caching in the SQL server?
Suggested answer

timl 34,933 Super User 2025 Season 1 on at

Like (0)

Report

How does performe quering to a local database through a data gateway vary versus a cloud database?

Hi bslahsen

All things being equal, performance should be slightly better with SQL Server Azure. This is due to the reduced latency in needing to connect to the gateway, and then from the gateway to your actual SQL server.

If you can give us further details, perhaps we can give other suggestions to improve performance. For instance, I assume you're using a Flow to execute dynamic SQL. If you can extract some of this logic into SQL Views, you could connect directly to data without going through Power Automate which would improve performance. If you were to consume this View data through a gallery, Power Apps can batch the data retrieval and it can minimise data transfer through the use of explicit column selection. This 2 features would further help performance.
bslahsen 23 on at

Like (0)

Report

How does performe quering to a local database through a data gateway vary versus a cloud database?

FLMike, this question is oriented to the speed that a SQL-on premise configuration has versus a cloud-type SQL configuration, regardless of the code. I am just asking wich is faster
Suggested answer

Michael E. Gernaey 40,786 Super User 2025 Season 1 on at

Like (0)

Report

How does performe quering to a local database through a data gateway vary versus a cloud database?

Hi

There isn't a way for us to tell you that.

1) We are not able to verify your configuration

2) we are not able to verify your Power Apps to Power Automate code

3) we cannot verify your On Premise Gateway

and so on, you get the picture.

However, what you should be doing, if its a local Database is (if its sql) running profiler, and then run your App and Flow

This way you can do 2 things

1) Look in your Flow run at each action and see how long each took

2) you can look at the profile created to see why things took a long time OR if they didn

Thats how you find it.