# Long running stored procedure **URL:** \ **Category:** Ignition\ **Created:** [September 12, 2023, 6:32pm UTC](https://forum.inductiveautomation.com/t/long-running-stored-procedure/79036 "2023-09-12T18:32:06Z")\ **Posts on this page:** 12\ **Page:** 1
**Author:** ![ShaneH](https://avatars.discourse-cdn.com/v4/letter/s/3e96dc/32.png) [@ShaneH](https://forum.inductiveautomation.com/u/ShaneH)\ **Post date:** [September 12, 2023, 6:32pm UTC](https://forum.inductiveautomation.com/t/long-running-stored-procedure/79036/1 "2023-09-12T18:32:06Z")
I have a stored procedure that (unfortunately) takes longer than 60 seconds to run. On the upside, I only need to run it once a day. I would like to set it up as a gateway timer or scheduled task to run once a day. But because it takes longer than 60s, it errors out and it appears that the Gateway resets the DB connection. Any suggestions? (Short of modifying the SP to run faster.) Thanks. -Shane ---
**Author:** ![ShaneH](https://avatars.discourse-cdn.com/v4/letter/s/3e96dc/32.png) [@ShaneH](https://forum.inductiveautomation.com/u/ShaneH)\ **Post date:** [September 13, 2023, 1:14pm UTC](https://forum.inductiveautomation.com/t/long-running-stored-procedure/79036/2 "2023-09-13T13:14:37Z")
Any thoughts on how to change the connection timeout for system.db calls? Or maybe another mechanism I could use? Even calling system.db.runPrepUpdate with a tx created with beginTransaction and a large timeout doesn't seem to work. 60s seems to be the limit. Thanks. -Shane ---
**Author:** ![bschroeder](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bschroeder/32/13260_2.png) [@bschroeder](https://forum.inductiveautomation.com/u/bschroeder)\ **Post date:** [September 13, 2023, 1:22pm UTC](https://forum.inductiveautomation.com/t/long-running-stored-procedure/79036/3 "2023-09-13T13:22:14Z")
I think you are up against the hard-coded 60-second Vision to Gateway timeout... but I'm not sure. Your first step is to figure out what is going on with that stored procedure. Maybe the tables are missing indexes that will help speed things up. ---
**Author:** ![ShaneH](https://avatars.discourse-cdn.com/v4/letter/s/3e96dc/32.png) [@ShaneH](https://forum.inductiveautomation.com/u/ShaneH)\ **Post date:** [September 13, 2023, 1:32pm UTC](https://forum.inductiveautomation.com/t/long-running-stored-procedure/79036/4 "2023-09-13T13:32:43Z")
Thanks for the response. Unfortunately, this is from a legacy system that we are slowly modernizing, so modifying the SP isn't possible (hence my caveat in the original post). You make a good point though, I should try running this completely from a Gateway context to make sure none of the built in timeouts or restrictions from the designer/console/etc. are coming into play. ---
**Author:** ![bschroeder](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bschroeder/32/13260_2.png) [@bschroeder](https://forum.inductiveautomation.com/u/bschroeder)\ **Post date:** [September 13, 2023, 1:36pm UTC](https://forum.inductiveautomation.com/t/long-running-stored-procedure/79036/5 "2023-09-13T13:36:32Z")
Is this something that queries data from tables or inserts data into the database? If it queries you will probably need to do a sendMessage both ways, one to the gateway where the payload has the target client node that does the stored procedure, and another from the gateway to the target client node with the data after it is done. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [September 13, 2023, 1:38pm UTC](https://forum.inductiveautomation.com/t/long-running-stored-procedure/79036/6 "2023-09-13T13:38:05Z")
If it only runs once a day, I would think placing the results/status/etc into gateway memory tags would suffice for all consumers. ---
**Author:** ![bschroeder](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bschroeder/32/13260_2.png) [@bschroeder](https://forum.inductiveautomation.com/u/bschroeder)\ **Post date:** [September 13, 2023, 1:39pm UTC](https://forum.inductiveautomation.com/t/long-running-stored-procedure/79036/7 "2023-09-13T13:39:59Z")
Missed that part... yep. 100%. Now more coffee. ---
**Author:** ![YF129701](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/yf129701/32/60498_2.png) [@YF129701](https://forum.inductiveautomation.com/u/YF129701)\ **Post date:** [September 13, 2023, 2:50pm UTC](https://forum.inductiveautomation.com/t/long-running-stored-procedure/79036/8 "2023-09-13T14:50:22Z")
From what I read, the OP doesn't mention they need any results from the stored procedure - if that is the case and it just needs to run once a day, why not move it out of the Ignition space and just use a SQL agent job or something similar? That would be very easy to manage and not interfere with Ignition... ---
**Author:** ![ShaneH](https://avatars.discourse-cdn.com/v4/letter/s/3e96dc/32.png) [@ShaneH](https://forum.inductiveautomation.com/u/ShaneH)\ **Post date:** [September 13, 2023, 7:52pm UTC](https://forum.inductiveautomation.com/t/long-running-stored-procedure/79036/9 "2023-09-13T19:52:22Z")
Ok, so I set it up to call system.db.runPrepUpdate() on the stored procedure from a scheduled gateway event, and at the 60s mark it timed out and reset the DB connection. This was the exception returned: java.sql.SQLRecoverableException: Io exception: Socket read timed out I had hoped running it solely in the gateway context would allow it to go longer than 60s. I don't need any results from the stored procedure. I can't modify the stored procedure as it's part of a legacy system that's being converted. Any thoughts? ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [September 13, 2023, 7:55pm UTC](https://forum.inductiveautomation.com/t/long-running-stored-procedure/79036/10 "2023-09-13T19:55:49Z")
> [@ShaneH](#): > > java.sql.SQLRecoverableException: Io exception: Socket read timed out That sure looks like a JDBC driver error. Look for a newer version (if you can) or an extra connection property that could modify its behavior. ---
**Author:** ![ShaneH](https://avatars.discourse-cdn.com/v4/letter/s/3e96dc/32.png) [@ShaneH](https://forum.inductiveautomation.com/u/ShaneH)\ **Post date:** [September 13, 2023, 9:42pm UTC](https://forum.inductiveautomation.com/t/long-running-stored-procedure/79036/11 "2023-09-13T21:42:57Z")
Opened a ticket. Ignition support says the 60 second timeout is built in to the Oracle JDBC driver and can not be modified / extended. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [September 13, 2023, 10:31pm UTC](https://forum.inductiveautomation.com/t/long-running-stored-procedure/79036/12 "2023-09-13T22:31:11Z")
You're going to have to run it using Oracle scheduling in the DB, then. 🤷‍♂️