Azure function, slooooow.

[Einstein Jnr]

What if you run a logic app with sql connector instead of the function?

[DimPrawn]

Move it all to AWS Lambda, M$ suck.

[BlueSharp]

The only other thing I can think of is a none azure specific issue such as a bad execution plan OPTION(RECOMPILE) on the azure function call to the proc or corruption in the buffer.

Edit: Are the database indexes and statistics fresh?

https://docs.microsoft.com/en-us/sql...s-transact-sql

Failing that try turning turing sql server on and off again if you can get a window to do that.

[woohoo]

The only other thing I can think of is a none azure specific issue such as a bad execution plan OPTION(RECOMPILE) on the azure function call to the proc or corruption in the buffer.

Edit: Are the database indexes and statistics fresh?

https://docs.microsoft.com/en-us/sql...s-transact-sql

Failing that try turning turing sql server on and off again if you can get a window to do that.

I think it's very likely to be something related to the statistics/dodgy plan.

I think Mudskipper might be onto something with SET ARITHABORT ON. I've read a couple of answers on SO and people say it's sorted their problem out then others have pointed out that it may be a red herring and it's actually a dodgy plan.

I did try setting SET ARITHABORT ON in the stored procedure but no difference. But from what I've read the actual connection from ado.net trumps the stored procedure settings. So I'm going to attempt to change the code in the Azure function to set this on for the connection. Working with live data so will make the change and allow it to run in the morning.

I'm limited on time at the moment so going for the the low hanging fruit. But my feeling is it's actually not related to the function at all and as you have said it's db.

[mudskipper]

I think it's very likely to be something related to the statistics/dodgy plan.

I think Mudskipper might be onto something with SET ARITHABORT ON. I've read a couple of answers on SO and people say it's sorted their problem out then others have pointed out that it may be a red herring and it's actually a dodgy plan.

I did try setting SET ARITHABORT ON in the stored procedure but no difference. But from what I've read the actual connection from ado.net trumps the stored procedure settings. So I'm going to attempt to change the code in the Azure function to set this on for the connection. Working with live data so will make the change and allow it to run in the morning.

I'm limited on time at the moment so going for the the low hanging fruit. But my feeling is it's actually not related to the function at all and as you have said it's db.

There's also the parameter sniffing stuff. I changed my SPs to assign the passed parameters to local variables and use them in the query. Can't be sure whether it made a difference - my comment said " -- This might help... "

[woohoo]

There's also the parameter sniffing stuff. I changed my SPs to assign the passed parameters to local variables and use them in the query. Can't be sure whether it made a difference - my comment said " -- This might help... "

hah yeah, I'm currently reading this about parameter sniffing...

Slow in the Application, Fast in SSMS?

[mudskipper]

I did try setting SET ARITHABORT ON in the stored procedure but no difference. But from what I've read the actual connection from ado.net trumps the stored procedure settings. So I'm going to attempt to change the code in the Azure function to set this on for the connection. Working with live data so will make the change and allow it to run in the morning.

At the risk of stating the bleedin' obvious, you could try setting to OFF in the SP and see if it runs slow in SSMS - would at least indicate whether it's worth pursuing.

https://docs.microsoft.com/en-us/sql...t-transact-sql

[woohoo]

I’ve read the article before thanks.

Just as easy to change the azure function. I’m having a day off and will take a look at it Sunday.

[woohoo]

Just in case anyone is interested. The Azure function timing reduced itself from 6 mins to 17 seconds without changing anything.

I didn't get a chance to deploy the change to the Azure function to SET ARITHABORT ON for the login session but if it helps someone else here is the code I pinched and tested.

                
conn.StateChange += new StateChangeEventHandler(OnStateChange);
conn.Open();

        
private static void OnStateChange(object sender, StateChangeEventArgs args)
{
    if (args.OriginalState == ConnectionState.Closed
        && args.CurrentState == ConnectionState.Open)
    {
        using (SqlCommand cmd = ((SqlConnection)sender).CreateCommand())
        {
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SET ARITHABORT OFF;";

            cmd.ExecuteNonQuery();
        }
    }
}

[mudskipper]

The Azure function timing reduced itself from 6 mins to 17 seconds without changing anything.

Most unsatisfactory The black art of databases.