DevForce Community Forum : Timeout calling Stored Procedure

Timeout calling Stored Procedure : After setting spQuery.CommandTimeout...

Tue, 20 Oct 2009 19:07:36 -700

Author: sebma
Subject: 1523
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.

Timeout calling Stored Procedure : That timeout exception is coming...

Tue, 20 Oct 2009 17:31:31 -700

Author: kimj
Subject: 1523
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.

Timeout calling Stored Procedure : I still got a timeout exception...

Mon, 19 Oct 2009 02:44:42 -700

Author: sebma
Subject: 1523
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(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?

Timeout calling Stored Procedure : Under normal circumstances, SP...

Sun, 18 Oct 2009 07:16:21 -700

Author: sebma
Subject: 1523
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