Timeout when I call a stored procedure in SQL Server 2008

Question

From C # with EF, I call a long stored procedure using ExecuteStoreCommand

30 seconds after starting the procedure, I have a timeout exception.

How to set a timeout? On the server or on my C # client?

thanks

Answer 1

You can set CommandTimeout on a basic connection, but a much better idea is to spend time and effort diagnosing why a timeout occurs in the first place.

Even if you “solve” the problem by raising CommandTimeout, you can potentially cause other locking problems in your database. Look for blocking queries or poor query plans or poorly designed tables and indexes.

Answer 2

  using (var context = new MyDbEntities()) { context.CommandTimeout = 600; context.MyLongRunningStoredProc(); } 

Answer 3

 using (var conn = new SqlConnection(ConnectionStrings.toMyDB)) { conn.Open(); using (var cmd = new SqlCommand("myProc", conn)) { cmd.CommandType = CommandType.StoredProcedure; cmd.CommandTimeout = 30; // Increase this to allow the proc longer to run cmd.Parameters.AddWithValue("@Param", myParam); cmd.ExecuteNonQuery(); } } 

Answer 4

Everything you do, except for fixing SQL in the stored procedure, just masks the real problem (SQL).

You need to ask a question about speeding up your procedure, where you publish your tables, and the stored procedure code so that it can be fixed once and for all.

Answer 5

Using indexes solved my problem, I found that executing a stored procedure using ExecuteStoreCommand is not at the same time as in SQL.

You can use SQL Management Studio to find the index you need, select your sql code for the stored procedure, right-click and “Show the estimated execution plan”, accept the proposed index. This should optimize your stored procedure.