1

I have an API with Express/Node.js with MySQL DB. I'm using the Mysql2 npm package (no ORM) for queries. For some particular routes, I have some queries running in parallel (which are not exactly related, so no joins can be performed). I want to know whether using a stored procedure can improve my performance, especially considering the non-blocking behavior of Node.js. I'm mainly concerned with 2 parameters:

  1. Query time and
  2. Server overhead.

To rephrase, if I changed from individual queries to stored procedure, what will be the effect on my response time and server utilization?

Edit:

What I mean by queries running in parallel is, I have a function set up, so that the queries can run independently (without waiting for the response of the previous query) and once all promises resolve, my function returns the results as a Promise.resolve.

A little snippet:

const makeThreeQueries = (q1, q2, q3) => { # queries being array with 2 elements: ['query string', ['Array', 'of', 'Args']]
    return new Promise((resolve) => {
        const results = []
        const queryHandler = (result) => {
            results.push(result)
            if (results.length === 3) resolve(results) # 3 in case there are 3 queries
        }
        db.query(...q1).then(queryHandler)
        db.query(...q2).then(queryHandler) # doesn't wait for q1 to finish
        db.query(...q3).then(queryHandler) # doesn't wait for q2 to finish
    })
    
}

Of course the main code is much more robust than this, with proper error handling and validation/positional consistency in place.

VaibhavJoshi
  • 355
  • 2
  • 15
  • 2
    There is hardly a correct answer. The possible best answer is: it depends. Depending on locking mechanism, caching, indexing, etc. it might be faster using stored-procedures. It may also scale better in case, everything can be calculated by database. Node isn't running in parallel (if you run only one instance), everything is offloaded to your database. Depending on what you need, it might still be faster by leveraging nodejs non-blocking behavior. In fact: it's hard to guess - it depends. – Michael Hirschler Jan 19 '21 at 11:27
  • @MichaelHirschler Please see the edit about what I mean by queries running in parallel. – VaibhavJoshi Jan 19 '21 at 11:58
  • 1
    A stored procedure can not run itself in parallel - only the client (I mean client from the point of view of MySQL, not the browser) can invoke several instances of your stored procedure in parallel. So in your particular case, running 3 queries in parallel - a stored procedure won't help you. – IVO GELOV Jan 19 '21 at 12:03

1 Answers1

1

There are some savings because control stays inside the Stored Proc rather than needing to go back and forth between the client and server.

The savings is very dramatic if the client and server are on different servers that are geographically far apart. In this case, transmission time can dominate performance.

Whether the code is coming from the client or from a stored proc, parallelism may require careful attention to transactions -- to keep separate actions from stepping on each other.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • I'm interested in the comparison between the performances of a stored procedure and Node.js parallel queries (as given in the question), given all other aspects are kept the same. – VaibhavJoshi Jan 27 '21 at 08:11