cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
LaelH
Advocate III
Advocate III

SQL Execute Stored Procedure v2 suddenly very slow

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.

LaelH_0-1618837636760.png

LaelH_2-1618838170229.png

 

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

1 ACCEPTED SOLUTION

Accepted Solutions

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.

View solution in original post

5 REPLIES 5
kierian
Community Support
Community Support

 

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

LaelH
Advocate III
Advocate III

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?

Hi Kierian

 

No - it is Azure SQL

kierian
Community Support
Community Support

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.

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.

View solution in original post

Helpful resources

Announcements
Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Users online (2,753)