CODEWITHSUNDEEP
c#sqlsql-serverstored-procedurestimeout
Patrice Pezillier
Patrice Pezillier

Reputation: 4566

Timeout when I call a stored procedure in SQL Server 2008

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

30 sec after the procedure starts, I have a timeout exception.

How can I configure timeout ? On the server or in my C# client ?

Thanks

Upvotes: 6

Views: 22133

Answers (5)

GeorgesC
GeorgesC

Reputation: 145

Using indexes solved my problem, I found out that executing the stored procedure with ExecuteStoreCommand has not the same time as in SQL.

You can use the SQL Management Studio in order to find the index that you need, select you sql code for the stored procedure, right click and "Display Estimated Execution Plan" take the proposed index. This should optimize your stored procedure.

Upvotes: 0

Vlad Bezden
Vlad Bezden

Reputation: 89499

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

Upvotes: 5

Skrealin
Skrealin

Reputation: 1114

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();
    }
}

Upvotes: 3

KM.
KM.

Reputation: 103579

Anything you do, other than fixing the SQL in the stored procedure is just masking the real problem (the SQL).

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

Upvotes: 1

Dave Markle
Dave Markle

Reputation: 97671

You could set the CommandTimeout on the underlying connection, but a much, much, much better idea would be to take the time and effort to diagnose why the timeout is happening in the first place.

Even if you "solve" the problem by upping your CommandTimeout, you could potentially be causing other blocking issues in your database. Look for blocking queries or poor query plans, or badly designed tables and indexes instead.

Upvotes: 16

Related Questions