SQL Execute Stored Procedure v2 suddenly very slow

(0) Share

Report

Posted on by LaelH

195

Hi All,

I have a simple flow which sets up some variables, calls a SQL Stored procedure, and then sends the response back to PowerApps.

It has been running absolutely fine for months, up until 12 pm today, when it has suddenly gone from taking between 1 and 4 seconds to run, to taking up to and over 40 seconds to run. Nothing in the data has changed. In fact, the stored procedure itself in Azure SQL is still executing in under 2 seconds - so nothing has changed there. Something has gone pear in flow and I don't know what? I had another flow that corrupted totally at about the same time. It just refused to execute at all - just hung up and said "running". To get that one to succeed again I had to save a copy, delete the old, rename back to original, and then delete from Power Apps and re-import to get it right. It cam right. I tried the same with this one, but sadly it hasn't worked. At least this one is still running, but it is just dog slow.

Any ideas?

Regards

Lael

Categories:

Using connectors

I have the same question (0)

All responses (5)

Answers (1)

kierian Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hello

Is this SQL server on prem ?

If so please check gateway logs network errors or latency

is the gateway up to date?

Have good day

Was this reply helpful? Yes No
LaelH 195 on at

Like (0)

Report
Copy link

Link copied!

More on the above - it is now taking over a minute and a half - it is getting worse and worse! I am so frustrated. I noticed something weird - it seems that my environment has duplicated itself for some reason - could this be the issue? Well, not exactly duplicated, but I now seem to have two environments, both exactly the same name - one has all my flows in it, and the other is empty.
Confusion reigns supreme.
Any ideas? Help please?

Was this reply helpful? Yes No
LaelH 195 on at

Like (0)

Report
Copy link

Link copied!

Hi Kierian

No - it is Azure SQL

Was this reply helpful? Yes No
kierian Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hello

regarding environment duplication that can cause issue because you will have duplicate flows that make same calls to same sql that not expected behavior please open request with support so environment team can dig more see what's happened.

Was this reply helpful? Yes No
Verified answer

LaelH 195 on at

Like (2)

Report
Copy link

Link copied!

Hi Kierian.

It turned out to be a Parameter Sniffing issue in SQL - I didn't pick it up because whenever I tested the stored proc in SQL I replaced the parameters with local variables. Anyway, I found stacks in google on how to fix it and I am back to normal. For anyone else who has the issue, google parameter sniffing and there are lots of strategies offered to fix. What worked for me was parameter masking. Not sure if it is the most elegant solution but is solves the problem.

Was this reply helpful? Yes No