Bulk Procedure Call Node MySQL

Question

This is similar to insert multiple rows into mysql through node.js but is about procedures instead.

I'd like to call a procedure in bulk (over 10k times).

Doing this all in separate calls in incredibly slow (at about 20 inserts per second, as the procedure inserts things).

How do I call a procedure multiple times, efficiently using a single database call?

I could just build up a string of CALL procedure(args...); and call that but is there a better, more quick and efficient way?

I can help with this most likely. You would need to show details of the tables to be affected with schemas via `show create table xyz` for each and the stored proc. I could show comparisons in times of your way versus a fast way. But you need to improve this question before I would begin. — Drew, Jun 17 '16 at 19:18

score 2 · Answer 1 · answered Jun 06 '17 at 22:32

You can do it by executing multiple statements in a single query.

First of all you should create your connection using mysql module with the multipleStatements option enabled:

var mysql      = require('mysql');
var connection = mysql.createConnection({
  host     : 'localhost',
  user     : 'me',
  password : 'secret',
  database : 'my_db',
  multipleStatements: true
});

Then you need to concatenate each of your procedure calls into a single string. Don't forget the semicolon ; .

var procCalls = "CALL procedure(arg1a, arg1b);" + 
    "CALL procedure(arg2a, arg2b);" + 
    "CALL procedure(arg3a, arg3b);" + 
    "CALL procedure(arg4a, arg4b);" + 
    "CALL procedure(arg5a, arg5b);" + 
    "CALL procedure(arg6a, arg6b);" + 
    "CALL procedure(arg7a, arg7b);";
// You might want to do it in a loop

Finally, just execute your concatenated string query using the multiple-statement enabled connection:

connection.query(procCalls , function (error, results, fields) {
  if (error) throw error;
  // Success! 
});

Bulk Procedure Call Node MySQL

1 Answers1