Handling Web Service Timeouts While Performing Long-Running Database Tasks

Question

The architecture of one of our products is a typical 3-tier solution:

• C# client
• WCF web service
• SQL Server database

The client requests information from the web service. The web service hits the database for the information and returns it to the client.

Here's the problem. Some of these queries can take a long, long time, and we don't know up-front which ones will be slow. We know some that are often slower than others, but even the simplest requests can be slow given enough data. Sometimes uses query or run reports on large amounts of data. The queries can be optimized only so far before the sheer volume of data slows them down.

If a query in the database hits the maximum query timeout in SQL server, the database query terminates, and the web service returns an error to the client. This is understood. We can handle these errors.

The client is waiting for the web service call to complete. If the database call takes a long time, the client may timeout on its call to the web service. The client gives up, but the database request continues processing. At this point, the client is out-of-synch with the database. The database call may or may not succeed. There may have been an error. The client will never know. In some cases, we don't want our users initiating another request that may result in an invalid state given the completion of the previous request.

I'm curious to see how others have handled this problem. What strategies have you used to prevent web service timeouts from affecting database calls?

The best ideas I can come up with involve making an actual database layer somewhere-- inside the web service, attached to a message queue-- something. Offloading every single query to another process seems excessive. (Then again, we don't always know if a given request will be fast or slow.)

It would be great if we could separate the act of making an HTTP request from the act of initiating and running a database process. I've seen this done with a custom server at a previous company, but it was using straight socket communication, and I'd rather avoid replacing the web service with some custom application.

Note that given the amount of data we deal with, we are all over query optimization. Query optimization, indexes, etc., only takes you so far when the volume of data is high. Sometimes things just take a long time.

Answer 1

I've encountered similiar problems in the past, and used one of the following 3 methods to resolve it:

1. Add all long running queries to a queue, and process these sequentially.
   In my case these were all complicated reports which where then emailed to the client, or which were stored in permanent 'temporary' tables, for viewing by clients after they had been notified.
2. We called a webservice using a JQuery call, which then called a javascript postback method when it was complete.
   This worked well when we didn't want to make the page load synchronise with what the web service was doing.
   However it did mean that that piece of functionality was not available until the long running process was complete.
3. The most complicated one.
   We popped up another window which displayed a progress bar, which also polled the server periodically.
   This used a session variable to determine how far along to show the progress bar.
   After the progress bar was initiated, a new thread was started which updated the same session variable periodically.
   Once the session variable value was set to 100, the popup window closed itself.
   The clients loved this method.

Anyway I hope one of those is of some help to you.

Answer 2

The web service could run the queries in a threadpool and if the thread does not finish within, say 5 seconds (see Thread.Join()), the web service call returns the client a JobID instead of the result set which the client can then use to poll the server every few seconds to see if its query finished. When a thread finishes the results can be stored in a hash table until the client polls again.