Long last stored procedure execution in ignition

Ignition
,
1

Hi everyone,

I need to execute a stored procedure that takes approximately 30 minutes to complete. I'm looking for the best approach to handle this while avoiding system hang-ups.

One option I’m considering is creating a button that triggers a function. The function would execute the procedure asynchronously, thus keeping the UI responsive. I’d also need to extend the timeout to 40 minutes or so to ensure the process completes without interruption.

Another option I'm leaning towards is using a Transaction Group to handle the execution. This seems like a cleaner solution, but I’d love to hear your thoughts on this.

I know the ideal advice might be to optimize the stored procedure itself, but due to company policies and other constraints, I'm limited to working with the existing procedure as-is.

What do you think? Which method would you recommend? Are there any other alternatives I should consider?

Thanks in advance for your help!

2

  • You must run this from gateway scope to escape the hard 60-second limit on execution from Designer or Vision Client scope.

  • You must update your DB connection's own timeout to permit the very long execution time.

  • You must configure your DB itself to not timeout on this.

  • It doesn't matter whether you use transaction groups, named queries, or scripted queries.

  • You probably need to only call this procedure from one place, and ensure it cannot be called multiple times in parallel.

5 Likes
3

I would recommend a dedicated DB connection purely to run this and any other long-lived queries with these specific timeout parameters.

2 Likes
4

I agree with what the others have said.

What I think?

I think they need to revise the policies because I strongly suspect the stored procedure has built in performance issues. A lot of people make egregious mistakes in SQL and you can pick up massive performance gains by simply optimizing them. One time I rewrote something to do the same thing with the same database in 2 seconds that they were used to waiting 15 minutes for.

Bad code should always be refactored imo.

3 Likes