Increasing timeout for LINQ to SQL stored procedure call

Question

I call a stored procedure via Linq-to-SQL. This stored procedure simply processes data that I've already inserted into another table. On large data sets, I get a timeout exception:

"Timeout expired. The timeout period elapsed prior to completion of the operation
 or the server is not responding."

I can't do anything to speed the stored procedure up -- it's just moving data from one table to another. I don't particularly want to increase the timeout in the database connection string -- this is the only thing that takes a long time.

This isn't a web app; the stored procedure is called from a background thread in a normal Windows service. The background thread is kicked off by a WCF call, and the client periodically polls for the result of the background thread.

Unfortunately, the stored procedure takes too long, and the GetDataContext().spRunStoredProcedure() call throws a TimeoutException, even though the stored procedure appears to be running fine.

Can I increase the timeout just for this stored procedure call? Or is there a way to get the stored procedure to return "I'm not dead yet" to keep the connection from timing out?

Answer 1

Since it's about moving data, and since it actually takes so long that it times out: might it be a solution to move this away from the web, and make it a scheduled SQL job?

Other than that, really, there are no queries that cannot be optimized; especially not among the ones that time out. Increasing timeout is not a very good solution. Even moving data from one table to another can usually be quite hasty. Have a look at your indexes, how you select the data, your locks, and your statistics. Run a query that's typically a long one in Management Studio with Execution Plan enabled, and see what's responsible for most of the load (it can usually be nailed down to one or two bottleneck factors) and have a look at if there's anything you can do about them.

Answer 2

On the DataContext, set the .CommandTimeout to a much higher value.