DbCommand.CommandTimeout is not working

Topics: Data Access Application Block
Nov 26, 2011 at 4:13 AM

Hi,

I am using EntLib 5.0 with Visual Studio 2010 and SQL Server 2008R2. Below is the code that I am using to execute a stored procedure: 

		SqlDatabase databaseObect = new SqlDatabase(CreateConnectionString());
                DbCommand sqlCmd = databaseObect.DbProviderFactory.CreateCommand();
                sqlCmd.CommandType = CommandType.StoredProcedure;
                //Set command timeout in seconds
                sqlCmd.CommandTimeout = 999999;
                sqlCmd = databaseObect.GetStoredProcCommand("usp_FillDatabaseTables");
                databaseObect.ExecuteNonQuery(sqlCmd);

Stored procedure that is being executed by the above code is long running and takes around 15 minutes but this code gets timed out within a minute throwing an exception: 

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

As you can see in the code, I have set the CommandTimeout to fairly large value but still it's getting timed out within a minute. Now, if I try to execute the same stored procedure using "System.Data.SqlClient" then it runs fine. Code is below:

		SqlConnection sqlConn = new SqlConnection(CreateConnectionString());
                SqlCommand sqlComm = new SqlCommand();
                sqlComm.Connection = sqlConn;
                sqlComm.CommandType = CommandType.StoredProcedure;
                sqlComm.CommandText = "usp_FillDatabaseTables";
                sqlComm.CommandTimeout = 999999;
                sqlConn.Open();
                sqlComm.ExecuteNonQuery();
                sqlConn.Close();

This code runs fine and does not time-out (commandtimeout property for both the codes are same). Is that mean Enterprise library ignores the CommandTimeout value?

Could you please suggest me what can I do to execute the stored procedure using EntLib 5.0.

Nov 26, 2011 at 8:58 AM
Edited Nov 26, 2011 at 8:59 AM

When you call GetStoredProcCommand it is returning a new DbCommand object so any properties you've set on the DbCommand
prior to that call are lost and not reflected in the new object.

This should work:

SqlDatabase databaseObect = new SqlDatabase(CreateConnectionString());           
DbCommand sqlCmd = databaseObect.GetStoredProcCommand("usp_FillDatabaseTables");
//Set command timeout in seconds
sqlCmd.CommandTimeout = 999999;
databaseObect.ExecuteNonQuery(sqlCmd);

--
Randy Levy
Enterprise Library support engineer
entlib.support@live.com

Feb 24, 2012 at 8:50 PM

Randy, I have the same problem.  I'm using a private instance of the default database, instantiated as follows:

private Database _db = EnterpriseLibraryContainer.Current.GetInstance<Database>();

Then I'll call a command similar to this:

string sql = "usp_SomeStoredProcedure";

DbCommand cmd = _db.GetStoredProcCommand(sql);

_db.AddInParameter(cmd, "CatID", Db.Type.String, "234");

cmd.CommandTimeout = 20;

DataSet ds = _db.ExecuteDataSet(cmd);

This query will run and fill my dataset, even if it takes four minutes or so.  It won't trigger a timeout exception if it runs longer than the 20 seconds I specified.  But sometimes I will get a SQLException timeout if it runs about five minutes or so--and I'm trying to figure out the issue. 

I've tried creating a local instance of the current database and running similar commands on that, thinking perhaps the private instance was the problem, but that didn't seem to help: 

trial_db = EnterpriseLibraryContainer.Current.GetInstance<Database>();  (and then I run the commands above on trial_db instead of _db). 

Any thoughts?  What am I missing here?

David Shaddock

Feb 28, 2012 at 6:01 AM

The documentation for CommandTimeout says:

This property is the cumulative time-out for all network reads during command execution or processing of the results. A time-out can still occur after the first row is returned, and does not include user processing time, only network read time.

One common scenario with timeouts is that you execute the command and no results are returned within the CommandTimeout period (let's say 20 seconds as in your example).   So after 20 seconds waiting on results a timeout exception is thrown.

However, your scenario is different.  It looks like you are getting results back from the server but there is a lot of data.  So it seems that you are getting fairly constant data back from the server.  As per the docs, the timeout is: cumulative and the counting occurs for network reads.  So it looks like you are occasionally hitting the timeout limit because there are either many small network reads that eventually add up to exceed the 20 seconds or occasionally during the five minutes of processing there is larger network delay.

This blog does a good job of explaining the scenario.  Although the blog claims that the timeout is not cumulative based on the author's experience.  It does seem that way but I don't have enough evidence to claim the docs are wrong.


As per usual the approach in these situations is to first optimize query and if that doesn't help then increase the CommandTimeout if required.

-- 
Randy Levy
Enterprise Library support engineer
entlib.support@live.com

Feb 28, 2012 at 6:47 PM

Randy, that was an outstandingly clear reply, and a very good blog reference as well.  Thanks for explaining all this.  As a result, we've changed our script timeout settings and don't really expect to see a command timeout unless our server gets really busy in the future.  I'm running ReportViewer and have to use local processing since it's SQL Server 2005 that provides the data for the datasets I hand over to the RDLC files to format and display.  I've optimized the queries to within an inch of their lives; we just ask them to provide one heck of a lot of data with lots of joins.  This morning we tested by running two reports from different machines, at the same time, which used a 12-part join query to provide each of us with about 470 pages of report data from our production server.  We both got our complete reports in less than five minutes, fully formatted and displayed, with no timeouts.  So we're happy.

Thanks for the help! 

David Shaddock

eComSystems software engineer