Data Access throwing "random" timeout exceptions

Topics: Data Access Application Block
Feb 21, 2008 at 7:38 AM
We've been happily using Enterprise Library 3.1 (and its data access application block) for a some time but now we have encountered a problem we can't solve. At one point of our code we call ExecuteReader(sqlCommand) and this line eventually will cause this exception:

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

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.Practices.EnterpriseLibrary.Data.Database.DoExecuteReader(DbCommand command, CommandBehavior cmdBehavior)
at Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteReader(DbCommand command)
at Datawell.Epp.DataLayer.GetProductionData(String producerCodes, String productCodes, String customerCodes, Int32 productionID, HierarchyTypeEnum sdimHierarchy) in C:\Projects\EPP50\EPP50\Data\DataLayer.vb:line 1356
---

The weird thing is that if we copy and paste the same SQL query into a query analyzer and execute it from there, it takes ~0.5seconds to complete. Like it should be when it's working correctly. But when using data access application block's ExecuteReader-method, it takes 30seconds before timing out.

This problem occured couple months back for the first time but it went away like nothing had happened. But now again it is here and we would like to fully understand what is causing this problem. Other queries seem to be working fine and like I mentioned, this query works too if executed through the query analyzer. One thing that differs this query from most of the others used in our application is that it includes quite a many long IN-clauses.

Any help is greatly appreciated.
Feb 22, 2008 at 3:16 PM

Miksu wrote:
We've been happily using Enterprise Library 3.1 (and its data access application block) for a some time but now we have encountered a problem we can't solve. At one point of our code we call ExecuteReader(sqlCommand) and this line eventually will cause this exception:

---
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
<snip>
Any help is greatly appreciated.


Can't help but can confirm that EntLib seems to have it's own idea about timeout. We have an SP that takes about 1 minute to run. We set the cmd.CommandTimeout to 120 seconds before calling ExecuteDataSet and still get the timeout exception.

anyone have any ideas?

thx
Jan 27, 2009 at 3:39 PM
Not sure if you ever solved this, but I just got this exact problem today and I resolved it by changing the language for the SQL Server login from "English" to "British English".
A query that was previously timing out in my code now takes a second to run..