Print Page | Close Window

Timeout calling Stored Procedure

Printed From: IdeaBlade
Category: DevForce
Forum Name: DevForce 2009
Forum Discription: For .NET 3.5
URL: http://www.ideablade.com/forum/forum_posts.asp?TID=1523
Printed Date: 08-Apr-2025 at 8:33pm


Topic: Timeout calling Stored Procedure
Posted By: sebma
Subject: Timeout calling Stored Procedure
Date Posted: 18-Oct-2009 at 7:16am
Under normal circumstances, SP queries would return results pretty fast, well within the default ADO.NET's SqlCommand 30 seconds timeout.
 
This topic is not about database design, though this too has significant impact to application performance.
 
We have bulk computation store procedures that are executed by background tasks on hourly basis, these SPs may exceed the default SqlCommand timeout. In such cases, we receive
 

IdeaBlade.EntityModel.EntityServerException: An error occurred while executing the command definition. See the inner exception for details. ---> System.Data.EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details. ---> System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

 
Without deviating from facilities provided by DevForce (e.g. ADO.NET Asynchronous SqlCommand, Microsoft-specific T-SQL with Service Broker etc.), can we use EntityManager's ExecuteQueryAsync(...) to execute long-running SP asynchronously?
 
Thanks in advance.
Sebastian
 
 


Replies:
Posted By: sebma
Date Posted: 19-Oct-2009 at 2:44am
I still got a timeout exception in my async callback:
 
        private void DoTask()
        {
            StoredProcQuery spQuery = m_EntityManager.MySP(...);
           
            // Does not help, though reference help mentioned the default Timeout is 1 minute for UseDTCOption.True
            //TransactionSettings txnSettings = new TransactionSettings(UseDTCOption.True);
            //spQuery.QueryStrategy = new QueryStrategy(QueryStrategy.DataSourceOnly, txnSettings);
            Guid id = Guid.NewGuid();
            m_EntityManager.ExecuteQueryAsync(spQuery, new AsyncCompletedCallback<EntityFetchedEventArgs>(AsyncFetchCompleted), id);
        }
 
        private static void AsyncFetchCompleted(EntityFetchedEventArgs e)
        {
            if (e.Error != null)
            {
                throw e.Error; // timeout exception in InnerException
            }
 
You can easily simulate timeout by placing a delay > 30seconds at the start of any SP:
 
WAITFOR DELAY '00:00:50';
 
Looks like EntityManager's ExecuteQueryAsync(...) is not implemented using Asynchronous SqlCommand, or am I wrong?
 

Posted By: kimj
Date Posted: 20-Oct-2009 at 5:31pm
That timeout exception is coming from the database.  The query has a CommandTimeout property you can set to override the 30 second default, e.g.,
 
   spQuery.CommandTimeout = 45;
 
You can set this for either sync or async execution requests.
 
All database access in DevForce WinClient is delegated to the Entity Framework.  I'd guess that EF delegates to the ADO.NET provider in use concerning how the database commands are generated and issued.
 

Posted By: sebma
Date Posted: 20-Oct-2009 at 7:07pm
After setting spQuery.CommandTimeout = 60, I ran my app again got thrown out with

'IdeaBlade.Core.IdeaBladeException' occurred in IdeaBlade.EntityModel.dll
FetchStrategy for StoredProcQuery must be DatasourceOnly.

I remember I read somewhere that StoredProcQuery is by default DataSourceOnly. Anyhow, I added spQuery.QueryStrategy = QueryStrategy.DataSourceOnly. Set a breakpoint before ExecuteQueryAsync, to see spQuery's FetchStrategy was indeed DataSourceOnly.

Continue and still throws "FetchStrategy for StoredProcQuery must be DatasourceOnly".

Only I went back to stored proc and removed WAITFOR DELAY '00:00:50', that the spQuery worked. This is really weird.





Print Page | Close Window