Print Page | Close Window

Increasing timeout for all the queries

Printed From: IdeaBlade
Category: DevForce
Forum Name: DevForce 2010
Forum Discription: For .NET 4.0
URL: http://www.ideablade.com/forum/forum_posts.asp?TID=3351
Printed Date: 28-Mar-2024 at 6:16am


Topic: Increasing timeout for all the queries
Posted By: pponzano
Subject: Increasing timeout for all the queries
Date Posted: 21-Mar-2012 at 3:22am
Hello,
in my application I call stored procedures using

   StoredProcQuery query = entityManagerProvider.Manager.SP_MY_STORE(....);

            EntityQueryOperation op = query.ExecuteAsync();

            return op.OnComplete(onSuccess, onFail).AsOperationResult<myResultType>();

without specifing the query.CommandTimeout = 240;

Since the DBAs are integrating the logic of some stored procedure and the execution time is increasing I wish to set the CommandTimeout for alll the StoredProcQuery I'm to use... is there a way of specifing it in the web.config?  or in another place, just not to write this line everywhere?

Thanks
Paolo



Replies:
Posted By: DenisK
Date Posted: 22-Mar-2012 at 11:34am
Hi Paolo,

Setting the timeout in web.config will set it globally for all queries, not just for stored procs. But it is an option. See  http://drc.ideablade.com/xwiki/bin/view/Documentation/code-sample-custom-client-servicemodel#HChangetimeoutvalues - http://drc.ideablade.com/xwiki/bin/view/Documentation/code-sample-custom-client-servicemodel#HChangetimeoutvalues

If you just want to set it for stored procs, you can wrap the query in a TransactionScope. For example:

      // create a querystrategy that has a 240 second timeout
      TransactionSettings settings = new TransactionSettings(System.Transactions.IsolationLevel.Serializable,
        new TimeSpan(0, 0, 0, 240, 0));
      QueryStrategy qs = new QueryStrategy(QueryStrategy.Normal, settings);
      var sp1 = mgr.StoredProcQuery1();
      var sp2 = mgr.StoredProcQuery2();
      sp1.With(qs).Execute();
      sp2.With(qs).Execute();

Here's a section on our DRC that may help you better understand timeouts.  http://drc.ideablade.com/xwiki/bin/view/Documentation/understand-timeouts - http://drc.ideablade.com/xwiki/bin/view/Documentation/understand-timeouts


Posted By: pponzano
Date Posted: 23-Mar-2012 at 12:36am
Hello Denisk,
thanks for your reply... I've kist read the section on drc...in fact setting the CommandTymeout in the query fix my problem... for your first suggestion (setting in web.config I've set it but as far I've seen it only sets the WCF timeout...my problem is with the SQL Timeout expired, in fact I've seen that the CommandTimeout is 0 ... since I don't usedirect queries but everything passes via stored procedure... I don't care if all the timeouts are at 3 minutes..

Thanks


Posted By: DenisK
Date Posted: 23-Mar-2012 at 12:32pm
Hi Paolo,

I want to apologize before hand as I believe I gave you the wrong information above. In my post, I was implying that CommandTimeout is the same as TransactionTimeout and the SendTimeout in the web.config. In reality they're not. There are apparently different timeouts that you can set. I don't fully understand the timeouts as well but I know that they are:

1. Database timeout - Can be set by using EntityQuery.CommandTimeout

2. Transaction timeout - Can be set by using the TransactionSettings which in turn passed into the QueryStrategy

3. WCF communication timeouts - These are the sendTimeout, receiveTimeout, etc settings in the .config file.

4. and finally IIS timeouts

Yes, we have a lot of timeouts to deal with. 

Answering your specific question, no, there is not a way to set the CommandTimeout globally. Unfortunately you have to set it manually per query.


Posted By: sebma
Date Posted: 28-Mar-2012 at 1:17am
Hi Denis,

In a reasonably-sized app, backend apps in particular, there are queries everywhere.

Instead of hunting high and low for each EntityQuery.CommandTimeout, is there any future plan to have a "global" setting for SQL timeout (by config or programmatically), much like your QueryStrategy options pertaining to an EntityManager ?

Thanks
-Seb


Posted By: DenisK
Date Posted: 28-Mar-2012 at 12:02pm
Hi sebma,

Thank you for the feedback. I've added this to our feature request list.


Posted By: sebma
Date Posted: 03-Apr-2012 at 1:58am
Hi all,

For DevForce 2009 only, perhaps a not so elegant workaround (till we have the IdeaBlade solution), is to set CommandTimeout at ADO.NET EF level. Implement the partial method OnContextCreated() found in your EDMX data model context class. For example:

    // See your EDMX's Designer.cs class for your Context class
    public partial class YOUR_DataModelContext : global::System.Data.Objects.ObjectContext
    {
        /// <summary>
        /// <para>Properties of this data model's context will be set here.</para>
        /// <para>CommandTimeout will be prolonged to avoid unnecessary SQL timeout.</para>
        /// <para>If SqlTimeoutInSeconds from AppSettings section is not found or set, CommandTimeout will not be set, hence its value will be ADO.NET default.</para>
        /// </summary>
        partial void OnContextCreated()
        {
            int timeoutFromConfig = GetCommandTimeoutFromConfig();
            if (timeoutFromConfig > 0)
            {
                this.CommandTimeout = timeoutFromConfig; //60 * 30; // 60secs * 30 = 1800secs = 30mins
            }
        }

WARNING! Setting CommandTimeout at ADO.NET EF level will cause it to be applied globally to all projects that reference your EDMX assembly.






Posted By: sebma
Date Posted: 18-Jul-2012 at 3:08am
Hi,

May I know if this feature to increase timeout for all queries is available in DevForce 2010 v6.1.8 ?
I asked because it is ridiculously stupid to search all codes looking for queries and set timeout individually.

Thanks
Seb


Posted By: DenisK
Date Posted: 18-Jul-2012 at 12:41pm
Hi Seb,

This feature is not in 6.1.8. I don't currently have a timeline on when this will be implemented but I have moved it up the priority list. 

FYI, this feature number is F1930. You can always check our http://drc.ideablade.com/xwiki/bin/view/Documentation/release-notes - Release Notes  to find out the features and defects in a DevForce version.


Posted By: Thomax
Date Posted: 10-Mar-2014 at 5:18am
Hi DenisK,

Any news for this feature? Can I use the below code for setting CommandTimeout for all the queries?
    var MyEntityManager1 = new MyEntityManager();

            MyEntityManager1.Querying += (s1, e1) =>
            {
                e1.Query.CommandTimeout = 300;
            };

......


Posted By: DenisK
Date Posted: 10-Mar-2014 at 4:04pm
Hi Thomax,

This feature has yet to be implemented.

But you are correct. If you are setting the CommandTimeout in the Querying event, then that will set the timeouts for each query that goes out.

Please be aware that the query timeouts is but one of many timeouts that you should be aware of.

See  http://drc.ideablade.com/xwiki/bin/view/Documentation/understand-timeouts - here  for more info on various timeouts.



Print Page | Close Window