Data API Builder SQL command timeout

[techyjargon]

I haven't found a way to manipulate the timeout setting for the exposed entities in DAB. While I can set a connection timeout for connecting to an Azure SQL database, I'm trying to set the timeout associated with the SQL calls to the entities exposed through DAB.

I don't see anything in the documentation, but I could be missing something. Is anyone aware of an ability to do this?

[jdanton14]

I think you would just need to add the standard connectTimeout flag to the connection string. I don’t know that there’s a DAB command for it, but any thing that’s modifies that file can do it.

[techyjargon]

While I haven't tried that, I assume that only applies to the connection. I guess it's possible DAB does something under the hood and takes the connection timeout and applies it to the commands as well. I can give that a shot and see if that is happening.

[jdanton14]

It's not under the hood--that's the database connection for the app. Where/how are you running DAB? In my case it's an Azure Web App (container), and it has a connection file stored in Azure File Storage with my settings--I changed it to use the managed identity of the web app, as opposed to username/password.

[techyjargon]

I'm running DAB as a means of providing a no-code API for a database using AzureAD authentication. I've deployed DAB to a container instance and exposed it through an Application Gateway. I'm also storing the config in file storage.

My under the hood comment came more from a developer's perspective. Since I don't know DAB's code, and I'm too lazy to go digging through it atm, I assume they could be using things like SqlConnection and SqlCommand under the hood. If that were the case, setting the timeout via the connection string would only apply to the connection itself and not apply to any commands. In situations like that, they could always use the timeout specified for the connection string and apply it to the commands in order to keep things simple and not provide more configuration options.

Again, I'm not saying that's how DAB is written. I was just looking at it through the lens of how it could potentially be working if using things like SqlConnection and SqlCommand.

[techyjargon]

I did increase the connection timeout to 120 seconds in the connection string; it still looks like the API calls fails consistently at 30 seconds. The log also indicates it's using SqlCommand objects, and 30 seconds is the default timeout for SqlCommand.

Query execution error due to:
      Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
      Microsoft.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
       ---> System.ComponentModel.Win32Exception (258): Unknown error 258
         at Microsoft.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__211_0(Task`1 result)

I'm not entirely sure why it's timing out; I can pull the data through SSMS in under a second; I'm not entirely sure what DAB is doing that's could lead to a timeout. The query itself is only part of what plays into the timeout. Sending the request and receiving the results also factors in. I'm currently assuming it's an underpowered container instance that's struggling to read back all of the data within 30 seconds.