Context : There is an stored procedure and it takes around 20-30 mins to finish.User clicks button which triggers an process which calls the stored procedure.
Now, Since the stored procedure is taking lot of time, it fails to execute by throwing below error , what can be done?
Note : Below are the things that is already done and yet not working.
Kindly ask if any more info is needed :)
@Charith RC : Your case is similar to what is been discussed at @ https://success.outsystems.com/support/troubleshooting/application_runtime/bpt_automatic_activities_timeout/
From what I understand, you’re invoking a process that triggers a stored procedure from an Automatic Activity. Since your SQL execution takes over 20 minutes and Automatic Activities are limited to a 5-minute timeout by design, the execution fails once that limit is reached.
As suggested in the article, the recommended approach is to:
Tried this, still Stored procedure throws Scheduler Service: Error executing request http://127.0.0.1:80/ISCOP_MDM_PRODPLAN_CS_V2/ for Activity AutomaticActivity1. Request duration = 360 secs.
I'm not sure this was done correctly, as the exception still shows AutomaticActivity1 like before. If you had moved the execution of the stored procedure to a timer, the error message would have been different
Hello,
Based on what you've already tried, here some additional options:
1. Asynchronous processing with a job queue
- User clicks a button and create a record record in a entity
- A separate service picks up pending jobs and executes the SP
2. Infraestructure optimization
- Review server resources: CPU, RAM, disk on the database server
- Check if the server is under heavy load during SP execution
- Consider scaling up resources if the server is bottlenecked
3. Index optimization
- Missing indexes
- Unused or redundant indexes
4. Batch Processing or Pagination
- Instead of processing all records at once, modify the SP to process in batches. For example: process 1,000 records every 5 minutes
- Store the progress/last processed ID in a control table
- The timer/service continues processing until complete
5. Cache Parametric or Reference Tables
- If the SP uses lookup/parametric tables that rarely change, cache them in memory tables or table variables
- This reduces repeated joins and lookups during complex calculations
Example:
DECLARE @CountryCache
TABLE (
CountryID INT PRIMARY KEY,
CountryName VARCHAR(100),
TaxRate DECIMAL(5,2),
ShippingCost DECIMAL(10,2)
)
Hi @Charith RCif you are using integration studio and getting output using .net code means
cmd.CommandTimeout = 0; use this
Dear @Charith RC ,
If in case of On Prem Server or Self Managed SQL DB we can leverage the use of SQL Server Agent job. We can create a SQL Server agent from SSMS and from our code we can just trigger the job so that the SP is executed. Let me give you step below and let us know if it helps.1. Create a SQL Server Agent job to execute the procedure. You can do this in SQL Server Management Studio (SSMS) by navigating to SQL Server Agent > Jobs, right-clicking, and selecting New Job(eg : NewBackgroundJob). 2. Add a new job step with the type "Transact-SQL" and enter the EXEC command (eg : EXEC dbo.LongRunningTask;). 3. Then in your code instead of Executing SP try to Execute the job on demand using sp_start_job
(eg: EXEC msdb.dbo.sp_start_job 'MyBackgroundJobName';)
.