Power Platform Community Forum Thread Details

I've been roaming this community posts for days now and I can't find a clue to what I am doing wrong.

I'm Building an app and need some data from an sql server. Hence I have a on-prem gateway. The gateway and the database server are on different VM's within the same network.

I need limited columns to be returned from the sql server's database and thus want to save memory of the app by not retrieving all columns, also I need data from multiple tables thus I have created views that return exactly what I need.
When I select the data from the view, select id, attributeName, attributeValue from view where id= 'someId';

it runs in a micro second to return 30 ish rows.

When retrieving the same data through powerapps it takes aprox = 5000ms so like 5 seconds.

In the powerapp I have tried datatable, gallery and all kinds of controls. Also played around with FILTER, SEARCH, STARTSWITH etc. but no differences in performance.

Also I have added monitoring on the gateway server.
Here is the query as listed in the monitoring (https://learn.microsoft.com/en-us/data-integration/gateway/service-gateway-performance)

Executing query (timeout=109) "<ccon>[ AzureConnection = [authenticationType="SQLAuth",gateway="true",server="SQL2",database="someDatabase"],
request = [Connection = AzureConnection],
dataSet = "SQL2,someDatabase",
table = "[dbo].[app_serie_attributes]",
options = [Top=100,Filter=()=>each ([id] = "DR0941-5878"),Select={"id"}],

ITableDataProvider.ListItems = Sql[ITableDataProvider][ListItems],

return = ITableDataProvider.ListItems(request, dataSet, table, options)
][return]
</ccon>", requestId=44149517-fa59-4dcb-9790-b5426a765031

The stats are shown like following. you can see it takes total of 4 seconds in duration but most of the underlying stats are not showing execution times.:

I just don't understand how such fast queries with datareading of 134 ms can take total 4 seconds to execute.
What bottleneck am I missing? slow authentication? issue with performance of ITableDataProvider.ListItems

Categories:

Building Power Apps