Forum

HomeHomeCoreCoreGeneralGeneralSQL timeout when query takes too much time gathering dataSQL timeout when query takes too much time gathering data
Previous
 
Next
New Post
3/27/2012 3:05 AM
 

that is correct, if the query starts returning data within 30 seconds all is well. 

I've had a DBA look at the query and it has been optimized as much as possible. Everything is in place to optimize the query, it is just the huge amount of data that slows it down. Posting the query won't help, there is a lot of custom data being handled.

Using the custom connection solves the problem at the moment. working on a redisign of the database to solve the problem on a more fundamental level.

New Post
3/27/2012 7:31 AM
 
I  guys,
   for me the query takes long time because it's importing 350.000 products form different access tables. It's normal for its job. It takes between 25 tand 35 seconds. I think it's a fast query!

Anyway the question is how to change the command timeout of a stored procedure? I think that it's not possible with ows. I have done the same function in dnn custom module with no problem.

I hope I was helpful
Thanks for your responses that are useful
Matteo
New Post
3/26/2014 6:07 PM
 
Hi Kenin, I have had this problem come up for me where I have a long running process (started by the scheduler). There is a query there operating against an oracle DB that takes about 45 mins to start returning data. Problem is that it never returns back to OWS. I have trapped exceptions in OWS but nothing ever comes back. I have tested the query against oracle (Using TOAD) and all works well (data comes back after 45 mins). I have also changed the query to remove a few joins and that works in OWS no probs. In fact, i have queries against Oracle that take up to 25 mins to start to return data and they are OK. So the any timeout is happenning after the 25 minute mark.

In the IIS Session state I have tried to set "SQLCommandTimeout" (via IIS7) to 3 hours. Even tried to set it to 0 but no luck

I have also tried to set "Timeout" to 3 hours without luck

And I have also set the ExecutionTimeout to 3 hours but still no luck

Can you offer any advice on how to get a long running query to work via OWS. I suspect that the "SQLCommandTimeout" parameter is the way to go but I don't think that OWS is using this parameter when it connects to the DB.

I have also tried the exact same query through excel (VBA) using the same connection string and it works even without setting the "SQLCommandTimeout" parameter. SO not quite sure whats going on here. Is there another timeout parameter I am missing
Cheers
New Post
3/27/2014 6:17 AM
 

The long and short of it is - I wouldn't ever attempt to do it that way. Period. I know that sounds harsh, but due to a number of specific circumstances, it's just simply not a good idea. You have to rely on a few specific timeout periods that may or may not be within your control. First off, you have the SQL Timeout period - which obviously you can override. On top of that you have the Web Request Timeout, which you can also override. Next Up, the Session Timeout, which may or may not fire during your three hour window depending on the size of the sessions and competition for resources. On top of that you have the IIS Host Process which can reset unexpectedly at any time due to many environmental factors. 

Rule #1 of web requests. They are stateless, and should remain such.

So - how would I do this? Well - I do something very similar to this in many sites that have long running processes, or reporting systems. You need to build an additional piece. It doesn't have to be beautiful, and once you build it, you can use it on dozens of projects.

So - what is this magical piece? It's either a Windows Service, or a Command Line Batch Process, depending on how you want to maintain the runtime. The logic is simple, on a scheduled basis, the process will execute a SQL statement to look in a batch table for Tasks to complete. At the time it pulls the Task from the table, it marks it as processing. The process then performs the task, and once completed, executes a SQL statement to set the Task Completion Date, and trigger any additional statements that would be necessary. If this is done as a windows service, you can simply have the thread go to sleep for a period of time and then start the process all over again. If its a command line process, you can use the Task Scheduler to fire it on a scheduled basis. There is a larger advantage to the Windows Service in that you can have any number of threads processing any number of tasks at the same time. 

So - you would rely on Windows to handle this process, not IIS. Since it appears that this process is fired from the Scheduler anyway, it doesnt matter specifically what happens with the results I suppose. When I do this for reporting purposes, I simply have the front end trigger the service task record, and then the browser continually checks the status of the task until it is completed, then returns  the result that the task stored in the database.


Kevin M Schreiner
Open Web Studio
PayPal - The safer, easier way to pay online!
New Post
3/27/2014 6:55 AM
 

thanks for that.  I wasnt planning on leaving these timeouts set to the max values.  Just wanted to know which one was causing the timeout.  I suspect that it was the SQLCommandTimeout but even after setting it to 3 hours, it still didnt return & I know that the query only takes 45 minutes.  Something else must be causing the timeout.  


Your right about long running batch tasks. They're a separate beast to http requests but I was just being lazy here and hoping that i could somehow use the DNN scheduler to perform long-running SQL by overriding the SQLTimeOut.  I guess I'll just have to write something else to accomplish this task,


Cheers

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