Time out exception in Caching fetch

Topics: Caching Application Block
Aug 9, 2012 at 1:52 PM

I am getting the timeout exception while fetching the caching key data. Is there any config by which command timeout can be set or any other workaround?

I am using enterprise library version 5

Here is the complete stack trace


Error: Error Occurred: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.    at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
  at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
  at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
  at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj)
  at System.Data.SqlClient.TdsParserStateObject.ReadNetworkPacket()
  at System.Data.SqlClient.TdsParserStateObject.ReadBuffer()
  at System.Data.SqlClient.TdsParserStateObject.ReadByteArray(Byte[] buff, Int32 offset, Int32 len)
  at System.Data.SqlClient.TdsParser.ReadSqlValue(SqlBuffer value, SqlMetaDataPriv md, Int32 length, TdsParserStateObject stateObj)
  at System.Data.SqlClient.SqlDataReader.ReadColumnData()
  at System.Data.SqlClient.SqlDataReader.ReadColumn(Int32 i, Boolean setTimeout)
  at System.Data.SqlClient.SqlDataReader.GetValueInternal(Int32 i)
  at System.Data.SqlClient.SqlDataReader.GetValues(Object[] values)
  at System.Data.ProviderBase.DataReaderContainer.CommonLanguageSubsetDataReader.GetValues(Object[] values)
  at System.Data.ProviderBase.SchemaMapping.LoadDataRow()
  at System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping)
  at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
  at System.Data.Common.DataAdapter.Fill(DataSet dataSet, String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
  at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
  at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
  at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
  at Microsoft.Practices.EnterpriseLibrary.Data.Database.DoLoadDataSet(IDbCommand command, DataSet dataSet, String[] tableNames)
  at Microsoft.Practices.EnterpriseLibrary.Data.Database.LoadDataSet(DbCommand command, DataSet dataSet, String[] tableNames)
  at Microsoft.Practices.EnterpriseLibrary.Data.Database.LoadDataSet(DbCommand command, DataSet dataSet, String tableName)
  at Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteDataSet(DbCommand command)
  at Microsoft.Practices.EnterpriseLibrary.Caching.Database.DataBackingStore.LoadDataSingleKeyFromStore(String key)
  at Microsoft.Practices.EnterpriseLibrary.Caching.BackingStoreImplementations.BaseBackingStore.LoadSingleKey(String key)
  at Microsoft.Practices.EnterpriseLibrary.Caching.Cache.GetData(String key)
  at Microsoft.Practices.EnterpriseLibrary.Caching.CacheManager.get_Item(String key)
  at DataConversion.CachingHelper.ObjectFetch(String key)
  at DataConversion.ConversionUtility.ExistsInDatabaseCache(String NodeName, String CMR8ID, Boolean IsVocabulary, String NameSpaceOrCHILDSUB, Int32 CMR8RowID)
  at DataConversion.IncidentMigration.MigrateData(DataSet& oDataSet, Conversion& frmConversion, NameSpaceCode NameSpaceCode)
  at DataConversion.IncidentMigration.IncidentMigration(Conversion& frmConversion)
  at DataConversion.Conversion.ProcessData()

Aug 9, 2012 at 5:18 PM
Edited Aug 9, 2012 at 5:19 PM

Are you using a custom build of Enterprise Library?  The reason I ask is that the methods BaseBackingStore.LoadSingleKey and BaseBackingStore.LoadDataSingleKeyFromStores do not seem to exist in the Enterprise Library source code.  

There is no out of the box way to alter the command timeout for the caching database provider. One way to accomplish this is to modify the source code and set the CommandTimeout to an appropriate value in DataBackingStore.cs.

A general purpose approach to command timeouts is to profile the SQL that is being executed.  The default CommandTimeout is 30 seconds and, for the most part, this should be plenty of time to execute a SQL statement (the exception usually being complicated reporting stored procedures) so it's almost always worth profiling the SQL, determining what is causing the query to execute slowly and to optimize the query.

It looks to me like you are trying to load a single key from the database which is causing a table scan (or perhaps index scan) on the CacheData table.  If you are querying by the [key] column (actually it should be [PartitionName] and [Key]) then you could try adding an index to the [key] (or [PartitionName] and [Key]) column to improve the performance of the select statement.  Just be aware that adding an index to improve reads could impact insert performance so you should validate that performance is not affected in your scenario.

Another tuning approach would be to lower the number of elements in the cache (database) if this is acceptable.

Randy Levy
Enterprise Library support engineer

Aug 10, 2012 at 6:29 AM

Yes, I am using custom build. Reason for customize it because I need the fresh data of particular key from database not from memory. 

For that I created separate method and SP to fetch the data. In SP I am using both Partition name and key in where clause. Reason which I could think is amount of data stored in particular key. Actually we are storing max 100000 items in hashtable which will be saved in DB for single key.

Here is the SP which I created

CREATE PROCEDURE [dbo].[LoadItemsForSingleKey]( @partitionName varchar(128), @key varchar(128), @lastAccessedTime Datetime)


UPDATE CacheData set LastAccessedTime=@lastAccessedTime where PartitionName = @partitionName AND [key]=@key;

select  [Key],  Value,  RefreshAction,  Expirations,  ScavengingPriority,  LastAccessedTime from CacheData where PartitionName = @partitionName AND [key]=@key



Aug 10, 2012 at 7:02 AM

Since you have a custom build you can just set the CommandTimeout property in your custom code.

For root cause, I would recommend SQL profiling/explain plan and perhaps running the application with a profiler if required.

Randy Levy
Enterprise Library support engineer