Increasing the latency of a LINQ to SQL stored procedure call

Question

I am calling a stored procedure through Linq-to-SQL. This stored procedure simply processes the data that I have already inserted into another table. On large datasets, 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 up the stored procedure - it’s just moving data from one table to another. I especially do not want to increase the latency in the database connection string - this is the only thing that takes a lot of time.

This is not a web application; The stored procedure is called from a background thread in a regular Windows service. The background thread is started by calling WCF, and the client periodically polls the result of the background thread.

Unfortunately, the stored procedure takes too much time, and the call GetDataContext().spRunStoredProcedure()invokes TimeoutException, although the stored procedure works fine.

Can I increase the timeout only for this stored procedure call? Or is there a way to return the stored procedure "I'm not dead yet" to keep in touch with the timeout?

Answer 1

In the DataContext, set the .CommandTimeout property to a much higher value. The default for SQL Server is 30 seconds, and you can set it to 0 so that it is not a timeout.

Answer 2

, , : , , SQL-?

, , , ; , -. - - . . , , . , Management Studio , , ( ) , -, .