ExecuteDataSet throwing Timeout exception.

Topics: Data Access Application Block
Jun 27, 2007 at 2:25 PM
Hi,

I'm facing strange behaviour after using the ExecuteDataSet function of Microsoft.Practices.EnterpriseLibrary.Data library in my web application.
Here is my observation after I run the SQL Server Profiler 2005:

1. When I execute my Stored procedure using the ExecuteDataSet function(as specified below) from my web application,

Database db = DatabaseFactory.CreateDatabase();
DbCommand command = db.GetStoredProcCommand("SP_XYZ");
db.AddInParameter(command, "CurrentPage", DbType.Int32, iCurrentPage);
db.AddInParameter(command, "PageSize", DbType.Int32, iPageSize);
db.AddInParameter(command, "sortExpression", DbType.String, strSortExpression);
if (!bSortDirection)
db.AddInParameter(command, "sortDirection", DbType.String, "DESC");
else
db.AddInParameter(command, "sortDirection", DbType.String, "ASC");
db.AddInParameter(command, "ID", DbType.Int32, coeId);
return db.ExecuteDataSet(command);

Here is the trace when it is run using the ExecuteDataSet(command) function.

Application Name: .Net SqlClient Data Provider
CPU: 42078
READS: 3177316
WRITES: 0
Duration: 42834 (Start time: 2007-06-27 19:02:13.037 End time: 2007-06-27 19:02:55.840)

2. But when I execute the same query from SQL Server Query Analyzer, below is the trace

Application Name: .Net SqlClient Data Provider
CPU: 547
READS: 39799
WRITES: 0
Duration: 634 (Start time: 2007-06-27 19:04:55.707 End time: 2007-06-27 19:04:56.330)

Am I doing something wrong? Or Is there any bug in Data Access Application Block?

Thanks in Advance.
Manish
Jun 27, 2007 at 7:36 PM
Edited Jun 27, 2007 at 7:39 PM
Have you run the stored procedure several times from you web application?

Remember that sql server caches the result, so when you run the stored procedure again from the Query Analyzer the server does not need to do anything else then lookup the last result for that procedure.

Dont forget that the first time a stored procedure executes the server compiles the procedure and finds the best query plan.

If this stored procedure needs so long to execute, try to optimize the query so it doesnt use so long time first time it executes.

So before you test your procedure in the Query Analyzer, its a good ide to clear the procedure cache.

dbcc freeproccache
go

regards
Benny

Last tip:
If you dont get your procedures to be quicker. Set the commandtimout.

command.commandtimout = 90; <= more then this gets your application in trouble. But it should never take more then a few seconds.
Jun 27, 2007 at 8:12 PM
Thanks for the Reply.

Yes I have run the SP from my web application several times. But unfortunately, I got the same behaviour everytime i.e timeout.(by default commandtimeout=30)
Yes, I have also executed my SP after clear the procedure cache and it is taking 5-6 seconds(everytime) from Query Analyzer. But the same SP takes more than 40-50 seconds to return the result(using ExecuteDataSet) on UI.

I don't think so making the commandtimeout=90 is a good option in case of web application, though after making it 90, i'm not getting the timeout exception.

Any other thoughts on ExecuteDataSet(Command).

Regards,
Manish Siwach.
-------------------

BennyXNO wrote:
Have you run the stored procedure several times from you web application?

Remember that sql server caches the result, so when you run the stored procedure again from the Query Analyzer the server does not need to do anything else then lookup the last result for that procedure.

Dont forget that the first time a stored procedure executes the server compiles the procedure and finds the best query plan.

If this stored procedure needs so long to execute, try to optimize the query so it doesnt use so long time first time it executes.

So before you test your procedure in the Query Analyzer, its a good ide to clear the procedure cache.

dbcc freeproccache
go

regards
Benny

Last tip:
If you dont get your procedures to be quicker. Set the commandtimout.

command.commandtimout = 90; <= more then this gets your application in trouble. But it should never take more then a few seconds.

Jun 27, 2007 at 8:46 PM
Edited Jun 27, 2007 at 8:50 PM
Are your completly sure that the two runs are using the same parameters?

The problem has to lay in the parameters you send in.

Because the big gap between both CPU and Reads between the to runs indicate that there is a big difference between the parameters. And shouldnt have anything to do in the fact that you are using ExecuteDataset
Jun 28, 2007 at 6:53 AM
Yes, I have copied the same SP from SQL Profiler which was traced at the time of web application browsing.

Any thoughts.

Thanks
-Manish
Apr 8, 2009 at 3:17 AM
Has anyone ever got to the bottom of this?  It seems to be a common problem.  I had the same issue today.  A stored proc takes 1 second to run in SSMS but yet suddenly starts taking 30 seconds when executed through a web app (using ExecuteDataSet function of Microsoft.Practices.EnterpriseLibrary.Data library).  I've seen this several times now and each time the issue just magically disappears as fast as it first appears.  I'm convinced that it has something to do with EntLib and connection pooling.  Can someone please shed some light on this.

Regards

Myles

Event log error:

Timestamp local: 8/04/2009 9:20:04 AM

Message: HandlingInstanceID: 6f7f8237-c61d-4db7-b8ea-9bcd470e42be

An exception of type 'System.Data.SqlClient.SqlException' occurred and was caught.

----------------------------------------------------------------------------------

04/08/2009 09:20:04

Type : System.Data.SqlClient.SqlException, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089

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

Source : .Net SqlClient Data Provider

Help link :

Errors : System.Data.SqlClient.SqlErrorCollection

Class : 11

LineNumber : 0

Number : -2

Procedure :

Server : ********************

State : 0

ErrorCode : -2146232060

Data : System.Collections.ListDictionaryInternal

TargetSite : Void OnError(System.Data.SqlClient.SqlException, Boolean)

Stack Trace : 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.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)

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)

Apr 8, 2009 at 5:25 AM
I'm not sure about the suggestion mentioned in this post - http://entlib.codeplex.com/Thread/View.aspx?ThreadId=22574.  Might be somehow connected to this line I read from msdn:

"Connection attempts may fail when the current language is Turkish or Azeri and the server name contains "I". To avoid this problem, connect to the server using the IP address. If "I" is in the instance name, specify the TCP/IP port number when connecting. To connect to a specific port, use an alias." - http://msdn.microsoft.com/en-us/library/ms190181.aspx

Adding my own thoughts here, are you sure there are no connections left open in your app? Like in the case of using a datareader? Have you tried running sql profiler and see if that same stored procedure you run in SSMS is really the one which is causing the timeout exception?


Sarah Urmeneta
Global Technology & Solutions
Avanade, Inc.
entlib.support@avanade.com
Apr 8, 2009 at 7:21 AM
Our current language is not Turkish or Azeri (we are based in Australia)

In regards to open connections I was under the impression that EntLib automatically cleans up when using ExecuteDataSet?  Our business method that calls the stored proc consists of the following:

 

DataSet ds = new DataSet();

 

 

 

// Create database command object for the stored procedure.

 

 

Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetStoredProcCommand(SP_SCHEDULE_GET_BY_TYPE);
db.AddInParameter(cmd,
ScheduleDTO.DATA_CURRICULUM_PERIOD_ID, DbType.Int32, paramId);

 

 

 

ds = db.ExecuteDataSet(cmd);

 

ds.Tables[0].TableName = TAB_SCHEDULE_CLASS;

ds.Tables[1].TableName = TAB_SCHEDULE_ITEM;

ds.Tables[2].TableName = TAB_SCHEDULE_DATE;

 

return ds;
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Yes we have used SQL Profiler numerous times and are 100% certain that the same proc we run in SSMS is the same one that periodically causes the timeout exception.  From spending hours on Google it appears quite a few people have experienced the same behaviour over the years.  

http://entlib.codeplex.com/Thread/View.aspx?ThreadId=46729

Myles

 

Apr 8, 2009 at 7:47 AM
Edited Apr 8, 2009 at 7:55 AM
Yes, DAAB manages the connection most of the time.  Not unless you used a datareader, returning a datareader requires the connection to be open, you have to manually close it yourself.  Just making sure that you don't have any calls to creating a datareader with unclosed connection prior to the ExecuteDataSet method.  I can't repro it so I'll just look it up if it has been previously logged in the issue tracker.


Sarah Urmeneta
Global Technology & Solutions
Avanade, Inc.
entlib.support@avanade.com