how to wait for ExecuteQuery to complete?
-
Hi, I'm using linq's ExecuteQuery to call a stored proc on the server. I don't use the modeler to set up a funtion to call the stored proc because it is too complex for it to figure out the return values and only returns an int. That's another issue and I know there are workarounds such as setting up a dummy proc but, I don't want to hassle with that. So I use ExecuteQuery to call the stored proc and return the results. This works fine as long as I only call it once. Next I needed to call it again, but this time I only need a count of the returned rows. I didn't want to write another stored proc to return the counts of the same query, so instead I addeded .Count() to the results of executeQuery. I know its not the most efficient way to get a count, but it should be quick to get the results I needed. Ok, No problem so far, I get the value I need and quick enough for my purpose. The problem comes when I add a second call to ExecuteQuery immediately after the first. Basically I'm checking for the presence of data before and after a certain date range. Then I can notify the user whether there is data before or after... The problem is that the second call times out. If I put a breakpoint in and pause even for a second before making the second call, it will process just as fast as the first. Each query uses two separate instances of the same DataContext class. I don't want to put a pause there because I want to be sure the first has completed before continuing, not just guess that is is finished. I can replicate the timing issue in an sql server query window by calling the stored proc twice in the same query. If I ad a "GO" statement between them, they each return in less than a second. Without the "GO" statement, it takes 40+ seconds for the results to return. Any ideas will be helpful. Thanks.
-
Hi, I'm using linq's ExecuteQuery to call a stored proc on the server. I don't use the modeler to set up a funtion to call the stored proc because it is too complex for it to figure out the return values and only returns an int. That's another issue and I know there are workarounds such as setting up a dummy proc but, I don't want to hassle with that. So I use ExecuteQuery to call the stored proc and return the results. This works fine as long as I only call it once. Next I needed to call it again, but this time I only need a count of the returned rows. I didn't want to write another stored proc to return the counts of the same query, so instead I addeded .Count() to the results of executeQuery. I know its not the most efficient way to get a count, but it should be quick to get the results I needed. Ok, No problem so far, I get the value I need and quick enough for my purpose. The problem comes when I add a second call to ExecuteQuery immediately after the first. Basically I'm checking for the presence of data before and after a certain date range. Then I can notify the user whether there is data before or after... The problem is that the second call times out. If I put a breakpoint in and pause even for a second before making the second call, it will process just as fast as the first. Each query uses two separate instances of the same DataContext class. I don't want to put a pause there because I want to be sure the first has completed before continuing, not just guess that is is finished. I can replicate the timing issue in an sql server query window by calling the stored proc twice in the same query. If I ad a "GO" statement between them, they each return in less than a second. Without the "GO" statement, it takes 40+ seconds for the results to return. Any ideas will be helpful. Thanks.
Stryder_1 wrote:
I don't want to hassle with that.
Stryder_1 wrote:
I didn't want to write another stored proc
Stryder_1 wrote:
I don't want to put a pause there
I don't think a solution that suits your taste is coming any time soon. :|
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, and improve readability.
-
Stryder_1 wrote:
I don't want to hassle with that.
Stryder_1 wrote:
I didn't want to write another stored proc
Stryder_1 wrote:
I don't want to put a pause there
I don't think a solution that suits your taste is coming any time soon. :|
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, and improve readability.
Those statements were put in: 1. to head off the inevitable "don't do it that way, do it MY way" comments. 2. to identify that I already considered and rejected several workarounds for the problem. 3. to illustrate I'm not looking for another work around, but to answer the question - "Is there a way to know when the ExecuteQuery statement from linq has completed"? Upon re-reading, I probably could have worded my request differently, but I tried to put in as much information as I could to try to give an understanding of what was going on in the code. Thanks.
-
Hi, I'm using linq's ExecuteQuery to call a stored proc on the server. I don't use the modeler to set up a funtion to call the stored proc because it is too complex for it to figure out the return values and only returns an int. That's another issue and I know there are workarounds such as setting up a dummy proc but, I don't want to hassle with that. So I use ExecuteQuery to call the stored proc and return the results. This works fine as long as I only call it once. Next I needed to call it again, but this time I only need a count of the returned rows. I didn't want to write another stored proc to return the counts of the same query, so instead I addeded .Count() to the results of executeQuery. I know its not the most efficient way to get a count, but it should be quick to get the results I needed. Ok, No problem so far, I get the value I need and quick enough for my purpose. The problem comes when I add a second call to ExecuteQuery immediately after the first. Basically I'm checking for the presence of data before and after a certain date range. Then I can notify the user whether there is data before or after... The problem is that the second call times out. If I put a breakpoint in and pause even for a second before making the second call, it will process just as fast as the first. Each query uses two separate instances of the same DataContext class. I don't want to put a pause there because I want to be sure the first has completed before continuing, not just guess that is is finished. I can replicate the timing issue in an sql server query window by calling the stored proc twice in the same query. If I ad a "GO" statement between them, they each return in less than a second. Without the "GO" statement, it takes 40+ seconds for the results to return. Any ideas will be helpful. Thanks.
To your statement. "I don't want to put a pause there because I want to be sure the first has completed before continuing, not just guess that is is finished.", The ExecuteQuery method does not continue to the next exectueable statement in your code until it has received the results from that call. Most likely the problem is that the connection is timing out. You have two options you can take, 1 increase the timeout from the default of 30 seconds. or 2 open the connection yourself before the first call to ExecuteQuery and closing it just after the second call to ExecuteQuery. To increase the timeout you can do this just after creating a DataContext:
DataContext.CommandTimeout = 40;Or Opening the connection :DataClasses1DataContext db = new DataClasses1DataContext(); var con = db.Connection; con.Open(); // Do your Linq queries here con.Close();Fernando -
To your statement. "I don't want to put a pause there because I want to be sure the first has completed before continuing, not just guess that is is finished.", The ExecuteQuery method does not continue to the next exectueable statement in your code until it has received the results from that call. Most likely the problem is that the connection is timing out. You have two options you can take, 1 increase the timeout from the default of 30 seconds. or 2 open the connection yourself before the first call to ExecuteQuery and closing it just after the second call to ExecuteQuery. To increase the timeout you can do this just after creating a DataContext:
DataContext.CommandTimeout = 40;Or Opening the connection :DataClasses1DataContext db = new DataClasses1DataContext(); var con = db.Connection; con.Open(); // Do your Linq queries here con.Close();FernandoHi Fernando, Thanks for your response. I would tend to agree with you about it being a timeout issue, save for the fact that adding a 0.25 sec thread sleep between the ExecuteQuery calls caused each call to finish almost immediately. It's only when I don't have the sleep pause that the timeout returns. Unless you can think of a reason the thread sleep would somehow reduce the execution time, but I'm thinking the delay would be on the sql server, rather than the app server. I did try to manually open and close the connection around the queries as you suggested, but that did not solve the problem. I'm leery about just extending the timeout as the number of ExecuteQuery calls will change dynamically depending on the number of objects I am trying to display in the page. So I won't know how long it may need to be until the page is rendered. Thanks again
-
Hi Fernando, Thanks for your response. I would tend to agree with you about it being a timeout issue, save for the fact that adding a 0.25 sec thread sleep between the ExecuteQuery calls caused each call to finish almost immediately. It's only when I don't have the sleep pause that the timeout returns. Unless you can think of a reason the thread sleep would somehow reduce the execution time, but I'm thinking the delay would be on the sql server, rather than the app server. I did try to manually open and close the connection around the queries as you suggested, but that did not solve the problem. I'm leery about just extending the timeout as the number of ExecuteQuery calls will change dynamically depending on the number of objects I am trying to display in the page. So I won't know how long it may need to be until the page is rendered. Thanks again
Hi Stryder_1; Are you saying that doing this code still gives you a time out exception?
DataClasses1DataContext db = new DataClasses1DataContext(); var con = db.Connection; con.Open(); // Do both your Linq queries here con.Close();My dilemma is that if you are still getting a time out exception while the connection is open then I don't know what is going on then because the two executions are happening on the same connection, the second execution does not need to open the connection because it is already open. Fernando -
Hi Stryder_1; Are you saying that doing this code still gives you a time out exception?
DataClasses1DataContext db = new DataClasses1DataContext(); var con = db.Connection; con.Open(); // Do both your Linq queries here con.Close();My dilemma is that if you are still getting a time out exception while the connection is open then I don't know what is going on then because the two executions are happening on the same connection, the second execution does not need to open the connection because it is already open. FernandoHi Fernando, No, that is not the setup. I have the DataContext intantiation and the call to ExecuteQuery in a separate function. More like this:
public List<DataTable> callExecute() { DataClassesDataContext dc = new DataClassesDataContext(); List<DataTable> listReturn = dc.ExecuteQuery(strSQL); return listReturn; } // code that does time out ... listTemp = callExecute(); // these calls return counts with different date ranges int nCount1 = callExecute().Count(); int nCount2 = callExecute().Count(); // timeout happens during this call ... // code that does _not_ time out ... listTemp = callExecute().ToList(); System.Threading.Thread.Sleep(250); // these calls return counts with different date ranges int nCount1 = callExecute().Count(); System.Threading.Thread.Sleep(250); int nCount2 = callExecute().Count(); ...Thanks for your help.