Forum

HomeHomeCoreCoreGeneralGeneralSQL timeout when query takes too much time gathering dataSQL timeout when query takes too much time gathering data
Previous
 
Next
New Post
12/21/2011 1:49 AM
 

When retrieving data from the database in the query template I've encountered SQL timeout problems.


When I use a query that starts returning data within 30 seconds the query will continue returning data even if that means it needs to continue for over several minutes. 

But if use a query that takes more than 30 seconds to start returning data it will enter a SQL timeout.


unfortunately because of the complexity of the query I am not able to change it, but is there a way to increase of even disable the 30 second timeout? 

I've looked around in several config files, IIS properties, but so far I've not been able to find a solution, 


Any help would be appreciated.


New Post
3/21/2012 5:02 AM
 

I have same issue. It should be a command timeout problem, default is 30 seconds. 

My question is: how can I change the command timeout in ows?


anyone can help us?
thanks

Matteo

New Post
3/21/2012 4:41 PM
 
are you getting a httptimeout?.  you might want to have a look at the web.config executionTimeout parameter.  Refer to this

http://msdn.microsoft.com/en-us/library/ms960520%28v=cs.70%29.aspx

 

<configuration> 
<system.web> 
   <httpRuntime executionTimeout="300"/> 
</system.web> 
</configuration>
 

 Also, if you have long running tasks (that dont require any web output to be generated) then you might want to consider spawning the query in its own execution thread.  You can do this only in Action queries and not query templates.  The action query has a checkbox that says "Run as a process?".  If you check this then the query will be spawned in its own thread and contol immediately returns to the next OWS statement.  Every OWS statement indented under the query will run as part of this thread.  I dont beleive that you will be able to use the query results to generate any web output to the user cause the thread is no longer in scope.

 

Another method for executing long running tasks is to use the DNN scheduler to schedule OWS jobs.  There is a forum thread somewhere here that talks about it.  Its fairly simple to set up.

Cheers

John

 

New Post
3/22/2012 12:17 AM
 
the executionTimeout doesn't work for me. the web.config already has a httpRuntime executionTimeout="1200", but the timeout derives from somewhere else. the run as process is a viable option if you don't need the output for generating web output. In that case you might want to try to add a custom connection to the query template and add: CommandTimeout=120. This will set the timeout to 2 minutes. This seems to work here, but I don't know what the impact will be on the site: having a lot of users setting up a custom connection to the database?
New Post
3/22/2012 7:58 AM
 

so from what I understand if the query starts to return data within 30 seconds it will be ok and if not you get a timeout.  You might need to have a look at re-working the query if its to be used as part of a regular HTTP request.  Run the query in SQL management studio & have a look at the execution plan.  If its taking 30 seconds just to start to return data then it may need tuning or maybe there are missing indexes on some tables and its doing a full table scan. 

Order By Clauses can also be nasty because in some cases data can only be sorted once the full result set is known.  If you dont need an order By clause then remove it & see if it makes a difference.  The execution plan will reveal whats going on.

Can you post the query?

Previous
 
Next
HomeHomeCoreCoreGeneralGeneralSQL timeout when query takes too much time gathering dataSQL timeout when query takes too much time gathering data


 

New York, NY • Baltimore, MD • Vienna, VA • St. Louis, MO • Seattle, WA • info@openwebstudio.com

Bookmark & Share Bookmark and Share