Concurrently execute a stored procedure in mysql

Question

I am working in mysql. I have a stored procedure which takes 1 min to run. I have to run this procedure 10 times with different parameters. How can I run the 10 instances of procedure simultaneously with different parameters? My procedure is such that no locking will happen between any 2 instances.

Maybe a batch file is what you want. Look here: https://stackoverflow.com/questions/8055371/how-do-i-run-two-commands-in-one-line-in-windows-cmd — Icculus018, Feb 04 '19 at 16:51
@kristech "&" means second command run AFTER first has executed. I want all command to run simultaneously/parallely. — rahul verma, Feb 04 '19 at 17:07
Do you have a scripting language you can use to wrap this in? Those can often run parallel queries using multiple connections/threads. If not, you'll need N parallel processes for N concurrent queries. — tadman, Feb 04 '19 at 18:11

score 0 · Accepted Answer · answered Feb 04 '19 at 19:47

If you are using Linux then one solution is to create shell scripts and run them as cron jobs at a particular time. Here is an example shell script. This script is named as sql1.sh and it will write the current date to out1.txt

#!\bin\bash
date > out1.txt

so i have made sql1.sh till sql5.sh and the output written into out1.txt till out5.txt

Here is the crontab file to execute the shell scripts at 19:37.

37 19 * * * /yourDirectory/sql1.sh
37 19 * * * /yourDirectory/sql2.sh
37 19 * * * /yourDirectory/sql3.sh
37 19 * * * /yourDirectory/sql4.sh
37 19 * * * /yourDirectory/sql5.sh

Here is the output from out1.txt till out5.txt

Mon Feb 4 19:37:01 UTC 2019 - out1.txt
Mon Feb 4 19:37:01 UTC 2019 - out2.txt
Mon Feb 4 19:37:01 UTC 2019 - out3.txt
Mon Feb 4 19:37:01 UTC 2019 - out4.txt
Mon Feb 4 19:37:01 UTC 2019 - out5.txt

so instead of date command use mysql with suitable options to run your sql query. This may not be efficient but will get the job done.

Concurrently execute a stored procedure in mysql

1 Answers1