New Posts New Posts RSS Feed: Timeout calling Stored Procedure
  FAQ FAQ  Forum Search   Calendar   Register Register  Login Login

Timeout calling Stored Procedure
 Post Reply Post Reply
Author
  Share Topic Share Topic  Topic Search Topic Search  Topic Options Topic Options
sebma View Drop Down
Groupie
Groupie
Avatar

Joined: 19-Aug-2008
Location: Singapore
Posts: 66 		Post Options Post Options   Quote sebma Quote  Post ReplyReply Direct Link To This Post Topic: Timeout calling Stored Procedure
    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
 
 
Back to Top
sebma View Drop Down
Groupie
Groupie
Avatar

Joined: 19-Aug-2008
Location: Singapore
Posts: 66 		Post Options Post Options   Quote sebma Quote  Post ReplyReply Direct Link To This Post 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?
 
Back to Top
kimj View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 09-May-2007
Posts: 1391 		Post Options Post Options   Quote kimj Quote  Post ReplyReply Direct Link To This Post 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.
 
Back to Top
sebma View Drop Down
Groupie
Groupie
Avatar

Joined: 19-Aug-2008
Location: Singapore
Posts: 66 		Post Options Post Options   Quote sebma Quote  Post ReplyReply Direct Link To This Post 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.



Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down