SQL connector incredible slow

[JCD-JTP]

Hi,

I'm using the SQL connector to connect to an Azure PaaS SQL database.

But it's like every times i need to get/save data, it's a role with the dice if it's quick og slow as he..

In this example i'm updating three rows, in a table with six columns, using the Patch function.

Using the monitor function, I can see the following execution times:

Row1: 3.8 sec

Row2: 0.49 sec

Row3: 0.29 sec

It's the same data in the three rows.  Trying again:

Row1: 0.07 sec

Row2: 0.06 sec

Row3: 0.17 sec

It's not always the first row that is slow...

It's making the app more or less useless, as we in production have a lot of data and sometimes waiting 10 minutes for the data to be saved..

Are the SQL connecter just that bad, or is it working as designed? Then we might have to look else ware for our customers.

[RandyHayes]

@JCD-JTP

Performance of your app is going to depend a lot on your formulas you are using.  How are you Patching the records?

There is caching involved with all the connectors.  Keep in mind that your app is not really interacting directly with the datasource.  It works through a session in the cloud, that then work with (in your case for SQL) a connector to the actual datasource.  So, some of the actions and the responses are cached for short periods of time.  In other words, it is not unusual to see a long time for one action and then a very short time for the same action directly after it.

I hope this is helpful for you.

[JCD-JTP]

@RandyHayes 

Hmm.. I have just loaded the full app, and it takes roughly 10 minutes to load.

Looking at the monitor, I have a lot of getRows to fetch data, but many of them looks like this:

The request:

{
  "url": "https://europe-002.azure-apim.net/apim/sql/db1cb7ed747e4e1f9e24b7bd7bdd051c/v2/datasets/XXXXXX.database.windows.net,XXXXXX/tables/%255Bdbo%255D.%255BReportsOnCall%255D/items?%24filter=Id+eq+b43d19aa-124d-4656-84cb-e5cbfb905aa3&%24top=500",
  "method": "GET",
  "headers": {
    "Accept": "application/json",
    "x-ms-protocol-semantics": "cdp",
    "x-ms-pa-client-custom-headers-options": {
      "addCustomHeaders": true
    },
    "x-ms-pa-client-telemetry-options": "paclient-telemetry {\"operationName\":\"CdpGetRowsFunction.GetRows\"}",
    "x-ms-pa-client-telemetry-additional-data": {
      "apiId": "/providers/microsoft.powerapps/apis/shared_sql"
    }
  },
  "body": ""
}

And then the response:

{
  "duration": 44493.59,
  "size": 234,
  "status": 200,
  "headers": {
    "Cache-Control": "no-cache,no-store",
    "content-encoding": "gzip",
    "Content-Type": "application/json; charset=utf-8; odata.metadata=minimal",
    "Date": "Thu, 17 Mar 2022 08:38:40 GMT",
    "expires": -1,
    "pragma": "no-cache",
    "strict-transport-security": "max-age=31536000; includeSubDomains",
    "timing-allow-origin": "*",
    "vary": "Accept-Encoding",
    "x-content-type-options": "nosniff",
    "x-frame-options": "DENY",
    "x-ms-apihub-cached-response": true,
    "x-ms-apihub-obo": true,
    "x-ms-request-id": "a47156de-be69-409c-b604-dc9c80bd201f",
    "x-ms-user-agent": "PowerApps/3.22031.21 (Web Player; AppName=935464df-db4b-4dea-a010-ea2bb59652c7)"
  },
  "body": {
    "@odata.context": "https://europe-002.azure-apim.net/apim/sql/db1cb7ed747e4e1f9e24b7bd7bdd051c/$metadata#datasets('XXXXXX.database.windows.net%2CXXXXXXX')/tables('%5Bdbo%5D.%5BReportsOnCall%5D')/items",
    "value": []
  },
  "responseType": "json"
}

44.5 sec to return 0 rows... Thats alot.. And looking at the SQL performance for the given time:

It looks like it's doing nothing..

[RandyHayes]

@JCD-JTP 

It's going to really depend a lot on your formulas first.

It will also depend on - are you querying the tables directly or are you querying a view in your database?  If it is a view, has it been optimized properly?