New Posts New Posts RSS Feed: Increasing timeout for all the queries
  FAQ FAQ  Forum Search   Calendar   Register Register  Login Login

Increasing timeout for all the queries
 Post Reply Post Reply
Author
  Share Topic Share Topic  Topic Search Topic Search  Topic Options Topic Options
pponzano View Drop Down
Senior Member
Senior Member
Avatar

Joined: 28-Apr-2011
Location: Italy
Posts: 165 		Post Options Post Options   Quote pponzano Quote  Post ReplyReply Direct Link To This Post Topic: Increasing timeout for all the queries
    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
Back to Top
DenisK View Drop Down
IdeaBlade
IdeaBlade


Joined: 25-Aug-2010
Posts: 715 		Post Options Post Options   Quote DenisK Quote  Post ReplyReply Direct Link To This Post 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

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


Edited by DenisK - 22-Mar-2012 at 11:35am
Back to Top
pponzano View Drop Down
Senior Member
Senior Member
Avatar

Joined: 28-Apr-2011
Location: Italy
Posts: 165 		Post Options Post Options   Quote pponzano Quote  Post ReplyReply Direct Link To This Post 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
Back to Top
DenisK View Drop Down
IdeaBlade
IdeaBlade


Joined: 25-Aug-2010
Posts: 715 		Post Options Post Options   Quote DenisK Quote  Post ReplyReply Direct Link To This Post 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.
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: 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
Back to Top
DenisK View Drop Down
IdeaBlade
IdeaBlade


Joined: 25-Aug-2010
Posts: 715 		Post Options Post Options   Quote DenisK Quote  Post ReplyReply Direct Link To This Post Posted: 28-Mar-2012 at 12:02pm
Hi sebma,

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


Edited by DenisK - 28-Mar-2012 at 12:33pm
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: 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.
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: 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
Back to Top
DenisK View Drop Down
IdeaBlade
IdeaBlade


Joined: 25-Aug-2010
Posts: 715 		Post Options Post Options   Quote DenisK Quote  Post ReplyReply Direct Link To This Post 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 Release Notes to find out the features and defects in a DevForce version.
Back to Top
Thomax View Drop Down
Newbie
Newbie


Joined: 04-Dec-2008
Location: China
Posts: 1 		Post Options Post Options   Quote Thomax Quote  Post ReplyReply Direct Link To This Post 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;
            };

......


Edited by Thomax - 10-Mar-2014 at 5:22am
Back to Top
DenisK View Drop Down
IdeaBlade
IdeaBlade


Joined: 25-Aug-2010
Posts: 715 		Post Options Post Options   Quote DenisK Quote  Post ReplyReply Direct Link To This Post 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 here for more info on various timeouts.
Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down