Is there a way to parallelerise the execution of stored procedures in snowflake

user_15383469 0
Viewed 471

We have a master stored procedure that loops through a table and for each row calls a stored procedure based on data from that table. There is likely to be a lot of rows and so we are worried about the the master stored procedure timing out due to the time it would take to loop through all of the rows.

The stored procedures called do not have a dependency on each other so we would like to speed up the process by executing the stored procedures without waiting for the previous one to finish. Is there a way to achieve this in snowflake?

snowflake-cloud-data-platform parallel-processing stored-procedures
2 Answers

Javascript stored procedures are single-threaded so there is no way to get them to run parts of their code in parallel.

You might be able to improve performance by changing your design, for example by running instances of the SP via tasks as you can get tasks to run in parallel

user_136583990

We have a master stored procedure that loops through a table and for each row calls a stored procedure based on data from that table.

Having one stored procedure call others will be single threaded. You can do this instead:

  1. Create a task for each of your child stored procedures. Set its schedule to 1 minute; specify no overlapping execution, and do not enable it.
  2. Have your main stored procedure read from the table that drives it.
  3. Instead of having your main stored procedure call other stored procedures, have it alter their tasks to resume them.
  4. The first thing each child stored procedure should do is to run a SQL statement to alter their task disabling it.

This will allow asynchronous, parallel execution. The main stored procedure just needs to figure out what other stored procedures do or don't need to run and what their call parameters should be. It kicks off their execution by enabling the schedule for their task and moves on to the next code statement. A minute later, the child stored procedure starts and disables its schedule so when it completes it won't run again until the main stored procedure alters its task back to enabled.

Your task create statements for the child stored procedures should be something like this:

create or replace task MY_SP_TASK
 warehouse = 'MY_WAREHOUSE'
 schedule = '1 MINUTE '
 allow_overlapping_execution = false
as
  call MY_SP('PARAM1', 'PARAM2')

If you need the main stored procedure to change the parameters in the call, you can have the main stored procedure run the "create or replace task" and change them. You'll then want to enable the task:

alter task MY_SP_TASK resume;

If you don't need to change the child's call parameters, you can just run the alter statement to resume it and that's all. Then on the child stored procedure among the first things the code should do is disable its own taskL

alter task MY_SP_TASK suspend;
user_127563810
Related