1

I'm using Entity Framework 6 to access a SQL Server in an API application. The API server and DB server are separate. I have a known long query that takes a little more than a minute to run. I have set the CommandTimeout on my EF DbContext using the following code:

db.Database.CommandTimeout = 20 * 60;  // 20 minutes

Unfortunately, this does not seem to have any affect because the query still times out in less than a minute and the server returns a 504 Gateway Timeout error.

Why does this change not have any affect on the command execution time limit?

Is there something else I should be doing to allow this query to run longer?

Some more details:

I have written a desktop app that makes API calls to an API server which in turn makes queries to the db server. On the desktop application side, I have set the HttpClient property:

httpClient = new HttpClient { Timeout = new TimeSpan( 0, 20, 0 ) };

On the API server side, I have set the execution timeout:

<system.web>
    <httpRuntime requestPathInvalidCharacters="" maxRequestLength="2097151" executionTimeout="30000" />
</system.web>

And on the API server, I've set the SQL command timeout as above.

At some point, the response always ends after a little less than a minute despite the fact that I've set all the timeouts I can think of to 20 minutes. I assumed the 504 was caused by the db timeout, but I suppose it's just as likely that it's between the desktop app and the API server.

Regardless, I'm running out of ideas so anything will help.

Sparafusile
  • 4,696
  • 7
  • 34
  • 57
  • What about the timeout on the API Server? – Octanic Mar 09 '16 at 19:33
  • @Octanic I'm assuming that the timeout is with the db server since I'm getting the gateway timeout. Perhaps the wrong assumption though. – Sparafusile Mar 09 '16 at 19:40
  • Have you tried running that same command locally? That doesn't seem at all like an EF timeout error, but most likely a problem with the client app service connection timeout. – Gabriel Rainha Mar 09 '16 at 19:57
  • @Gabriel Rainha I have added more details. – Sparafusile Mar 09 '16 at 20:08
  • Can you run the API in a debugger to see if it is the SQL query that is timing out (and get any error details)? – Jack A. Mar 09 '16 at 20:29
  • @Sparafusile Gabriel Rainha got my point. Gateway timeout doesn't seems to be EF related error. Also, if it was EF error, you'd probably get inner exceptions too. I see you've tried to reach the api server and got the same problem, but, have you tried running the application directly on the API server? Or am I missing something here? Perhaps IIS is forcing timeouts? – Octanic Mar 09 '16 at 20:36
  • I have set up the API service to run locally pointing to the production database. Then I set up the desktop application to point to he local API service. Nothing timed out at all when everything was running in debug mode and the query finished in a little over 3 minutes (this is typical when access the remote db server). So I guess I need to figure out what setting is preventing this behavior in production. Any ideas? – Sparafusile Mar 09 '16 at 20:41

1 Answers1

2

Turns out there was another timeout setting I wasn't thinking about. Because I host the API and database servers on Amazon Web Services using Elastic Beanstalk, there was a Load Balancer between my desktop application and the API server. The default timeout is 60 seconds. Here is a link to increase the Load Balancer's timeout settings:

http://docs.aws.amazon.com/ElasticLoadBalancing/latest/DeveloperGuide/config-idle-timeout.html

Sparafusile
  • 4,696
  • 7
  • 34
  • 57